Select query for Optional Fields (3 replies)
IDL, not a problem.
The crux, is around the use of alias on the optional field tables: for each optional field you need to perform a left outer join on the optional field, specifying the optional field id in the join criteria.
Attached is a sample SQL script which shows how extract both header and detail optional fields. Simply alter optional field ids.
Any questions please reply to this post.
select ORDNUMBER, RTRIM(H1.VALUE) as "HDROPTFLD1", RTRIM(H2.VALUE) as "HDROPTFLD2", D.LINENUM, ITEM, QTYORDERED, RTRIM(D1.VALUE) as "DTLOPTFLD1", RTRIM(D2.VALUE) as "DTLOPTFLD2", RTRIM(D3.VALUE) as "DTLOPTFLD3" from OEORDH H inner join OEORDD D on H.ORDUNIQ = D.ORDUNIQ left outer join OEORDHO H1 on H.ORDUNIQ = H1.ORDUNIQ and H1.OPTFIELD = 'OPTFLD1' ---header optional field 2 left outer join OEORDHO H2 on H.ORDUNIQ = H2.ORDUNIQ and H2.OPTFIELD = 'OPTFLD2' ---detail optional field 1 left outer join OEORDDO D1 on D.ORDUNIQ = D1.ORDUNIQ and D.LINENUM = D1.LINENUM and D1.OPTFIELD = 'OPTFLD1' ---detail optional field 2 left outer join OEORDDO D2 on D.ORDUNIQ = D2.ORDUNIQ and D.LINENUM = D2.LINENUM and D2.OPTFIELD = 'OPTFLD2' ---detail optional field 3 left outer join OEORDDO D3 on D.ORDUNIQ = D3.ORDUNIQ and D.LINENUM = D3.LINENUM and D3.OPTFIELD = 'OPTFLD3'
select ORDNUMBER, RTRIM(H1.VALUE) as "HDROPTFLD1", RTRIM(H2.VALUE) as "HDROPTFLD2", D.LINENUM, ITEM, QTYORDERED, RTRIM(D1.VALUE) as "DTLOPTFLD1", RTRIM(D2.VALUE) as "DTLOPTFLD2", RTRIM(D3.VALUE) as "DTLOPTFLD3" from OEORDH H inner join OEORDD D on H.ORDUNIQ = D.ORDUNIQ left outer join OEORDHO H1 on H.ORDUNIQ = H1.ORDUNIQ and H1.OPTFIELD = 'OPTFLD1' ---header optional field 2 left outer join OEORDHO H2 on H.ORDUNIQ = H2.ORDUNIQ and H2.OPTFIELD = 'OPTFLD2' ---detail optional field 1 left outer join OEORDDO D1 on D.ORDUNIQ = D1.ORDUNIQ and D.LINENUM = D1.LINENUM and D1.OPTFIELD = 'OPTFLD1' ---detail optional field 2 left outer join OEORDDO D2 on D.ORDUNIQ = D2.ORDUNIQ and D.LINENUM = D2.LINENUM and D2.OPTFIELD = 'OPTFLD2' ---detail optional field 3 left outer join OEORDDO D3 on D.ORDUNIQ = D3.ORDUNIQ and D.LINENUM = D3.LINENUM and D3.OPTFIELD = 'OPTFLD3'
Closed due to inactivity.
Hi, we are trying to write a query to get all the optional fields for a single record (in this case the Order Detail) onto a single line. We're struggling a little. Do you have any sample queries?