Need help making this query/formula work in CR 10

[sam1one]

Hello:

I need help in making this query/formula work in CR 10, any ideas?

SELECT O.VENDORID, O.DOCNUMBR, O.TRXDSCRN AS DESCRIPTION,
CONVERT (CHAR(10), O.DOCDATE, 101) AS DOCDATE, G.JRNENTRY,
CONVERT (CHAR(10), O.PSTGDATE, 101) AS GL_DATE,
O.DOCAMNT, H.DEBITAMT, H.CRDTAMNT,
RTRIM(A.ACTNUMST) AS 'GL_ACCT_NUMBER',
B.ACTDESCR AS 'ACCOUNT_DESCRIPTION',
G.USWHPSTD AS 'WHO POSTED'

FROM PM30600 AS H, PM30200 AS O, GL00105 AS A,
GL00100 AS B, GL20000 AS G

WHERE O.DOCDATE < @DATE
AND O.PSTGDATE >= @DATE
AND O.VCHRNMBR = H.VCHRNMBR
AND H.DOCTYPE = 1
AND A.ACTINDX = H.DSTINDX
AND A.ACTINDX = B.ACTINDX
AND G.ORCTRNUM = H.VCHRNMBR
AND G.ORMSTRID = H.VENDORID
AND O.VOIDED = 0

UNION

SELECT O.VENDORID, O.DOCNUMBR, O.TRXDSCRN AS DESCRIPTION,
CONVERT (CHAR(10), O.DOCDATE, 101) AS DOCDATE, G.JRNENTRY,
CONVERT (CHAR(10), O.PSTGDATE, 101) AS GL_DATE,
O.DOCAMNT, H.DEBITAMT, H.CRDTAMNT,
RTRIM(A.ACTNUMST) AS 'GL_ACCT_NUMBER',
B.ACTDESCR AS 'ACCOUNT_DESCRIPTION',
G.USWHPSTD AS 'WHO POSTED'

FROM PM10100 AS H, PM20000 AS O, GL00105 AS A,
GL00100 AS B, GL20000 AS G

WHERE O.DOCDATE < @DATE
AND O.PSTGDATE >= @DATE
AND O.VCHRNMBR = H.VCHRNMBR
AND O.DOCTYPE = 1
AND A.ACTINDX = H.DSTINDX
AND A.ACTINDX = B.ACTINDX
AND G.ORCTRNUM = H.VCHRNMBR
AND G.ORMSTRID = H.VENDORID
AND O.VOIDED = 0

ORDER BY O.VENDORID

 

[lbass]

Did you try entering this as a command? Go to database->database expert->your datasource->add command and enter it there. The only element that stands that I think you will need to change is the formula {@date}. Or is this meant to be a parameter? Create the parameter within the command and add it to both sides of the union statement.

-LB

 

[sam1one]

Hello LB:

Thank you for your prompt reply, but the Add Command feature is grayed out and I can't update it?

What should I do?

Thanks,
Sam

 

[lbass]

Sorry, I don't know, but someone else probably does. Be sure to identify your database.

-LB

 

[gav12345]

I had this problem some time ago and in the end had to work round it. However, elsewhere on Tek-Tips its been suggested that you try connecting to your database differently. I.e. If you're connecting natively try connecting via ODBC etc.

Gavin

 

[sam1one]

Thanks fcr your replies. I was able to create a command, but when click ok, I get a message "Database Warning More than one datasource or a stored procedure has been used in this report. Please make sure that no SQL Expression is added and no server-side group-by is performed"

That means that I cannot add this command, correct?

And if so, what are my other choices?

Thank you very much...

 

[lbass]

No, you can use it. Just ignore the message.

-LB

 

[sam1one]

If so, why am I not seeing the results when I add it to the fields on my Report?

 

[muffntuf]

Did you try to pull in anything else besides the command query? Crystal doesn't like a command object and other tables together, but its still doable.