nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

strange problem, ORA-00936 missing expression

Baber_Amin

Hi,

I am making a livereport and facing a very strange issue. Upon running giving error

ORA-00936: missing expression

OR ( is null))

but in my SQL i have as below

OR (%4 is null))

why its removing variable for %4 ?

Param %4: is selected as User Input 4

Find more posts tagged with

Comments

Donna Nalls

Hi Baber,

Are you sure you defined a 4th User Input parameter (at the top). I can only replicate this error message with an undefined input parameter. Are you sure you meant to select "User Input" as your parameter option?

Regards,

Donna

Baber_Amin

Hi Donna,

Thanks for your reply. I am attaching screen shot from the report. I am suspecting my query is having some CASE WHEN etc so that may be creating problem. I ran the same query in Oracle SQL developer and its working fine. It looks to me a kind of bug in Livereports.

regards,

Baber.

live-report-param4.JPG

Lindsay_Davies

Hi Baber,

Why not post your SQL so we can try to spot the problem.

Regards

Lindsay

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2012 May 14, Mon 06:59
To: eLink Recipient
Subject: strange problem, ORA-00936 missing expression

strange problem, ORA-00936 missing expression

Posted bybaber.amin@dolphinenergy.com (Amin, Baber) On 05-14-2012 01:54

Hi Donna,

regards,

Baber.

Attachment Links:

live-report-param4.JPG (11 KB)

[To post a comment, use the normal reply function]
Topic:	strange problem, ORA-00936 missing expression
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Donna Nalls

Hi Baber,

You defined four "user input" parameters, but did you define the corresponding input fields?

(see attached)

Donna

4parameters.jpg

4inputs.jpg

Baber_Amin

Hi Donna,

Yes, I defined that as well. Please see that attached screen shot.

regards,

Baber.

live-rep-params.JPG

CBancroft

Make sure the format of the report is Auto-LiveReport as that can have an impact

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, May 15, 2012 1:07 AM
To: eLink Recipient
Subject: strange problem, ORA-00936 missing expression

strange problem, ORA-00936 missing expression

Posted bybaber.amin@dolphinenergy.com (Amin, Baber) On 05-15-2012 01:02

Hi Donna,

Yes, I defined that as well. Please see that attached screen shot.

regards,

Baber.

Attachment Links:

live-rep-params.JPG (46 KB)

[To post a comment, use the normal reply function]
Topic:	strange problem, ORA-00936 missing expression
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Lindsay_Davies

Hi Baber

I can see a bit of your SQL statement – I did ask you to send the SQL statement itself through so I could look at it.

I believe the problem you are having relates to the re-use of input parameters.

You are using %4 in three places.

I do not think LiveReports supports referencing a parameter in more than one place.

You can check this by examining the connect log to see exactly what statement was passed to Oracle.

I think you will find that only the first use of %4 will have been substituted with a value.

This is a restriction in the product.

You will have to code your way around it. By changing your statement.

Regards

Lindsay

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2012 May 15, Tue 13:10
To: eLink Recipient
Subject: RE strange problem, ORA-00936 missing expression

RE strange problem, ORA-00936 missing expression

Posted bycbancrof@opentext.com (Bancroft, Chris) On 05-15-2012 08:09

Make sure the format of the report is Auto-LiveReport as that can have an impact

strange problem, ORA-00936 missing expression

Posted by baber.amin@dolphinenergy.com (Amin, Baber) On 05-15-2012 01:02

Hi Donna,

Yes, I defined that as well. Please see that attached screen shot.

regards,

Baber.

Attachment Links:

live-rep-params.JPG (46 KB)

[To post a comment, use the normal reply function]
Topic:	strange problem, ORA-00936 missing expression
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

[To post a comment, use the normal reply function]
Topic:	strange problem, ORA-00936 missing expression
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Baber_Amin

Hi,

report format is Auto Livereport. SQL is as below:

select dd.formnumber "Form Number",to_char( dd.issuedate , 'DD-MON-YYYY') "Issue Date",dd.subject "Subject",to_char(dd.duedate , 'DD-MON-YYYY') "Due Date", case when substr(wswt.SUBWORKTASK_TITLE,1,instr(wswt.SUBWORKTASK_TITLE,'-',1,1)-1) is null then wswt.SUBWORKTASK_TITLE else substr(wswt.SUBWORKTASK_TITLE,1,instr(wswt.SUBWORKTASK_TITLE,'-',1,1)-1) end "Issue Purpose",dd.status "Status",dd.projcode "Project Code", 'https://llserver/livelink/livelink.exe?func=work.EditTask&workid=' || wswt.SUBWORKTASK_WORKID || '&SubWorkID=' || wswt.SUBWORKTASK_SUBWORKID || '&TaskID=' || wswt.SUBWORKTASK_TASKID || '&paneindex=0&nexturl=https://llserver/livelink/livelink.exe?func=Personal.Assignments' "Task link" from WSubWorkTask wswt, dd_dist_data dd where wswt.SUBWORKTASK_SUBWORKID=dd.workid and SubWorkTask_PerformerID = %3 and (dd.duedate < %5 ) and SUBWORKTASK_STATUS not in (1,-1,-2,-3,-4) and ((upper(dd.projcode)=upper(%1) and (%1 is not null)) OR (%1 is null)) and (case when substr(wswt.SUBWORKTASK_TITLE,1,instr(wswt.SUBWORKTASK_TITLE,'-',1,1)-1) is null then wswt.SUBWORKTASK_TITLE else substr(wswt.SUBWORKTASK_TITLE,1,instr(wswt.SUBWORKTASK_TITLE,'-',1,1)-1) end)=%2 and (((upper(dd.formnumber) like '%%' || upper(trim(%4)) || '%%') and (%4 is not null)) OR (%4 is null)) order by 1,2

Bhupinder_Singh

The problem occurs after the 8^th usage of an input parameter. Your 9^th reference fails. This is a known bug. Please contactsupport@opentext.com and ask for an update on the following bug:

Bug # LPAD-10411 -It appears that the Livereport can not have more than 8 input parameters in the SQL statement.

- Bhupinder

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, May 15, 2012 3:41 PM
To: eLink Recipient
Subject: RE RE strange problem, ORA-00936 missing expression

RE RE strange problem, ORA-00936 missing expression

Posted bybaber.amin@dolphinenergy.com (Amin, Baber) On 05-15-2012 15:34

Hi,

report format is Auto Livereport. SQL is as below:

select dd.formnumber "Form Number",to_char( dd.issuedate , 'DD-MON-YYYY') "Issue Date",dd.subject "Subject",to_char(dd.duedate , 'DD-MON-YYYY') "Due Date", case when substr(wswt.SUBWORKTASK_TITLE,1,instr(wswt.SUBWORKTASK_TITLE,'-',1,1)-1) is null then wswt.SUBWORKTASK_TITLE else substr(wswt.SUBWORKTASK_TITLE,1,instr(wswt.SUBWORKTASK_TITLE,'-',1,1)-1) end "Issue Purpose",dd.status "Status",dd.projcode "Project Code", 'https://llserver/livelink/livelink.exe?func=work.EditTask&workid=' || wswt.SUBWORKTASK_WORKID || '&SubWorkID=' || wswt.SUBWORKTASK_SUBWORKID || '&TaskID=' || wswt.SUBWORKTASK_TASKID || '&paneindex=0&nexturl=https://llserver/livelink/livelink.exe?func=Personal.Assignments' "Task link" from WSubWorkTask wswt, dd_dist_data dd where wswt.SUBWORKTASK_SUBWORKID=dd.w! orkid and SubWorkTask_PerformerID = %3 and (dd.duedate < %5 ) and SUBWORKTASK_STATUS not in (1,-1,-2,-3,-4) and ((upper(dd.projcode)=upper(%1) and (%1 is not null)) OR (%1 is null)) and (case when substr(wswt.SUBWORKTASK_TITLE,1,instr(wswt.SUBWORKTASK_TITLE,'-',1,1)-1) is null then wswt.SUBWORKTASK_TITLE else substr(wswt.SUBWORKTASK_TITLE,1,instr(wswt.SUBWORKTASK_TITLE,'-',1,1)-1) end)=%2 and (((upper(dd.formnumber) like '%%' || upper(trim(%4)) || '%%') and (%4 is not null)) OR (%4 is null)) order by 1,2

[To post a comment, use the normal reply function]
Topic:	strange problem, ORA-00936 missing expression
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Lindsay_Davies

Hi Baber,

Teasing apart your statement, I see the following…

and ( ( upper(dd.projcode) = upper(%1)

and (%1 is not null)

)

OR (%1 is null)

)

and

It looks like you want to allow the user who runs the report to choose to omit the first parameter.

The same applies to your code for %4.

The restriction that you can only use a parameter once means this logic will not work.

I suggest you drop that logic and instead offer a number of separate reports that together provide all of the alternatives.

Regards

Lindsay

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2012 May 15, Tue 20:41
To: eLink Recipient
Subject: RE RE strange problem, ORA-00936 missing expression

RE RE strange problem, ORA-00936 missing expression

Posted bybaber.amin@dolphinenergy.com (Amin, Baber) On 05-15-2012 15:34

Hi,

report format is Auto Livereport. SQL is as below:

select dd.formnumber "Form Number",to_char( dd.issuedate , 'DD-MON-YYYY') "Issue Date",dd.subject "Subject",to_char(dd.duedate , 'DD-MON-YYYY') "Due Date", case when substr(wswt.SUBWORKTASK_TITLE,1,instr(wswt.SUBWORKTASK_TITLE,'-',1,1)-1) is null then wswt.SUBWORKTASK_TITLE else substr(wswt.SUBWORKTASK_TITLE,1,instr(wswt.SUBWORKTASK_TITLE,'-',1,1)-1) end "Issue Purpose",dd.status "Status",dd.projcode "Project Code", 'https://llserver/livelink/livelink.exe?func=work.EditTask&workid=' || wswt.SUBWORKTASK_WORKID || '&SubWorkID=' || wswt.SUBWORKTASK_SUBWORKID || '&TaskID=' || wswt.SUBWORKTASK_TASKID || '&paneindex=0&nexturl=https://llserver/livelink/livelink.exe?func=Personal.Assignments' "Task link" from WSubWorkTask wswt, dd_dist_data dd where wswt.SUBWORKTASK_SUBWORKID=dd.w! orkid and SubWorkTask_PerformerID = %3 and (dd.duedate < %5 ) and SUBWORKTASK_STATUS not in (1,-1,-2,-3,-4) and ((upper(dd.projcode)=upper(%1) and (%1 is not null)) OR (%1 is null)) and (case when substr(wswt.SUBWORKTASK_TITLE,1,instr(wswt.SUBWORKTASK_TITLE,'-',1,1)-1) is null then wswt.SUBWORKTASK_TITLE else substr(wswt.SUBWORKTASK_TITLE,1,instr(wswt.SUBWORKTASK_TITLE,'-',1,1)-1) end)=%2 and (((upper(dd.formnumber) like '%%' || upper(trim(%4)) || '%%') and (%4 is not null)) OR (%4 is null)) order by 1,2

[To post a comment, use the normal reply function]
Topic:	strange problem, ORA-00936 missing expression
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Baber_Amin

Hi Lindsay,

I really appreciate your effort in pointing out the problem but I feel its not good. System should be flexible enough to handle this. Queries can be even more complex than this.

regards,

Baber.