Parameter fields in SQL Expressions

[GretaD]

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

 

[Madawc]

I had a similar problem, though I was using a set of temporary tables to collect data. In one of them I found the age in months:

Age_Months=DATEDIFF(month, cus01.Birthdate, AA.DateStamp)

I was looking for customers who were approaching 24, but using a parameter in the Stored Procedure:

Age_Months < ((24*12) - {?Months_Before})

Maybe you can adapt this.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[synapsevampire]

Instead of using a SQL Expression, use the entire query in a Command Object as the data source (1st thing in the list under your connection in the Database Expert.

Then you can add Crystal parameters within it, you'll see the option and they are still Crystal parameters, they are just limited a bit more. So the statement of can you still use the parameters from the main report doesn't make sense, these are still in the main report.

Please rememeber to also post your database type and connectivity.

-k

 

[GretaD]

synapsevampire,

Thanks for your answer. I am using Oracle 9 database. This is a complicated report with several sub-reports in it. If I use a Command object for the entire sql how will this effect the subreport links and the formula fields that I already have made?

madawc,
thanks for your answer too. I'm not sure if I can use your example, it is not quite the same problem (I think..) I need to find the max date of a table but limited up to the last day of the invoice month (from anonther table).

I have a lot of similar problems in several reports, so if I can find a solution to this it will help me a lot !

Thanks again
Greta

 

[Madawc]

Check DATEPART - does both months and years.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You could create the subquery in a command and then link it to a report table, and this might be the simplest approach with a complex report. However, the link itself, I believe, occurs locally and therefore can slow your report. You might want to try it and see what kind of performance hit there is.

-LB

 

[GretaD]

Thanks Ibass, just what I was wondering if was possible. I will try this. I also thought of putting the subquery in a PL/SQL function, and might do this if a command does not solve my problem.

I also have another problem where I should use parameters in a SQL expression, maybe you or someone else can help me on this also ?
I have an annual report that can go from january to december or from october to september. To control which type it is there is a database field called CONTR_YEAR_START which is a number for the start month(1 or 10). As an input parameter to the report I only get a number for year, and I have to check the start month to see what the date interval should be.
I want to create a record selection formula that looks something like this

date between to_date('01.'||CONTR_YEAR_START||'.'||{?Input Year}, 'dd.mm.yyyy') and to_date('01.'||CONTR_YEAR_START||'.'||({?Input Year}+1), 'dd.mm.yyyy')-1

That is: date between <first day of year> and <last day of year>
Hope this was understandable and that you can help me!

Thanks!!

Greta

 

[lbass]

Not sure why you would need a SQL expression for this, if you set up your two parameters as numbers. In CR, the record selection formula would look like this:

{table.date} in date({?year},{?month},01) to
dateserial({?year},{?month}+12,01)-1

-LB

 

[GretaD]

lbass,
thanks for your answer. This formula probably works fine, but the problem is that it doesn't seem to be pushed down to the database, which means I will get a lot more data than nececary from the database, which will slow down performance.

Is there any way to make this formula so that it will be pushsed down to the database?

Greta

 

[lbass]

When I test the formula, it DOES pass to the SQL query (database->show SQL query).

-LB

 

[GretaD]

Again, thanks for your quick answer

Maybe I have done something wrong then.. Here's my record selection formula:
{CONTRACT_INFO.REVISION_NO} = {%Max Revision No} and
{CONTRACT_INFO.VALID_FROM_DATE} = {%Max valid from date} and
{CONTR_ACCUMULATOR.CONTR_ACC_DATE} in date({?Invoice Year},{CONTRACT.CONTR_YEAR_START},01) to
dateserial({?Invoice Year},{CONTRACT.CONTR_YEAR_START}+12,01)-1

This results in the following where clause in the SQL query:
WHERE "CONTRACT_INFO"."REVISION_NO"=(pck_report_functions.contract_info_revision_no("CONTRACT_INVOICE"."CONTRACT_ID",last_day(to_date('01.'||"CONTRACT_INVOICE"."INVOICE_MNTH" ||'.'||"CONTRACT_INVOICE"."INVOICE_YEAR", 'dd.mm.yyyy')) ) ) AND "CONTRACT_INFO"."VALID_FROM_DATE"=(pck_report_functions.contract_info_valid_from("CONTRACT_INVOICE"."CONTRACT_ID",last_day(to_date('01.'||"CONTRACT_INVOICE"."INVOICE_MNTH" ||'.'||"CONTRACT_INVOICE"."INVOICE_YEAR", 'dd.mm.yyyy')) ) )

only the two first criterias have been included in the where clause.

Thanks!
Greta

 

[lbass]

I've never seen a SQL query that looks quite like that. I don't know why it doesn't pass for you. It works when I test it. Sorry.

-LB

 

[GretaD]

thanks lbass for your time and effort in trying to help me.

I think I might have found a workaraoud for this problem. So for anyone who is interessted this is what I have done:

problem: if input year = 2005 and start = 10 contract year is between 1.10.2005 and 30.09.2006, if input year = 2005 and start =1 contract year is beteween 1.1.2005 and 31.12.2005)

instead of creating dates of the input year I decided to create "contract years" of the dates I should test on. To do that I created a SQL Expression with the follwoing code:

decode(<contract_year_start>, 1, extract (year from <date>), 10, extract (year from add_months(<date>, -9))

Greta



this field I can use in my selection formula to test against the parameterfield.