asked on

Report from Query

It seems like this should be easy, but I can't fnd it.
I'm trying to create a report with Crystal Reports drivvel by an SQL Query.
The query Joins several tables, One of them twice withe different aliases.
Some of the joins are on different dats types ( int and char )
Crystal will not let me create the joins in the 'Database Expert', ther is no way to retrict the Join specifying contants. I.E.
LEFT JOIN GLINTCO J
ON N.COMPANY = J.COMPANY_A
AND N.LEVEL_DETAIL_01 = J.ZONE_B
AND J.RECORD_TYPE='L'
AND J.SYSTEM='ZC'
Seems like ther should be an easy way to specify the query string that drive the report an treat it much like a table in Crystal, but I can't find it.

Select
     P.COMPANY , P.VENDOR, P.INVOICE, P.TRANS_NBR
     ,D.ORIG_TRAN_AMT,D.DIST_COMPANY, D.DIS_ACCT_UNIT, D.DIS_ACCOUNT, D.DIS_SUB_ACCT
     ,D.ORIG_TRAN_AMT
     ,N.LEVEL_DETAIL_01
   
     ,I.AR_ACCOUNT_A CRACCT1,I.AR_ACCTUNIT_A CRUNIT1,I.AR_SUB_ACCT_A CRSUB1
     ,I.AP_ACCOUNT_B DBACCT1,I.AP_ACCTUNIT_B DBUNIT1,I.AP_SUB_ACCT_B DBSUB1
   
     ,J.AR_ACCOUNT_B CRACCT2,J.AR_ACCTUNIT_B CRUNIT2,J.AR_SUB_ACCT_B CRSUB2
     ,J.AR_ACCOUNT_A DBACCT2,J.AR_ACCTUNIT_A DBUNIT2,J.AR_SUB_ACCT_A DBSUB2
    
     from APPAYMENT P
     left join APDISTRIB D
      ON  P.COMPANY = D.COMPANY
      AND P.VENDOR = D.VENDOR
      AND P.INVOICE = D.INVOICE
      AND P.SUFFIX = D.SUFFIX
      AND P.CANCEL_SEQ = D.CANCEL_SEQ
     LEFT JOIN GLNAMES N
      ON D.DIST_COMPANY = N.COMPANY AND D.DIS_ACCT_UNIT = N.ACCT_UNIT

     LEFT JOIN GLINTCO I
      ON N.COMPANY = I.COMPANY_A
      AND N.LEVEL_DETAIL_01 = I.ZONE_B
      AND I.RECORD_TYPE='L'
      AND I.SYSTEM='AP'

     LEFT JOIN GLINTCO J
      ON N.COMPANY = J.COMPANY_A
      AND N.LEVEL_DETAIL_01 = J.ZONE_B
      AND J.RECORD_TYPE='L'
      AND J.SYSTEM='ZC'
     where P.COMPANY=' & Company & ' AND TRANS_NBR in ('1500','1501','1502','1503')
     and I.AP_ACCOUNT_A is not null
     order by P.TRANS_NBR,P.INVOICE

Open in new window

almander

The best option here is to create a View in the database. Then reference the view in the report.

kinsey

ASKER

Can't touch the database! Just query, read only.

almander

There is no way that I know of, to specify the actual Query text in Crystal.

However, I have gotten around this type of limitation by specifying the Join criteria in the Query. But, this is:
1. Not Officially Supported, or recommended.
2. Takes longer, and takes more resources to run the report.

For example, you could add the table in question (GLINTCO), and remove the links in the database expert (usually crystal warns you that this is not supported in some way).

Then in the Record Selection criteria, specify your join criteria.

{N.COMPANY} = {GLINTCO.COMPANY_A}
AND {N.LEVEL_DETAIL_01} = {GLINTCO.ZONE_B}
AND {GLINTCO.RECORD_TYPE}='L'
AND {GLINTCO.SYSTEM}='ZC'

This should effectively create the same result as the join would have.

ASKER CERTIFIED SOLUTION

kinsey

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

almander

Very Nice

Mike McCracken

That is the method.

Writ ethe command in the syntax of the database. It will be passed to the database before the report is opened.

mlmcc

Mike McCracken

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.