Business objects Query Builder question

View previous topic View next topic Go down

Business objects Query Builder question

Post  LUCY.ONLINE on Thu Mar 14, 2013 3:15 pm

I have the following query that will list scheduled instances. The client wants to see this query differently.

They want to see all the null end time instances first followed by not null endtime instances in descending order. I tried doing union query something like below but query builder fails and does not recognize query. Is there a limitation on query builder or is it a full fledged query engine. Any Idea how can I achieve nulled endtime followed by not nulled end time in descending order. Following query does not work but when I run seaparately without union both queries work fine. I also tried doing sub query but I think sub query is not allowed either. Can you tell me if there is any query limitation in QB or any alternative you can think of? Thanks

SELECT
SI_ID, SI_NAME, SI_DESCRIPTION, SI_SCHEDULE_STATUS, SI_NEXTRUNTIME,
SI_STARTTIME, SI_ENDTIME, SI_SCHEDULEINFO.SI_OBJID, SI_SCHEDULEINFO.SI_SCHED_NOW
FROM
CI_INFOOBJECTS
WHERE
SI_KIND = 'Webi'
AND SI_INSTANCE = 1
AND SI_SCHEDULEINFO.SI_SUBMITTER = '11.2'
AND (SI_SCHEDULE_STATUS != 9 OR SI_NEXTRUNTIME < '03/13/2013 05:46:55')
AND SI_ENDTIME is null

UNION

SELECT
SI_ID, SI_NAME, SI_DESCRIPTION, SI_SCHEDULE_STATUS, SI_NEXTRUNTIME,
SI_STARTTIME, SI_ENDTIME, SI_SCHEDULEINFO.SI_OBJID, SI_SCHEDULEINFO.SI_SCHED_NOW
FROM
CI_INFOOBJECTS
WHERE
SI_KIND = 'Webi'
AND SI_INSTANCE = 1
AND SI_SCHEDULEINFO.SI_SUBMITTER = '11.2'
AND (SI_SCHEDULE_STATUS != 9 OR SI_NEXTRUNTIME < '03/13/2013 05:46:55')
AND SI_ENDTIME is not null
ORDER BY SI_ENDTIME DESC

LUCY.ONLINE

Posts : 4
Join date : 2012-06-01

View user profile

Back to top Go down

single query instead of union

Post  LearnDW on Mon Mar 18, 2013 6:38 am

Instead of using union you can achieve this in single sql query as below

SELECT
SI_ID, SI_NAME, SI_DESCRIPTION, SI_SCHEDULE_STATUS, SI_NEXTRUNTIME,
SI_STARTTIME, SI_ENDTIME, SI_SCHEDULEINFO.SI_OBJID, SI_SCHEDULEINFO.SI_SCHED_NOW
FROM
CI_INFOOBJECTS
WHERE
SI_KIND = 'Webi'
AND SI_INSTANCE = 1
AND SI_SCHEDULEINFO.SI_SUBMITTER = '11.2'
AND (SI_SCHEDULE_STATUS != 9 OR SI_NEXTRUNTIME < '03/13/2013 05:46:55')
ORDER BY SI_ENDTIME NULLS FIRST


Let me know if this is successful.

LearnDW

Posts : 1
Join date : 2013-03-18

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum