…to select rows only for distinct lsornm values.

If you want distinct values for LSORNM, you have to decide how to handle each of the other columns in the SELECT list. If there are multiple values of LSSUFX, LSCUST or DATE for a given value of LSORNM, you need to put a specification in the statement to tell it which one of the multiple values to use. That must be done for each column that might return multiple values.

Please provide examples of a few rows to give us some context. Also, if you can, describe a little more about what you mean by “distinct” for that column.

Tom

This file is deleted lines from sales orders. Let’s say there is a sales order 12345 that has 15 lines of items originally ordered. Line 3, 12, and 15 were deleted from the original order, so there will be 3 records on this SO05 file each with the same lsornm of 12345. I only need to pick up the fact that this sales order is on that file, not that it is on there 3 times. The other fields it picks and whether they should be the same for all three deleted lines are: lsornm(order number – same), lssufx(order suffix – same), lscust(customer number – same), lsdtor(order date – vary), lsitem(item number – vary), lsdesc(item description – vary), lsunpr(unit price – vary).

Here are some example of fields I would use from these records:
Field name
lsornm 152895 152895 152895
lssufx 00 00 00
lscust 000095 000095 000095
lsdtor 20100118 20100103 20100118
lsitem 12433910S 124396797 1244L3175
lsdesc Liquid disp handle kit shower valve
lsunpr 31.452000 8.691500 78.451000

Just saw that this didn’t line up very well, so here they are comma separated:

Here are some example of fields I would use from these records:
Field name
lsornm – 152895, 152895, 152895
lssufx – 00, 00, 00
lscust – 000095, 000095, 000095
lsdtor – 20100118, 20100103, 20100118
lsitem – 12433910S, 124396797, 1244L3175
lsdesc – Liquid disp, handle kit, shower valve
lsunpr – 31.452000, 8.691500, 78.451000

I believe you just need to Group By. Example…

This SQL retrieves every record for Cust 101:

/*  Every record output.  */
SELECT	OD.od_custno,  OD.od_loadmo, OD.od_loadda, (OD.od_loadcn * 100) + OD.od_loadyr, OD.od_ordrno
FROM	rap002001.orddtl AS OD 
WHERE	OD.od_custno  =  101 
WITH	NC;

Custno Date OrdrNo
101 3 25 2011 1
101 3 25 2011 1
etc….
101 3 28 2011 1
101 3 28 2011 1
etc….

This SQL outputs 1 record for Cust 101/Date/OrdrNo:

/*  One record output.  */
SELECT	OD.od_custno,  OD.od_loadmo, OD.od_loadda, (OD.od_loadcn * 100) + OD.od_loadyr, OD.od_ordrno
FROM	rap002001.orddtl AS OD 
WHERE	OD.od_custno  =  101 
GROUP BY	OD.od_custno,  OD.od_loadmo, OD.od_loadda, (OD.od_loadcn * 100) + OD.od_loadyr, OD.od_ordrno
WITH	NC;

Custno Date OrdrNo
101 3 25 2011 1
101 3 28 2011 1

Group By all of the fields in your select. As long as all fields are the same value within the order you will get 1 record per order number.

Gary

That’s part of the problem, the value of all the fields is not the same within the records for the same sales order.

Canuhp, it would be easier if you post your example data in a tabular form (using the editor’s “code” tool).

Something like this:

lsornm 	lssufx	lscust 	lsdtor 		lsitem 		lsdesc 		lsunpr 	

152895	00	000095	20100118	12433910S	Liquid disp	31.452000	
152895	00	000095	20100103	124396797	handle kit	8.691500
...

Also, I would recommend posting and example of the results you would want to get from the example data you posted, so we don’t have to guess.

I’m pretty sure you will get the solution to your requirement if you provide enough details.

Also, note that a GROUP BY doesn’t help with the “distinct values for LSORNM”. There can be any number of groups that would have the same value for LSORNM. A GROUP BY will generate distinct values for the composite of the GROUP BY columns, but not for just one of a set of GROUP BY columns.

For the example where a distinct value of LSORNM is 152895, there are two possible values for LSDTOR — 20100118 and 20100103.

If you will have only a single row with LSORNM = 152895, and you want to have LSDTOR in the same row, there must be a rule that specifies which value to use.

However, it’s not clear if that’s what you really want.

I only need to pick up the fact that this sales order is on that file, not that it is on there 3 times.

Then what use are the other fields? Why SELECT them at all? If they’re needed in the final result set, there’s essentially no way to have DISTINCT LSORNM values (in the result set). (And that’s not technically true because recursive SQL could be used, but that doesn’t seem to lead where you want to go.

So, are you really only looking for a list of DISTINCT LSORNM values or will you be using the other columns from your original SELECT column list? You can’t have both from a single SELECT (disregarding recursive SQL)?

Tom

    lsornm       lssufx       lsitem        lsdesc              lscust      lsunpr       lsdtor
     152895        0         110009321    Sandstone sink        001503    201.010000    20101118
     152895        0         12433910S    Liquid dispenser      001503     31.452000    20101118
     152895        0         124274998    Tray Faucet           001503     25.264200    20101130
     152895        0         12447430W    Sink Faucet           001503     86.931700    20101130

In the data I showed above, I would absolutely have to have the lsornm, lssufx, lscust, the most recent lsdtor, and (this is a new requirement) the sum of the dollars for all records for this sales order. The other fields I have talked about are needed for the results record, but I can force them to be a blank or space on these records if needed. So, for the above data, the result set would need to look like:

    lsornm       lssufx      lscust     lsdtor
     152895        0         001503    20101130 

Oops, forgot the sum. Assuming quantities on each of these records is 1, i would also need to return the sum value of 344.65.

Based on the results you posted, something like this should suffice:

SELECT lsornm,lssufx,lscust,MAX(lsdtor),SUM(lsunpr)
FROM your_table
GROUP BY lsornm,lssufx,lscust;

I agree with Carlosdl. The MAX() and SUM() functions allow the GROUP BY to provide single values for those columns. The actual GROUP BY columns should all work as effectively ‘distinct’ as long as LSSUFX doesn’t add a complication. (Customer number should work out fine.)

Any remaining columns that you need might still need attention, but we’ll have to know how they should affect results.

Tom

With your advice and forcing literals into the columns that I don’t absolutely need, I think this is going to work. Preliminary testing looks good. Thanks for all the help!!