Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Failing SQL 2

Status
Not open for further replies.
pk400i

pk400i

MIS
Feb 10, 2009
102
US
HI This SQL is failing on the Crystal Reports command area. Any ideas what is the problem?

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
 
Sort by date Sort by votes
Which database and connect method?
Have you tried it directly against that database?




[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
the db is SQL server. it is the Microsoft Great Plains. I ran this as a Crystal Command.
 
Upvote 0 Downvote
Hi,
Should have asked this earlier - what error are you getting, how is it failing?

also can you execute it from SqlServer's query tool ( can't remember its name - SqlPlus in Oracle)...



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
First its a Crystal Reports error "Failed to open rowset"
then Query Engine Error 42000 Microsoft SQL Native Client SQL Server Incorrect Sequence near the keyword ON.

I will see about trying to run it directly in SQL server I am new to that so need some time.
 
Upvote 0 Downvote
I would say the error is here

FROM
PBS.dbo.SOP10100 SOP10100 ON
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)

This should be
PBS.dbo.SOP10100 SOP10100

and the

ON
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)

Changed to

WHERE
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)

Placed at the end of the each query

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

where
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)


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

where
(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND
(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE)
 
Upvote 0 Downvote
Ian, it errors as 'the multi part identifier SOP10200.ITEMNMBR Could not be bound'
 
Upvote 0 Downvote
I ran this on the SQL Server as a Query and got this error: same as Crystal basically.


Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP10200.ITEMNMBR" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP10200.SOPTYPE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP10200.SOPNUMBE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP10200.ITEMNMBR" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP10200.SOPNUMBE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP10200.QUANTITY" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP10200.OXTNDPRC" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP10200.SOPTYPE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP30300.ITEMNMBR" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP30300.SOPTYPE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP30300.SOPNUMBE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP10200.ITEMNMBR" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP10200.SOPNUMBE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP10200.QUANTITY" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP10200.OXTNDPRC" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP10200.SOPTYPE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SOP10100.DOCDATE" could not be bound.
 
Upvote 0 Downvote
Not too much help that is a huge site though I will have to spend more time over there.
 
Upvote 0 Downvote
Sorry did not spot that you do not have a table with the alias

SOP10200 in your current query or
SOP30300 in your history query

YOu need to join these tables in correctly and then delete my suggested where clause.

Ian
 
Upvote 0 Downvote
Ian, I have the 102 on the inner join. When you say Query do you mean the "FROM" part?
 
Upvote 0 Downvote
But you have not declared the table anywhere

YOu need something like

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 SOP10200.SOP10200 ON
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)

Repeat as required for other union query.

Ian
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Gary Hnat
  • Locked
  • Question
Crystal SQL Code linking to partial database field entries
Replies
2
Views
569
Gary Hnat
Gary Hnat
dgillz
  • Locked
  • Question
Cannot connect to any ODBC datasource with MS SQL Server data in crystal
Replies
8
Views
931
LyleU
LyleU
PeaEssEl
  • Locked
  • Question
ASP.Net Crystal Reports connection to SQL Server through published website
Replies
2
Views
134
PeaEssEl
PeaEssEl
Luvsql
  • Locked
  • Question
Using dateadd(year, -1, @MyDate) in sp does not bring any data with Crystal but does with SQL
Replies
1
Views
131
briangriffin
briangriffin
japeconsulting
  • Locked
  • Question
SQL command required to show the first delivery type for each customer and also a distinct count
Replies
1
Views
114
hilfy
hilfy

Part and Inventory Search

Sponsor

Back
Top