Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Query Builder question

  1. #1
    Join Date
    May 2012
    Posts
    10

    Exclamation Query Builder question

    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

  2. #2
    Join Date
    Feb 2010
    Posts
    114

    Default

    try this one

    Code:
    select table.*
    from (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) as table
    ORDER BY table.SI_ENDTIME DESC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.