Hi,
I'm using Crystal Reports XI.
Is it possible to add parameter fields in SQL Expressions?
I want to create a subquery in an SQL expression and in that subquery I need to use the parameterfields from the report to select the correct record.
Example
Select CONTRACT_INVOICE.INVOICE_YEAR, CONTRACT_INVOICE.INVOICE_MNTH, CONTRACT_INFO.STANDARD_QTY
FROM CONTRACT_INVOICE, CONTRACT_INFO
WHERE CONTRACT_INVOICE.CONTRACT_ID = CONTRACT_INFO.CONTRACT_ID
AND CONTRACT_INFO.VALID_FROM_DATE = (select max(cin.VALID_FROM_DATE)
from CONTRACT_INFO cin, CONTRACT_INVOICE ci
where cin.CONTRACT_ID = ci.CONTRACT_ID
and valid_from_date <= last_day(to_date('1.'||TO_CHAR(ci.INVOICE_MNTH )||'.'||TO_CHAR(ci.invoice_year), 'dd.mm.yyyy'))
and ci.invoice_year = invoice_year
and ci.sequence_no = sequence_no
)
where invoice_year and sequence_no are parameters from the report.
If this is not possible and I need to put it in an sql command, do I have to put the whole query in the sql command, or can I have only the subquery here, and still reference the parmeters from the main report.
Thanks for any help!
Greta
I'm using Crystal Reports XI.
Is it possible to add parameter fields in SQL Expressions?
I want to create a subquery in an SQL expression and in that subquery I need to use the parameterfields from the report to select the correct record.
Example
Select CONTRACT_INVOICE.INVOICE_YEAR, CONTRACT_INVOICE.INVOICE_MNTH, CONTRACT_INFO.STANDARD_QTY
FROM CONTRACT_INVOICE, CONTRACT_INFO
WHERE CONTRACT_INVOICE.CONTRACT_ID = CONTRACT_INFO.CONTRACT_ID
AND CONTRACT_INFO.VALID_FROM_DATE = (select max(cin.VALID_FROM_DATE)
from CONTRACT_INFO cin, CONTRACT_INVOICE ci
where cin.CONTRACT_ID = ci.CONTRACT_ID
and valid_from_date <= last_day(to_date('1.'||TO_CHAR(ci.INVOICE_MNTH )||'.'||TO_CHAR(ci.invoice_year), 'dd.mm.yyyy'))
and ci.invoice_year = invoice_year
and ci.sequence_no = sequence_no
)
where invoice_year and sequence_no are parameters from the report.
If this is not possible and I need to put it in an sql command, do I have to put the whole query in the sql command, or can I have only the subquery here, and still reference the parmeters from the main report.
Thanks for any help!
Greta