× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.