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!

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

how to create parameterized query

Status
Not open for further replies.

namas

Technical User
Aug 31, 2006
31
US
environment: Crystal 8.5

How to create parameterized query?

Following SQL runs ok in Crystal SQL Designer.
This query will be used a data source in crystal report.
Fields 'gl_year' and 'gl_period' will need to be parameter fields.
So, when user runs the report, it will ask for year and
period and runs the query. Thanks.

SELECT gl_balance.gl_acct, gl_master.gl_abbrev_desc,
(SELECT Sum(gl_balance1.gl_balance_amt)
FROM gl_balance as gl_balance1, fd_master as fd_master1
WHERE gl_balance1.gl_acct = gl_balance.gl_acct
AND gl_balance1.gl_year = 2006
AND gl_balance1.gl_period < 1
AND gl_balance1.gl_period >= 0
AND fd_master1.fd_id = gl_balance1.fd_id) AS GL_BEGBAL,
(SELECT Sum(gl_balance2.gl_balance_debit)
FROM gl_balance as gl_balance2, fd_master as fd_master2
WHERE gl_balance2.gl_acct = gl_balance.gl_acct
AND gl_balance2.gl_year = 2006
AND gl_balance2.gl_period <= 1
AND gl_balance2.gl_period >= 1
AND fd_master2.fd_id = gl_balance2.fd_id) AS GL_DEBITS,
(SELECT Sum(gl_balance3.gl_balance_credit)
FROM gl_balance as gl_balance3, fd_master as fd_master3
WHERE gl_balance3.gl_acct = gl_balance.gl_acct
AND gl_balance3.gl_year = 2006
AND gl_balance3.gl_period <= 1
AND gl_balance3.gl_period >= 1
AND fd_master3.fd_id = gl_balance3.fd_id) AS GL_CREDITS
FROM gl_master INNER JOIN (gl_balance INNER JOIN fd_master ON gl_balance.fd_id = fd_master.fd_id) ON (gl_master.gl_id = gl_balance.gl_acct) AND (gl_master.fd_company = fd_master.fd_company)
WHERE (((gl_balance.gl_year)=2006) AND ((gl_balance.gl_period)<=1))
GROUP BY gl_balance.gl_acct, gl_master.gl_abbrev_desc
ORDER BY gl_balance.gl_acct, gl_master.gl_abbrev_desc;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top