Date Parameters in Report Query

[2005 IR Help]

By: andersons - andersons

Date Parameters in Report Query

2005-06-27 07:09

I am trying to conduct a report query which will display information only for the dates specified by a parameter. I am using an Oracle database. Currently when I try to run the query, I receive one of several SQL errors.

 

If I run the report as

SELECT ... FROM ... WHERE DATE = $P!{BEGIN_DATE} , I get a SQL statement command not properly ended.

 

If I run the report as:

SELECT...FROM...WHERE DATE = "$P!{BEGIN_DATE}" , I get invalid identifier

 

I have the parameter BEGIN_DATE set up right now as java.util.Date and it's default expression is new Date(). DATE is an Oracle Timestamp.

 

I know Oracle and Java store dates differently so I'm wondering if there is a way to get around that.

 

I have tried searching through the forum for an answer but haven't found one yet. Any insight on this would be helpful. Thanks in advance!

 

-Susan

 

 

 

 

By: Todd - tberman333

RE: Date Parameters in Report Query

2005-06-27 07:28

We had this same issue with designing from an Oracle database. The only way we could find to get around it is to store the parameters as Text and use this statement in the SQL to convert it bak to a date:

 

WHERE DATE = to_date('$P!{BEGIN_DATE}', 'MM/DD/YYYY')

 

 

 

 

By: andersons - andersons

RE: Date Parameters in Report Query

2005-06-29 13:43

Thanks! Based on your idea, we were able to come up with a solution. We ended up having our query be:

 

WHERE TO_CHAR(DATE,'DD/MM/RRRR') = $P{BEGIN_DATE}

 

 

 

 

By: andersons - andersons

RE: Date Parameters in Report Query

2005-07-21 06:17

Hopefully this will work..Otherwise I don't know how to do it.

SELECT ...... FROM ...... WHERE ...... AND TO_CHAR(auewdz,'MM/DD/RRRR') = '01/01/2005'

 

-Susan

 

 

 

 

By: propsystem - propsystem

RE: Date Parameters in Report Query

2005-07-21 01:14

i still got prob using ur idea.can help me to convert this parameter date:

 

SELECT auewdz,AUBRCD||'K' STAFFNO,AUB2TX,AUFGTX,AUFHCD FROM TH03DTA.SMAUREL1 WHERE auasst = '1'

AND AUNXCD='04' AND SUBSTRING(AUBRCD,1,2) NOT IN ('AL','AP')and aufgtx like '%' and auewdz = '01/01/2005'

UNION

SELECT auewdz,AUBRCD STAFFNO,AUB2TX,AUFGTX,AUFHCD FROM TH03dta.SMAUREL1 WHERE auasst = '1' AND AUNXCD <>'04'

AND SUBSTRING(AUBRCD,1,2) NOT IN ('AL','AP') and aufgtx like '%' and auewdz ='01/01/2005'

order by aufhcd

 

 

 

 

By: Rick Millar - rmillar

RE: Date Parameters in Report Query

2005-07-21 13:54

Hmmm, not really sure what you are asking but I'll take a guess...

 

I assume you want to be able take the literal date in your WHERE clause and turn it into a parameter. If that is so, you first need to define a parameter using the parameter tool, then reference it in the WHERE clause. Set the parameter's datatype to be String. You can set the default expression for the parameter to be '01/01/2005' for testing.

 

Then your where clause (assuming that auewdz is a Date) should look like the following:

 

Where...

and auewdz = to_date('$P!{Your_date_parameter_name}', 'MM/DD/YYYY')

 

HTH,

 

Rick

 

 

 

 

 

By: propsystem - propsystem

RE: Date Parameters in Report Query

2005-07-22 02:14

thank you 4 ur idea.

i just want to know why we need to put this sign ! after P!{parametername}.before this when i assign parameter i didnt put this sign.

 

 

 

 

By: Rick Millar - rmillar

RE: Date Parameters in Report Query

2005-07-22 08:36

The short answer from me is, I don't really know why the symbol is needed. I've seen some explanations in this forum before but they haven't been clear enough for me to understand. I've seen examples where the symbol hasn't been used, but in my experience, at least when referencing a String parameter in the WHERE clause, using the symbol is necessary. I would like to understand as well when and why the symbol is necessary.