Re: SQL: how to combine a join with WHERE EXISTS

On 15 Feb 2013 15:24, Vernon Hamberg wrote:

Your WHERE EXISTS is ahead of the LEFT JOIN
You also need parentheses around the SELECT that is populating the
INSERT.

Actually the INSERT using fullselect does not have parentheses,
according to the syntax diagram. Besides, as offered, there is a
extraneous parenthesis at the end of the statement which would be
unmatched, even if allowed. And AFaIK the ORDER BY can not be specified
on a subquery, which is what the fullselect becomes when inside
paretheses. Thus I believe what was offered, must have removed from the
statement, those parentheses that are enclosing the fullselect;
including the removal of the spurious right parenthesis at the end.

Try this:

insert into bzmiscchg OVERRIDING USER VALUE
(select ' ','I',ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG,
CTLTEXT3 ChgDesc,sum(AMSCHRG2) chgamt,
replace(char(curdate(),iso),'-','') || 'T'
|| replace(char(curTime()),':','')
,' ',' '
from miscchg3 a

left outer join ctltable
on CTLTABLE = 'MISCCHG' and AMSCHRG = CTLELEMENT

where exists
(select * from APPLSHIP b
where a.ASHPNBR = b.ASHPNBR
and a.ABLDNGD = b.ABLDNGD
and a.APLSQNB = b.APLSQNB
)
group by ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG,CTLTEXT3
order by ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG))

So the following [excuse any strange formatting, as I am trying to
test what might defeat the loss of formatting by the archives; replace '
' (required space) with ' ' (blank) to correct]:

insert into bzmiscchg OVERRIDING USER VALUE
select ' ','I',ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG
,CTLTEXT3 ChgDesc,sum(AMSCHRG2) chgamt
,replace(char(curdate(),iso),'-','') || 'T' ||
replace(char(curTime()),':','')
,' ',' '
from miscchg3 a
left outer join
ctltable
on CTLTABLE = 'MISCCHG' and AMSCHRG = CTLELEMENT
where exists ( select '*'
from APPLSHIP b
where a.ASHPNBR = b.ASHPNBR
and a.ABLDNGD = b.ABLDNGD
and a.APLSQNB = b.APLSQNB
)
group by ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG,CTLTEXT3
order by ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG