SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL query

Author

Topic

sureshprpt
Starting Member

33 Posts

Posted - 2013-08-30 : 02:34:28

Hi,

We changed our IBM server to new version , but the following query is working fine in my old version , but the same query will not working. The error message also mentioned below

Query

SELECT F00365.ONDATE "INVOICE DATE", F42119.SDDOCO "SO NO",F42119.SDDCTO "TY" ,F42119.SDMCU "BP" ,F42119.SDDOC "INVOICE" ,F42119.SDDCT "TYPE" , F42119.SDLITM "ITEMCODE" ,F42119.SDDSC1 "DESCRIPTION",F42119.SDGLC "GL" ,F0101.ABALPH "CUSTOMER" , F42119.SDUOM "UOM", F42119.SDSOQS/1000 "QTY" ,ROUND (F42119.SDITWT /10000,2) "WEIGHT" ,DOUBLE(F42119.SDSOQS*F4074.ALUPRC)/10000000 "VALUE" FROM P2DTAA/F42119 JOIN F0101 ON F42119.SDAN8 = F0101.ABAN8 JOIN F4074 ON F42119.SDDOCO = F4074.ALDOCO AND F42119.SDLNID = F4074.ALLNID AND F42119.SDDCTO = F4074.ALDCTO JOIN F00365 ON F42119.SDIVD = F00365.ONDTEJ WHERE F42119.SDKCOO = 03450 AND F42119.SDIVD > 113212 AND F4074.ALOSEQ =10 AND F42119.SDDCTO NOT LIKE 'G%

Error :

CWBDB0036 - Server returned SQL error

SQL0338 - JOIN predicate or MERGE ON clause not valid.

Cause . . . . . : The JOIN predicate, or ON clause of a MERGE statement, is not valid because a column is specified that exists in a table that is outside the scope of the join predicate or ON clause. The scope is generally determined from left to right but is also based on the position of the join-condition. If parentheses are used, columns inside the parentheses cannot come from a table outside the parentheses. If an implicit join (using a comma) is specified prior to a joined table with an ON clause, columns in the ON clause cannot come from a table before the comma. Recovery . . . : Do one of the following and try the request again: -- Make certain that the column names, table names, and any qualifiers are specified correctly. -- Specify parentheses around joined tables to specify a join order other than left to right. Ensure columns exist in tables that are in the same scope. -- For an ON clause in a MERGE statement, ensure referenced columns are within the scope of the ON clause. -- Specify a CROSS JOIN instead of an implicit join.

Please suggest to modify the query

Thanks

Suresh

Thanks & Regards
Suresh

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-30 : 02:49:13

[code]SELECT F00365.ONDATE "INVOICE DATE", F42119.SDDOCO "SO NO",F42119.SDDCTO "TY" ,F42119.SDMCU "BP" ,F42119.SDDOC "INVOICE" ,F42119.SDDCT "TYPE" , F42119.SDLITM "ITEMCODE" ,F42119.SDDSC1 "DESCRIPTION",F42119.SDGLC "GL" ,F0101.ABALPH "CUSTOMER" , F42119.SDUOM "UOM", F42119.SDSOQS/1000 "QTY" ,ROUND (F42119.SDITWT /10000,2) "WEIGHT" ,
DOUBLE(F42119.SDSOQS*F4074.ALUPRC)/10000000 "VALUE"

FROM P2DTAA/F42119 -- I think here is the error.... is "P2DTAA/" part is required? If Yes either put quotations or use alias name foe P2DTAA/F42119
JOIN F0101 ON F42119.SDAN8 = F0101.ABAN8
JOIN F4074 ON F42119.SDDOCO = F4074.ALDOCO
AND F42119.SDLNID = F4074.ALLNID
AND F42119.SDDCTO = F4074.ALDCTO
JOIN F00365 ON F42119.SDIVD = F00365.ONDTEJ
WHERE F42119.SDKCOO = 03450 AND F42119.SDIVD > 113212 AND F4074.ALOSEQ =10 AND F42119.SDDCTO NOT LIKE 'G%[/code]

--
Chandu

sureshprpt
Starting Member

33 Posts

Posted - 2013-08-30 : 03:09:22

No it is our library name contains list of tables , it is not come under quotes

Suresh

quote:

Originally posted by bandi

SELECT F00365.ONDATE "INVOICE DATE", F42119.SDDOCO "SO NO",F42119.SDDCTO "TY" ,F42119.SDMCU "BP" ,F42119.SDDOC "INVOICE" ,F42119.SDDCT "TYPE" , F42119.SDLITM "ITEMCODE" ,F42119.SDDSC1 "DESCRIPTION",F42119.SDGLC "GL" ,F0101.ABALPH "CUSTOMER" , F42119.SDUOM "UOM", F42119.SDSOQS/1000 "QTY" ,ROUND (F42119.SDITWT /10000,2) "WEIGHT" ,
DOUBLE(F42119.SDSOQS*F4074.ALUPRC)/10000000 "VALUE" 

FROM P2DTAA/F42119   -- I think here is the error.... is "P2DTAA/" part is required? If Yes either put quotations  or use alias name foe P2DTAA/F42119
JOIN F0101 ON F42119.SDAN8 = F0101.ABAN8 
JOIN F4074 ON F42119.SDDOCO = F4074.ALDOCO 
 AND F42119.SDLNID = F4074.ALLNID 
 AND F42119.SDDCTO = F4074.ALDCTO 
JOIN F00365 ON F42119.SDIVD = F00365.ONDTEJ 
WHERE F42119.SDKCOO = 03450 AND F42119.SDIVD > 113212 AND F4074.ALOSEQ =10 AND F42119.SDDCTO NOT LIKE 'G%

--
Chandu

Thanks & Regards
Suresh

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-30 : 03:13:05

Try either [P2DTAA/F42119] or P2DTAA/F42119 AS F42119

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-30 : 03:19:55

enclosing it [] should solve the issue also you need to create alias as F42119 if you want to use like F42119.SDLNID etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sureshprpt
Starting Member

33 Posts

Posted - 2013-08-30 : 03:22:07

quote:
Originally posted by bandi

Try either [P2DTAA/F42119] or P2DTAA/F42119 AS F42119

--
Chandu

Sorry i posted wrong sql previously.

The correct query is follows:-

SELECT F00365.ONDATE "INVOICE DATE", F4211.SDDOCO "SO NO",F4211.SDDCTO "TY" ,F4211.SDMCU "BP" ,F4211.SDDOC "INVOICE" ,F4211.SDDCT "TYPE" , F4211.SDLITM "ITEMCODE" ,F4211.SDDSC1 "DESCRIPTION",F4211.SDGLC "GL" ,A.ABAN8 "Customer No",A.ABALPH "CUSTOMER",B.ABAN8 "Consinne No",B.ABALPH"CONSIGNEE" ,A. ABAC09"VERTICAL CODE",A. ABAC11"REGION CODE",A. ABAC29 Company,F4211.SDPTC"PAYMENT CODE", F0014.PNPTD "PAYMENT TERMS", F4211.SDUOM "INVOICED UOM", F4211.SDSOQS/1000 "INVOICED QTY" ,F4211.SDUOM1"PRIMARY UOM",F4211.SDPQOR/1000 "QTY FOR PRIMARY ",ROUND (F4211.SDITWT /10000,2) "WEIGHT" ,DOUBLE(F4211.SDSOQS*F4074.ALUPRC)/10000000 "SELLING PRICE" FROM P2DTAA/F4211,P2DTAA/F0101 A,P2DTAA/F0101 B INNER JOIN F4074 ON F4211.SDDOCO = F4074.ALDOCO AND F4211.SDLNID = F4074.ALLNID AND F4211.SDDCTO = F4074.ALDCTO INNER JOIN F00365 ON F4211.SDIVD = F00365.ONDTEJ INNER JOIN F0014 ON F4211.SDPTC = F0014.PNPTC WHERE F4211.SDKCOO = 03450 AND F4211.SDIVD > 113212 AND F4074.ALOSEQ =10 AND F4211.SDDCTO NOT LIKE 'G%' AND F4211.SDAN8 = A.ABAN8 AND F4211.SDSHAN = B.ABAN8

Thanks & Regards
Suresh

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-30 : 05:31:47

Hi Suresh,
I just rectified syntax error..... I'm not clear about your query... ( why do you want to cross join the same table P2DTAA/F0101 )

SELECT F00365.ONDATE "INVOICE DATE"
	,F4211.SDDOCO "SO NO"
	,F4211.SDDCTO "TY" 
	,F4211.SDMCU "BP" 
	,F4211.SDDOC "INVOICE" 
	,F4211.SDDCT "TYPE" 
	,F4211.SDLITM "ITEMCODE" 
	,F4211.SDDSC1 "DESCRIPTION"
	,F4211.SDGLC "GL" 
	,A.ABAN8 "Customer No"
	,A.ABALPH "CUSTOMER"
	,B.ABAN8 "Consinne No"
	,B.ABALPH "CONSIGNEE" 
	,A.ABAC09 "VERTICAL CODE"
	,A.ABAC11 "REGION CODE"
	,A.ABAC29 Company
	,F4211.SDPTC "PAYMENT CODE"
	,F0014.PNPTD "PAYMENT TERMS"
	,F4211.SDUOM "INVOICED UOM"
	,F4211.SDSOQS/1000 "INVOICED QTY" 
	,F4211.SDUOM1"PRIMARY UOM"
	,F4211.SDPQOR/1000 "QTY FOR PRIMARY"
	,ROUND (F4211.SDITWT /10000,2) "WEIGHT" 
	,DOUBLE(F4211.SDSOQS*F4074.ALUPRC)/10000000 "SELLING PRICE" 
FROM P2DTAA/F4211 AS F4211 
INNER JOIN P2DTAA/F0101 A ON 1=1
INNER JOIN P2DTAA/F0101 B  ON 1=1
INNER JOIN F4074 ON F4211.SDDOCO = F4074.ALDOCO 
	AND F4211.SDLNID = F4074.ALLNID AND F4211.SDDCTO = F4074.ALDCTO 
INNER JOIN F00365 ON F4211.SDIVD = F00365.ONDTEJ INNER JOIN F0014 ON F4211.SDPTC = F0014.PNPTC 
WHERE F4211.SDKCOO = 03450 AND F4211.SDIVD > 113212 AND F4074.ALOSEQ =10 AND F4211.SDDCTO NOT LIKE 'G%' AND F4211.SDAN8 = A.ABAN8 AND F4211.SDSHAN = B.ABAN8

--
Chandu

sureshprpt
Starting Member

33 Posts

Posted - 2013-08-30 : 06:04:36

Chandu..

It is used to run the sale report. So we have two address number one is consginee and another is consignee .So the address numbers are store in F0101 table.

For this purpose only we use cross join function

Regards
Suresh

Thanks & Regards
Suresh

sureshprpt
Starting Member

33 Posts

Posted - 2013-08-30 : 07:13:03

Chandu,

Thanks now this query is working fine.

Regards
Suresh

quote:

Originally posted by bandi

Hi Suresh,
I just rectified syntax error..... I'm not clear about your query... ( why do you want to cross join the same table P2DTAA/F0101 )

SELECT F00365.ONDATE "INVOICE DATE"
	,F4211.SDDOCO "SO NO"
	,F4211.SDDCTO "TY" 
	,F4211.SDMCU "BP" 
	,F4211.SDDOC "INVOICE" 
	,F4211.SDDCT "TYPE" 
	,F4211.SDLITM "ITEMCODE" 
	,F4211.SDDSC1 "DESCRIPTION"
	,F4211.SDGLC "GL" 
	,A.ABAN8 "Customer No"
	,A.ABALPH "CUSTOMER"
	,B.ABAN8 "Consinne No"
	,B.ABALPH "CONSIGNEE" 
	,A.ABAC09 "VERTICAL CODE"
	,A.ABAC11 "REGION CODE"
	,A.ABAC29 Company
	,F4211.SDPTC "PAYMENT CODE"
	,F0014.PNPTD "PAYMENT TERMS"
	,F4211.SDUOM "INVOICED UOM"
	,F4211.SDSOQS/1000 "INVOICED QTY" 
	,F4211.SDUOM1"PRIMARY UOM"
	,F4211.SDPQOR/1000 "QTY FOR PRIMARY"
	,ROUND (F4211.SDITWT /10000,2) "WEIGHT" 
	,DOUBLE(F4211.SDSOQS*F4074.ALUPRC)/10000000 "SELLING PRICE" 
FROM P2DTAA/F4211 AS F4211 
INNER JOIN P2DTAA/F0101 A ON 1=1
INNER JOIN P2DTAA/F0101 B  ON 1=1
INNER JOIN F4074 ON F4211.SDDOCO = F4074.ALDOCO 
	AND F4211.SDLNID = F4074.ALLNID AND F4211.SDDCTO = F4074.ALDCTO 
INNER JOIN F00365 ON F4211.SDIVD = F00365.ONDTEJ INNER JOIN F0014 ON F4211.SDPTC = F0014.PNPTC 
WHERE F4211.SDKCOO = 03450 AND F4211.SDIVD > 113212 AND F4074.ALOSEQ =10 AND F4211.SDDCTO NOT LIKE 'G%' AND F4211.SDAN8 = A.ABAN8 AND F4211.SDSHAN = B.ABAN8

--
Chandu

Thanks & Regards
Suresh

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-02 : 01:35:55

quote:
Originally posted by sureshprpt

Chandu,

Thanks now this query is working fine.

Thanks & Regards
Suresh

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources