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; } } }

Order By not working in script

krispee

Hi All,

Order by in script does not seem functioning. I have the below query in Data Set

select
REPORTER_STATUS.SERVERSERIAL AS SERVERSERIAL,
REPORTER_STATUS.NODE AS NODE,
REPORTER_STATUS.ALERTGROUP AS ALERTGROUP,
REPORTER_STATUS.ALERTKEY AS ALERTKEY,
to_char (REPORTER_STATUS.FIRSTOCCURRENCE, 'MM-DD-YYYY HH24:MI:SS') AS FIRSTOCCURRENCE,
REPORTER_STATUS.SUMMARY AS SUMMARY ,
to_char (REPORTER_STATUS.LASTOCCURRENCE, 'MM-DD-YYYY HH24:MI:SS') AS LASTOCCURRENCE,
DECODE(REPORTER_STATUS.SEVERITY, 0 , 'Clear', 1, 'Indeterminate', 2 , 'Warning', 3, 'Minor', 4, 'Major', 5, 'Critical') AS SEVERITY,
DECODE(REPORTER_STATUS.ORIGINALSEVERITY, 0 , 'Clear', 1, 'Indeterminate', 2 , 'Warning', 3, 'Minor', 4, 'Major', 5, 'Critical') AS ORIGINALSEVERITY,
REPORTER_STATUS.TALLY AS TALLY,
CFOROWNER.CONVERSION AS OWNERUID
from
REPORTER_STATUS, REPORTER_CONVERSIONS CFOROWNER
where
REPORTER_STATUS.OWNERUID = CFOROWNER.VALUE AND CFOROWNER.CONVERSION_KEY = 'OwnerUID'||REPORTER_STATUS.OwnerUID AND
REPORTER_STATUS.AGENT = 'AirTalk PMAP'

And below script configured in beforeOpen method:

queryString = this.queryText;

queryString = queryString + " AND REPORTER_STATUS.ALERTGROUP LIKE '%" + params["Task"].value + "%'";
queryString = queryString + " AND TRUNC(REPORTER_STATUS.LASTOCCURRENCE) BETWEEN TO_DATE('" + params["StartDate"].value + "', 'MM/DD/YYYY') AND TO_DATE('" + params["EndDate"].value + "', 'MM/DD/YYYY')";
queryString = queryString + " ORDER BY REPORTER_STATUS." + reportContext.getParameterValue("sortColumn") + " " + reportContext.getParameterValue("sortOrder");

this.queryText = queryString;

Packages.java.lang.System.out.println("sql:" + this.queryText);

And we have Report parameters configured: Task, StartDate, EndDate, sortColumn and sortOrder.

Any help is really appreciated.

Warm regards,
Kris

Find more posts tagged with

Comments

mwilliams

When you output your queryText, what is the result?

krispee

Thanks Williams.

The query text does not include any report parameters and so the results are good but when we preview/run the report with data in beforeOpen method; the sorting does not happen at all.

Also we noticed that, say we have 2 entries; these two entries contain the same value for Node field say Bangalore then when we perform sorting on ServerSerial field it worked but when the Node value is different, the sorting does not function.

Any thoughts?

warm regards,
Kris

mwilliams

I'm wanting to know what the queryText looks like after you have edited in your script with your parameters. I see you're printing the queryText out at the end. I just wanted to know what you're seeing here when you try to do the "order by". Also, what is your report layout like? Are you just putting a straight table in there, or did you use grouping? Can you show me the queryText output when the order by is not working. Also, attach your design or let me know the layout. Thanks.

krispee

Thanks Williams.

Attached the Query Text and Script details. While i include order by in the Query Text, we end up with Oracle error (ORA-00933). Yes, we just put a blank report.

Warm regards,
Kris

mwilliams

You already showed all that above. What I'm interested in is the result of this line:

Packages.java.lang.System.out.println("sql:" + this.queryText);

As far as viewing the results, you're just viewing them in the "preview results" section of the dataSet editor then, if you have a blank report?

krispee

The output of Packages.java.lang.System.out.println("sql:" + this.queryText); is

sql:select
REPORTER_STATUS.SERVERSERIAL AS SERVERSERIAL,
REPORTER_STATUS.NODE AS NODE,
REPORTER_STATUS.ALERTGROUP AS ALERTGROUP,
REPORTER_STATUS.ALERTKEY AS ALERTKEY,
to_char (REPORTER_STATUS.FIRSTOCCURRENCE, 'MM-DD-YYYY HH24:MI:SS') AS FIRSTOCCURRENCE,
REPORTER_STATUS.SUMMARY AS SUMMARY ,
to_char (REPORTER_STATUS.LASTOCCURRENCE, 'MM-DD-YYYY HH24:MI:SS') AS LASTOCCURRENCE,
DECODE(REPORTER_STATUS.SEVERITY, 0 , 'Clear', 1, 'Indeterminate', 2 , 'Warning', 3, 'Minor', 4, 'Major', 5, 'Critical') AS SEVERITY,
DECODE(REPORTER_STATUS.ORIGINALSEVERITY, 0 , 'Clear', 1, 'Indeterminate', 2 , 'Warning', 3, 'Minor', 4, 'Major', 5, 'Critical') AS ORIGINALSEVERITY,
REPORTER_STATUS.TALLY AS TALLY,
CFOROWNER.CONVERSION AS OWNERUID
from
REPORTER_STATUS, REPORTER_CONVERSIONS CFOROWNER
where
REPORTER_STATUS.OWNERUID = CFOROWNER.VALUE AND CFOROWNER.CONVERSION_KEY = 'OwnerUID'||REPORTER_STATUS.OwnerUID AND
REPORTER_STATUS.AGENT = 'AirTalk PMAP' AND REPORTER_STATUS.ALERTGROUP LIKE '%1730235%' AND TRUNC(REPORTER_STATUS.LASTOCCURRENCE) BETWEEN TO_DATE('01/01/2010', 'MM/DD/YYYY') AND TO_DATE('06/23/2011', 'MM/DD/YYYY') ORDER BY REPORTER_STATUS.SERVERSERIAL DESC

Yes, the error encountered while "preview results".

warm regards,
Kris

krispee

The issue is resolved.

When you mentioned the term Grouping, we quickly verified and found that Grouping is performed on Node field and that's the reason we see that ORDER BY fails when Node values are different but works when Node values are same.

Thanks a lot.

warm regards,
Kris

mwilliams

Thanks. Does everything look correct according with your tables and fields in your query that is posted above? It looks like it should work to me. Can you reproduce this issue with the sample database so you can send me the report design and I can run it?