Skip to Content
0
Jun 05, 2009 at 05:06 AM

You cannot have command as the datasource with parameters that get passed?

29 Views

I wrote a Crystal Report using a SQL command shown below. The SQL in effect creates a table called Command with the specified fields. This CR works exactly as we requested. Now I am told that they are using some kind of Interface, written in VB6, not sure exactly of all this, but that it cannot accept this Command as the datasource. The developer says that the interface is expecting SQL Server tables. Does this sound right? The interface is a repository of all reports with parameters.

SELECT

IV00101.ITMGEDSC,

IV00101.ITEMDESC,

RM00101.CUSTNAME,

RM00101.CPRCSTNM,

RM00101.CUSTNMBR,

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,

RM00101.CPRCSTNM,

RM00101.CUSTNMBR,

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