|
AdamWest
Constraint Violating Yak Guru
360 Posts |
 Posted - 2013-08-16 : 08:44:58
|
| I have a problem, it's more of a ERP data issue but as you can see here we select the 2 of warehouse location types (PAL, RAK ) from one table (ICBLDTIR). However, the user is able to change the other type (PCK) which we get from the second table (ICBALMIE). He changed a PCK there to a RAK. Now the Crystal report is getting a duplicate location. Since this can happen for purposes of report we only want to show locations and QTY. So I was thinking is there a way to code in this view that, If we already have a same location (location is the 3 columns IRLOC1-3 or IELOC1-3 so if on the second table we already have that location then dont' include it a second time.True the ERP should not allow it, but it's got that possibility.CREATE VIEW astccdta.acbalmpk AS ( (SELECT LMLTPC, COALESCE(IRLOC1,'') as IRLOC1, COALESCE(IRLOC2,'') as IRLOC2, COALESCE(IRLOC3,'') as IRLOC3, IRPRT#, IRQOH#, IRWHS#, '' as IEPRT#, '.00' as IEQOH#, '' as IELOC1, '' as IELOC2, '' as IELOC3, '' as IERIDC, '' as IEWHS# FROM (SELECT LMLTPC, LMLOC1, LMLOC2, LMLOC3 FROM ASTDTA.ICLOCMLM WHERE LMLTPC IN ('PAL', 'RAK' ) )t1 left outer join (SELECT IRLOC1, IRLOC2, IRLOC3, IRPRT#, IRQOH#, IRWHS# FROM ASTDTA.ICBLDTIR ) t2 On LMLOC1=IRLOC1 AND LMLOC2=IRLOC2 AND LMLOC3=IRLOC3 ) UNION ALL (SELECT ' ' as LMLTPC, ' ' as IRLOC1, ' ' as IRLOC2, ' ' as IRLOC3, '' as IRPRT#, '.00' as IRQOH#, '' as IRWHS#, IEPRT#, IEQOH#, IELOC1, IELOC2, IELOC3, IERIDC, IEWHS# FROM ASTDTA.ICBALMIE) ) |
|
Need to adjust the data of this view