Hello,

I have a very standard SQL pass-thru report which has an ID column with a drilldown link to a detail report. Along with the ID column, I also have to pass the date from another column on the main report. I am facing issues with passing the date to the detail report.

Both reports have a simple SQL:
Main report SQL:
SELECT * FROM TABLE
WHERE DATE = TRUNC(SYSDATE)-5

Sample main report:
ID_NUM DATE NAME ADDR PHONE EMAIL .....
(Drilldown link)

MY drilldown statement:
TYPE=DATA, COLUMN=ID_NUM, FOCEXEC=DETAIL_REPORT(ID_NUM = ID_NUM DATE = DATE), $

Detail Report SQL:
SELECT * TABLE_B
WHERE ID_NUM = &ID_NUM
AND DATE = &DATE


I get the error:

(FOC1400) SQLCODE IS 907 (HEX: 0000038B)
 : ORA-00907: missing right parenthesis
 : Error context area: 017/09/20 00:00:00)
 L    (FOC1405) SQL PREPARE ERROR.
 0 ERROR AT OR NEAR LINE     12  IN PROCEDURE threshold_report_detail
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
 0 ERROR AT OR NEAR LINE     15  IN PROCEDURE threshold_report_detail
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
 BYPASSING TO END OF COMMAND

I tried so many things with the date field, like passing DATE.EVAL in the FOCEXEC statement, also tried TO_DATE ('&DATE', 'YYYYMMDD') in the SQL of the detail report, but nothing works.. get a new error everytime...

How can I pass a date value successfully for the detail report to filter on?

Thanks in advance!

This message has been edited. Last edited by: Nova27,

SELECT * TABLE_B
WHERE ID_NUM = &ID_NUM
AND DATE = &DATE

Are you missing the FROM in the SQL statement?

Hi Francis,

I do have a FROM statement in my SQL, I think I just missed typing it here..

Any idea how do I pass the date so as to avoid SQL errors?

Thanks!

As this is SQL passthru I would suggest doing -

• viewing the source HTML to see what anchor link is being produced
  
• an &ECHO = ON to see what is being produced by WebFOCUS
  
• capturing the SQL trace to see what is finally being passed to your RDBMS

then compare to see if you can spot where the problem is.

T

Try putting single quotes around the date variable.

 
SELECT * 
FROM TABLE_B
WHERE ID_NUM = &ID_NUM
AND DATE = '&DATE' 

Also double check the value of your variable. It looks like in the error that the date is a bit funky. like the year is missing the 2 for 2017

quote:

: Error context area: 017/09/20 00:00:00)

If your are using SQL Server, in the first SQL try the following to get the current date using T-SQL expression.

SELECT * FROM TABLE
WHERE DATE = CAST(GETDATE() AS DATE)
 

This message has been edited. Last edited by: Hallway,

If your are using SQL Server, in the first SQL try the following to get the current date using T-SQL expression.

SELECT * FROM TABLE
WHERE DATE = CAST(GETDATE() AS DATE)
 

If you're using SQL Server, in the first SQL try the following to get the current date using T-SQL expression.

 SELECT * FROM TABLE WHERE DATE = CAST(GETDATE() AS DATE)  

Oops, nevermind. I see that you are querying an Oracle DB

Thank you all for your help!

I used the &ECHO=ON to figure out the issue - since my date field was a date-time column, I Just used HDATE function to convert it to a date only column and then passed that to the SQL in the detail report.

I was using &ECHO=ALL and I think I wasn't really getting what I wanted to see.

Thanks again everyone for helping me out