What might be wrong with this SQL?

[former_member584790]

HI I am getting a failed message on Rowset near "ON" any ideas why this would fail? I am running this in a Crystal as a command.

SELECT

IV00101.ITMGEDSC,

IV00101.ITEMDESC,

RM00101.CUSTNAME,

IV40600.UserCatLongDescr,

CATS.UserCatLongDescr,

SOP10200.ITEMNMBR,

SOP10200.SOPNUMBE,

SOP10200.QUANTITY,

SOP10200.OXTNDPRC,

SOP10200.SOPTYPE,

SOP10100.DOCDATE,

'Current' as source

FROM

PBS.dbo.SOP10100 SOP10100 ON

(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND

(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)

INNER JOIN

PBS.dbo.IV00101 IV00101 ON

SOP10200.ITEMNMBR=IV00101.ITEMNMBR

INNER JOIN

PBS.dbo.RM00101 RM00101 ON

SOP10100.CUSTNMBR=RM00101.CUSTNMBR

LEFT OUTER JOIN

PBS.dbo.IV40600 IV40600 ON

IV00101.ITMGEDSC=IV40600.USCATVAL

LEFT OUTER JOIN

PBS.dbo.IV40600 CATS ON

IV00101.USCATVLS_2=CATS.USCATVAL

UNION

SELECT

IV00101.ITMGEDSC,

IV00101.ITEMDESC,

RM00101.CUSTNAME,

IV40600.UserCatLongDescr,

CATS.UserCatLongDescr,

SOP10200.ITEMNMBR,

SOP10200.SOPNUMBE,

SOP10200.QUANTITY,

SOP10200.OXTNDPRC,

SOP10200.SOPTYPE,

SOP10100.DOCDATE,

'History' as source

FROM

PBS.dbo.SOP30200 SOP30200 ON

(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND

(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE)

INNER JOIN

PBS.dbo.IV00101 IV00101 ON

SOP30300.ITEMNMBR=IV00101.ITEMNMBR

INNER JOIN

PBS.dbo.RM00101 RM00101 ON

SOP30200.CUSTNMBR=RM00101.CUSTNMBR

LEFT OUTER JOIN

PBS.dbo.IV40600 IV40600 ON

IV00101.ITMGEDSC=IV40600.USCATVAL

LEFT OUTER JOIN

PBS.dbo.IV40600 CATS ON

IV00101.USCATVLS_2=CATS.USCATVAL

[Former Member]

Hi,

FROM

*PBS.dbo.SOP10100 SOP10100 ON*

(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND

(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)

U can replace this with

FROM PBS.dbo.SOP10200 INNER JOIN SOP10100 ON

(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND

(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)

OR

FROM (SELECT FIELDNAME FROM PBS.dbo.SOP10100 , SOP10200

WHERE SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND

(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)

REGARDS,

sATHISH

[former_member584790]

Satish, I ran it on the SQL Server it has this error.

Msg 156, Level 15, State 1, Line 53

Incorrect syntax near the keyword 'ON'.

[Former Member]

It's because you're trying to join those first two tables to tables that aren't actually in the query. Either remove the ON clauses, or add in the tables that they refer to.

[former_member584790]

Which 2 tables do you mean? In the Select up top there is the SOP10100 which has the doc date.

[Former Member]

But SOP10200 and SOP30300 aren't.

[former_member584790]

Garret, When you say not in the Query - you mean the joins?

[Former Member]

Philky,

Please try this query, I have added some sql appearing in bold.

SELECT

IV00101.ITMGEDSC,

IV00101.ITEMDESC,

RM00101.CUSTNAME,

IV40600.UserCatLongDescr,

CATS.UserCatLongDescr,

SOP10200.ITEMNMBR,

SOP10200.SOPNUMBE,

SOP10200.QUANTITY,

SOP10200.OXTNDPRC,

SOP10200.SOPTYPE,

SOP10100.DOCDATE,

'Current' as source

FROM

PBS.dbo.SOP10100 SOP10100 inner join PBS.dbo.SOP10200 SOP10200 ON

(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND

(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)

INNER JOIN

PBS.dbo.IV00101 IV00101 ON

SOP10200.ITEMNMBR=IV00101.ITEMNMBR

INNER JOIN

PBS.dbo.RM00101 RM00101 ON

SOP10100.CUSTNMBR=RM00101.CUSTNMBR

LEFT OUTER JOIN

PBS.dbo.IV40600 IV40600 ON

IV00101.ITMGEDSC=IV40600.USCATVAL

LEFT OUTER JOIN

PBS.dbo.IV40600 CATS ON

IV00101.USCATVLS_2=CATS.USCATVAL

UNION

SELECT

IV00101.ITMGEDSC,

IV00101.ITEMDESC,

RM00101.CUSTNAME,

IV40600.UserCatLongDescr,

CATS.UserCatLongDescr,

SOP10200.ITEMNMBR,

SOP10200.SOPNUMBE,

SOP10200.QUANTITY,

SOP10200.OXTNDPRC,

SOP10200.SOPTYPE,

SOP10100.DOCDATE,

'History' as source

FROM

PBS.dbo.SOP30200 SOP30200 inner join PBS.dbo.SOP30300 SOP30300 ON

(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND

(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE)

INNER JOIN

PBS.dbo.IV00101 IV00101 ON

SOP30300.ITEMNMBR=IV00101.ITEMNMBR

INNER JOIN

PBS.dbo.RM00101 RM00101 ON

SOP30200.CUSTNMBR=RM00101.CUSTNMBR

LEFT OUTER JOIN

PBS.dbo.IV40600 IV40600 ON

IV00101.ITMGEDSC=IV40600.USCATVAL

LEFT OUTER JOIN

PBS.dbo.IV40600 CATS ON

IV00101.USCATVLS_2=CATS.USCATVAL

Thanks

-Azhar

Edited by: Abdul Rehman Mushtaque on May 7, 2009 7:32 AM

[former_member584790]

Azhar, this gets a "failed to open rowset " on the 102 fields and also on the docdate of the 101.

[Former Member]

Philky,

The scope of first SOP10100 is upto only the first Select statement, when you access it in the second select, you need to include it again as you have done it in the first select. I am just wondering if you are going wrong some where, I think you should query SOP30200 and SOP30300 in the second query, if not then include SOP10100 and SOP10200 in join of second select.

SELECT

IV00101.ITMGEDSC,

IV00101.ITEMDESC,

RM00101.CUSTNAME,

IV40600.UserCatLongDescr,

CATS.UserCatLongDescr,

SOP10200.ITEMNMBR,

SOP10200.SOPNUMBE,

SOP10200.QUANTITY,

SOP10200.OXTNDPRC,

SOP10200.SOPTYPE,

SOP10100.DOCDATE,

'Current' as source

FROM

PBS.dbo.SOP10100 SOP10100 inner join PBS.dbo.SOP10200 SOP10200 ON

(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND

(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)

INNER JOIN

PBS.dbo.IV00101 IV00101 ON

SOP10200.ITEMNMBR=IV00101.ITEMNMBR

INNER JOIN

PBS.dbo.RM00101 RM00101 ON

SOP10100.CUSTNMBR=RM00101.CUSTNMBR

LEFT OUTER JOIN

PBS.dbo.IV40600 IV40600 ON

IV00101.ITMGEDSC=IV40600.USCATVAL

LEFT OUTER JOIN

PBS.dbo.IV40600 CATS ON

IV00101.USCATVLS_2=CATS.USCATVAL

UNION

SELECT

IV00101.ITMGEDSC,

IV00101.ITEMDESC,

RM00101.CUSTNAME,

IV40600.UserCatLongDescr,

CATS.UserCatLongDescr,

SOP10200.ITEMNMBR,

SOP10200.SOPNUMBE,

SOP10200.QUANTITY,

SOP10200.OXTNDPRC,

SOP10200.SOPTYPE,

SOP10100.DOCDATE,

'History' as source

FROM

PBS.dbo.SOP30200 SOP30200 inner join PBS.dbo.SOP30300 SOP30300 ON

(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND

(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE)

INNER JOIN

PBS.dbo.IV00101 IV00101 ON

SOP30300.ITEMNMBR=IV00101.ITEMNMBR

INNER JOIN

PBS.dbo.RM00101 RM00101 ON

SOP30200.CUSTNMBR=RM00101.CUSTNMBR

LEFT OUTER JOIN

PBS.dbo.IV40600 IV40600 ON

IV00101.ITMGEDSC=IV40600.USCATVAL

LEFT OUTER JOIN

PBS.dbo.IV40600 CATS ON

IV00101.USCATVLS_2=CATS.USCATVAL

Hope this helps,

Thanks

-Azhar

[former_member584790]

this was it.

SELECT

IV00101.ITMGEDSC,

IV00101.ITEMDESC,

RM00101.CUSTNAME,

IV40600.UserCatLongDescr,

CATS.UserCatLongDescr,

SOP10200.ITEMNMBR,

SOP10200.SOPNUMBE,

SOP10200.QUANTITY,

SOP10200.OXTNDPRC,

SOP10200.SOPTYPE,

SOP10100.DOCDATE,

'Current' as source

FROM ((((PBS.dbo.SOP10200

SOP10200 INNER JOIN PBS.dbo.IV00101 IV00101 ON

SOP10200.ITEMNMBR=IV00101.ITEMNMBR)

INNER JOIN

PBS.dbo.SOP10100 SOP10100 ON

(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND

(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE))

INNER JOIN

PBS.dbo.IV40600 IV40600 ON

IV00101.ITMGEDSC=IV40600.USCATVAL)

INNER JOIN

PBS.dbo.IV40600 CATS ON

IV00101.USCATVLS_2=CATS.USCATVAL)

INNER JOIN

PBS.dbo.RM00101 RM00101 ON

SOP10100.CUSTNMBR=RM00101.CUSTNMBR

UNION ALL

SELECT

IV00101.ITMGEDSC,

IV00101.ITEMDESC,

RM00101.CUSTNAME,

IV40600.UserCatLongDescr,

CATS.UserCatLongDescr,

SOP30300.ITEMNMBR,

SOP30300.SOPNUMBE,

SOP30300.QUANTITY,

SOP30300.OXTNDPRC,

SOP30300.SOPTYPE,

SOP30200.DOCDATE,

'History' as source

FROM ((((PBS.dbo.SOP30300

SOP30300 LEFT OUTER JOIN PBS.dbo.IV00101 IV00101 ON

SOP30300.ITEMNMBR=IV00101.ITEMNMBR)

INNER JOIN

PBS.dbo.SOP30200 SOP30200 ON

(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND

(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE))

LEFT OUTER JOIN

PBS.dbo.IV40600 IV40600 ON

IV00101.ITMGEDSC=IV40600.USCATVAL)

LEFT OUTER JOIN

PBS.dbo.IV40600 CATS ON

IV00101.USCATVLS_2=CATS.USCATVAL)

INNER JOIN

PBS.dbo.RM00101 RM00101 ON

SOP30200.CUSTNMBR=RM00101.CUSTNMBR

[Former Member]

Hi,

Try replacing ON with WHERE in the following code,

FROM

PBS.dbo.SOP10100 SOP10100 WHERE

(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND

(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)

Regards,

Praveen

[former_member584790]

Nope it doesn't like Where' as well.