I am building one sql query based on 5 select statements unioned all together. Each select is built as a variable with where clauses based on filters selected by the users. All 5 selects are concatenated together to build the one dynamic query. The one select below needs to be changed to select rows only for distinct lsornm values.
SELECT LSORNM as ord, LSSUFX as sfx, LSCUST as cst, DATE (SUBSTRING(CHAR(LSDTOR),1,4) ||'-'|| SUBSTRING(CHAR (LSDTOR),5,2) ||'-'|| SUBSTRING(CHAR(LSDTOR),7,2)) as dte, LSITEM as pon, LSDESC as job, LSUNPR as amt, 'DEL' as sts, IFNULL(OPSOTYP,' ') as typ, IFNULL(MMAL01,' ') as nm1, IFNULL(MMAL02,' ') as nm2 FROM SO05 left outer join OP101L2 on SO05.LSORNM = OP101L2.INVN101 AND SO05.LSSUFX = OP101L2.SUVX101 left outer join MM01 on SO05.LSCUST = MM01.MMNMBR WHERE ((MMCPRE = 'C') or (MMCPRE = 'S')) AND (LSCUST > ' ') AND ((LSDTOR >= 19980101) AND (LSDTOR <= 20991231))
From what I have read, I think maybe group by or a sub query is the way to go, but cannot get it to work. Any help would be appreciated.
IBM iSeries AS400 V6R1
March 24, 2011 6:35 PM
March 28, 2011 1:31 PM