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