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

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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
> > ______________________________________________________________________