| Discoverer reports [message #132426] |
Fri, 12 August 2005 12:31  |
reach_honey
Messages: 10
Registered: June 2005
Location: Boston
|
Junior Member |
|
|
All,
I have written a query which I wanted to make it as discover report. I'm totally new to discoverer. I'm getting error in parameter as wrong parameter value.
Query is
--------------------------
SELECT EAL.CODE APP_TYPE1,
EASL.DESCRIPTION STATUS1,
COUNT(EAH.APPHD_ID) COUNT1
FROM
EL_APPLICATION_HDRS EAH,
WQ_TASKS WQT,
EL_APP_LKUPS EAL,
EL_FAMILY_NUMBERS EFN,
EL_APP_STAT_LKUPS EASL
WHERE
EAH.APPHD_ID = WQT.TABLE_PRIMARY_KEY (+)
AND EAH.FMNB_ID = EFN.FMNB_ID
AND EAH.APLK_ID = EAL.APLK_ID
AND EAH.CURRENT_APP_STATUS_ID = EASL.APSLK_ID
AND EAH.RECEIPT_DATE <= (TRUNC(SYSDATE) - :P_OLD_REPORT)
AND (EAH.RECEIPT_DATE >= :P_FROM_RECEIPT_DATE + 0.99999)
AND (EAH.RECEIPT_DATE <= NVL(:P_TO_RECEIPT_DATE, TRUNC(sysdate)))
AND EAL.CODE IN (:P_APPLICATION_TYPE)
AND EXISTS (SELECT 'X'
FROM EL_TABLES_LKUP H,
EL_MEDIA_LINKS I
WHERE H.TBLK_ID = I.TBLK_ID
AND H.TABLE_NAME = 'EL_APPLICATION_HDRS'
AND I.PK_VALUE = TO_CHAR(EAH.APPHD_ID))
---------------------
I'm able to make all condition except the parameter P_OLD report part and Adding 0.9999 part.
It does not take any value and booms out.
I read that in parameter if you using reserverd word the datatype should be same. But in my case i wanted a user to see report as per his requirement.
For ex if he wants to see data for last 2 days so when he enters 2 in P-old-reprot parameter it would be sysdate -2.
And depending on that reports would run.
Also how do i make the exists and not exists thing work?
Please help me out.
Thanks
reach
Also How do I create a Exists in
|
|
|
|
| Re: Discoverer reports [message #132651 is a reply to message #132426] |
Mon, 15 August 2005 09:06  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi Reach,
First of all, the (not) exists you can do by subqueries: create a sheet in you workbook containing the subquery, than create another sheet and create a condition with a subquery like "(not) in <the sheet you just created>".
I know there are discussions about the performance of (not) exists versus not in, but you can let the database deal with that.
And for the parameters you use: you can create each parameter seperately, not linked to a condition or item or anything. Then after that, you can use these in conditions. Having a number in a varchar2 parameter, added to sysdate is no problem, that's a implicit conversion the database does for you. If you like, you can put a to_number around it.
However, some of the conditions seem a bit odd to me, but I don't know your requirements.
For example "<= a date - 0.99999" seems to indicate that you mean <= trunc(a date).
Let me try to explain. Any date without a timestamp is treated by the database as "midnight, beginning of that day". So, what you're telling the database here is: pick a date, add aaaaaaaaaalmost one day to it and I want something else to be less or equal to that. But if you use <=trunc(date), than you just get everything including that date, regardless of the timestamp.
Furthermore, you write 'date <= from_date and date >= to_date', sounds like a between to me...
Anyway, good luck and have fun with Discoverer!
Regards,
Sabine
|
|
|
|
| Re: Discoverer reports [message #132677 is a reply to message #132651] |
Mon, 15 August 2005 11:19 |
reach_honey
Messages: 10
Registered: June 2005
Location: Boston
|
Junior Member |
|
|
Sabine,
thanks for your reply.
I'm using Trunc(Sysdate). But it would not not allow and you would get error of incompatible data type.
Discovered help(documents) says that with reserverd word being used in parameter,there respectived type should be same
I'm having all 3 different parameters and creating new calculating fields and inside them i'm using subtracting trunc(sysdate-p_oldreprot_date). It still does not work.
I'm using in and not in logic in subqueries and it comes out ok.It does take some time but still its ok.
Thanks
|
|
|
|
| Re: Discoverer reports [message #132798 is a reply to message #132677] |
Tue, 16 August 2005 04:56 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi,
That's strange, I've tested it using a date column and it works fine. But okay, maybe a version difference or NLS setting in the database, or whatever.
Could you please try to create a calculation, containing sysdate-1, does that work? Ok, now we've verified that you can substract a number from a date value, which is treated as days then.
Now please create a calculation <your date column>-1, does that work?
And now a calculation with sysdate - <your parameter>.
And a calculation with <your date column> - <your parameter>
If that doesn't work, than alter it to: <your date column> - to_number(<your parameter>)
We're gonna get this thing working.....
Regards,
Sabine
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
