Your WHERE EXISTS is ahead of the LEFT JOIN - you also need parentheses
around the SELECT that is populating the INSERT. 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))
On 2/15/2013 5:09 PM, Stone, Joel wrote:
I tried if after the join also - still no luck.
I think that I tried every possible sequence.
Is it possible?
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Friday, February 15, 2013 5:07 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL: how to combine a join with WHERE EXISTS
Joel
You have a WHERE clause ahead of the LEFT JOIN - that's the wrong order.
:)
Time to go home, man!
Vern
On 2/15/2013 5:04 PM, Stone, Joel wrote:
Im trying to combine a WHERE EXISTS with a left outer join.
I am receiving error "keyword LEFT not expected".
Is there a way to make this happen?
Thanks
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
where exists
(select * from APPLSHIP b
where a.ASHPNBR = b.ASHPNBR
and a.ABLDNGD = b.ABLDNGD
and a.APLSQNB = b.APLSQNB
)
left outer join ctltable
on CTLTABLE = 'MISCCHG' and AMSCHRG = CTLELEMENT
group by ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG,CTLTEXT3
order by ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG
______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
