Error: "On full left cross inner right using exception"

Question

Post reply

Error: "On full left cross inner right using exception"

gatorfe

SSC-Addicted

Points: 416
More actions
October 5, 2014 at 5:33 pm

#311527

Hello, I am trying to validate this syntax below in winsql against a db2 on an as/400. I get the message that shows up in the subject line above. Not sure how to get around this. Any suggestions would greatly be appreciated.
SELECT
A."CCUST" AS "CUSTOMER_NO",
A."CNME" AS "CUSTOMER_NM",
B."CTDESC" AS "CUSTOMER_GROUP_NM",
C."SNAME" AS "CUSTOMER_CAT15_DESC",
D."TMDESC" AS "TERMS DESCRIPTION",
E.CCCODE AS ZCC_CODE
FROM
ERPLXF."RCM" A
JOIN ERPLXF."RCT" B on B."CTCOMP"=A."CCOMP" AND B."CTCSTP"=A."CTYPE" AND B."CTCURR" = A."CCURR"
JOIN ERPLXF."SSM" C on C."SSAL" = A."CSAL"
JOIN ERPLXF."RTM" D and D."TMTERM"=A."CTERM" and D."TMCMPN" = A."CCOMP"
LEFT OUTER JOIN ERPLXF."ZCC" E ON E."CCCODE"=A."CREF01"
WHERE
A."CCUST" <> 0 and E."CCID" = 'CC' and E."CCTABL" = 'SCRF1'

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

The SQL syntax looks fine to me.

What if you tried a DB2 forum, since this is probably a DB2 specific error?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Looks to me like an error in the query:

SELECT

A."CCUST" AS "CUSTOMER_NO",

A."CNME" AS "CUSTOMER_NM",

B."CTDESC" AS "CUSTOMER_GROUP_NM",

C."SNAME" AS "CUSTOMER_CAT15_DESC",

D."TMDESC" AS "TERMS DESCRIPTION",

E.CCCODE AS ZCC_CODE

FROM

ERPLXF."RCM" A

JOIN ERPLXF."RCT" B on B."CTCOMP"=A."CCOMP" AND B."CTCSTP"=A."CTYPE" AND B."CTCURR" = A."CCURR"

JOIN ERPLXF."SSM" C on C."SSAL" = A."CSAL"

JOIN ERPLXF."RTM" D and ON D."TMTERM"=A."CTERM" and D."TMCMPN" = A."CCOMP"

LEFT OUTER JOIN ERPLXF."ZCC" E ON E."CCCODE"=A."CREF01"

WHERE

A."CCUST" <> 0 and E."CCID" = 'CC' and E."CCTABL" = 'SCRF1'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

ChrisM@Work (10/6/2014)
Looks to me like an error in the query:
...

Good catch.

And that's why we use the SQL formatting people 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Koen Verbeeck (10/6/2014)
ChrisM@Work (10/6/2014)
Looks to me like an error in the query:
...
Good catch.
And that's why we use the SQL formatting people 😀

+1

Our little team's working with DB2. If you think MS error messages can be misleading, you should see the stuff IBM throws at you. No wonder the OP was confused;-)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

ChrisM@Work (10/6/2014)
Koen Verbeeck (10/6/2014)
ChrisM@Work (10/6/2014)
Looks to me like an error in the query:
...
Good catch.
And that's why we use the SQL formatting people 😀
+1
Our little team's working with DB2. If you think MS error messages can be misleading, you should see the stuff IBM throws at you. No wonder the OP was confused;-)

Indeed. I'd expect a syntax error.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

gatorfe SSC-Addicted Points: 416 More actions · Answer 6

gatorfe

SSC-Addicted

Points: 416

October 6, 2014 at 8:34 am

#1750322

Thanks everyone for their help!