LiveReport query

[pankajdhoot20]

Hello everyone,

I am trying to create a Livereport in Livelink. It looks something like below:

select count(*) "Count" from KUAF where Type=0 and Deleted=0 and MailAddress like "%_abc.com%" and MailAddress not like "%_xyz.com%"

The query runs fine in PL/SQL & yeilds an appropriate result, however when I try to run the same via a Livereport, it throws me an error something like below:

Livelink Error: Error processing request
ORA-00972: identifier is too long -

tried to google the Oracle error & I understand that the identifier should not be more than 30 characters. Now, abc in the above query are 6 characters while xyz in actual are 8 characters. Kindly help me understand where am I going wrong.

Would highly appreciate your help. Let me know in case I need to provide any more details.
We are on LL 9.5.1

Thank you.


Pankaj

 

[appnair]

In my livelink my LR is set to auto LR.If I do it with Double quotes as you do I get errors in TOAD as well as livelink
howver this worked in TOAD & Livelink

select count(*) "Count" from KUAF where Type=0 and Deleted=0 and MailAddress like '%_abc.com%' and MailAddress not like '%_xyz.com%'

I hope you are not passing the varaible as a user input then the % has to be escaped again but if the query was
hard coded as you say single quotes may be what saves you

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008,Livelink ECM Champion 2010

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

http://www.livelink.in

 

[pankajdhoot20]

Thanks appnair.... Appreciate your help !!

I should hv mentioned this part in the OP.
Anyway, in Pl/SQL it won't accept double quotes. And in LR it won't accept in single quotes. It would say the query was terminated improperly. So I guess LR takes on double quotes. Nit sure how the.double quotes worked fr you.

Also I hv set LL to Auto LR as well & have not passed any user inputs as such

Thank you.

Pankaj

 

[appnair]

Are you saying that in your livelink this LR with single quotes do not work as intended

select count(*) "Count" from KUAF where Type=0 and Deleted=0 and MailAddress like '%_abc.com%' and MailAddress not like '%_xyz.com%'

I am not using PL/SQL I am using TOAD a oracle IDE.I think you mean to say SQLplus because there is a no procedure that you are doing as in PL/SQL.


The above query works in our livelink with no problems whatsover.I do not have a 9.5 system to test for you sorry.

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008,Livelink ECM Champion 2010

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

http://www.livelink.in

 

[pankajdhoot20]

oh yes.... apologies, I meant SQLplus. & yes I meant that the LR with single qoutes does not work

Pity, that I have tried everything till now & none of them are working. Does this seem to you to be something Livelink specific ?

 

[appnair]

no idea LL9.5 is basically a very old system
just try your query with removing the wild card and see where it lands you

The only thing livelink does is basically it creates a connection to the table in qn
escape it for correctness,and then pass that as a string so basically it is executing as
oracle would have it so something is not liked by oracle.It also checks to see if you are
trying to fool it SQLinjection that sort.

If you know how to use livelink builder you could trace the call in the debugger

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008,Livelink ECM Champion 2010

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

http://www.livelink.in

 

[Emptynext]

you need to use the %% twice as this is an operator for the LR to look for a parameter

select count(*) from KUAF where Type=0 and Deleted=0 and MailAddress like '%%_abc.com%%' and MailAddress not like '%%_xyz.com%%'

 

[venkatalakkoju]

I agree with DanserB. I remember using %% in LRs when I was working on LL9.2.

 

[pankajdhoot20]

Sorry for posting so after long time. Worked like charm.... thank you everyone for your help.

Really appreciate it.

Thanks
Pankaj