Error when using SQLList for LiveReport

I'm trying to use the SQLList parameter type for the first time and am running into some issues. The actual part of the WHERE clause in my SQL looks like this:

WHERE ISNULL(nl.[Status], 'None') IN %1

yet, when I attempt to run the WebReport that uses this live report I receive an error. I have figured out the syntax that i need the parameters to be in executing the LiveReport by itself, but I can't get it to work the same way running the WebReport. For instance, a single value such as status "Pending" needs to have the quotes around it when passed into the LiveReport to return results. However, when I try the same exact value as the default parameter in my WebReport, I receive a Content Server error. Any thoughts as to what I might be doing wrong or the correct way to go about using this SQLList? Thanks.

Find more posts tagged with

Comments

Colin_Schmidt

Have you tried using “COALESCE()”? I’ve used it in the SELECT, but not, that I recall, in the WHERE.

Regards,

Colin J

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, January 12, 2018 10:49 AM
To: eLink Recipient
Subject: [EXT] Error when using SQLList for LiveReport

Error when using SQLList for LiveReport

Posted byagorczyca@crown-chicago.com (Gorczyca, AJ) On 01/12/2018 10:47 AM

I'm trying to use the SQLList parameter type for the first time and am running into some issues. The actual part of the WHERE clause in my SQL looks like this:

WHERE ISNULL(nl.[Status], 'None') IN %1

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	My Support

The information contained in this message may be CONFIDENTIAL and is for the intended addressee only. Any unauthorized use, dissemination of the information, or copying of this message is prohibited. If you are not the intended addressee, please notify the sender immediately and delete this message.

AJ Gorczyca

From what I understand about COALESCE it's not what I want. It returns only the 1st non-null item, I want all the items passed in to be looked at. I know what my values being passed in are, that's not the issue, the issue is getting them to be formatted correctly to be read by the SQLList parameter in the report. It seems to behave different ways when i pass in the parameters through the LiveReport directly vs when I use a WebReport.

I want (potentially) multiple items to be included in my IN statement in the WHERE clause.

Colin_Schmidt

With COALESCE, you can provide a long list of optional fields, unlike “NVL” (in Oracle, which doesn’t work in MS SQL).

I had something like that happen to me using Oracle in a WR-driven LR, but I can’t recall what I had to do. Despite that I test my SQLs in SQL Developer or some other tool so that I can get a genuine usable syntax error, there are those differences that confound us.

I’m not very fluent in SQL Server, but I know it seems very particular in datatypes….CAST or CONVERT or something. Oracle seems more “forgiving”.

Regards,

Colin J

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, January 12, 2018 1:37 PM
To: eLink Recipient
Subject: [EXT] [EXTERNAL] - RE Error when using SQLList for LiveReport

[EXTERNAL] - RE Error when using SQLList for LiveReport

Posted byagorczyca@crown-chicago.com (Gorczyca, AJ) On 01/12/2018 01:34 PM

I want (potentially) multiple items to be included in my IN statement in the WHERE clause.

[To post a comment, use the normal reply function]
Topic:	Error when using SQLList for LiveReport
Forum:	Content Server LiveReports Forum
Content Server:	My Support