1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Output based sql query

Discussion in 'SQL PL/SQL' started by mukulverma2408, Sep 13, 2017.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    44
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hi Experts,

    I have this requirement where I have to execute a sql query based on output of another query, something like given in example below :
    Code (Text):

    a=select to_char(sysdate,'DAY') from dual
    if a='SUNDAY' then
       select syadate from dual;
    else
        select sysdate-1 from dual;
     
    Explanation - If output of my first query is SUNDAY then execute Query1 else execute query2

    Any help is appreciated.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    715
    Likes Received:
    143
    Trophy Points:
    830
    Location:
    Russian Federation
    In SQL , all is little bit easier )))

    Code (Text):

      select sysdate- DECODE(to_char(DATE '2017-09-17','DY'),'SUN',1,0) from dual;
     
    Helpfull:
    1) Format Models
    2) to_date to_char

    The days of cuurent week :
    Code (Text):

    SELECT
      TRUNC(SYSDATE,'IW') + LEVEL - 1 day_of_week,
      to_char(TRUNC(SYSDATE,'IW') + LEVEL - 1,'DAY') nameday_of_week
     
      FROM dual
      CONNECT BY LEVEL<= 7;
     
    The current day on week :
    Code (Text):

    SELECT    1+ trunc(sysdate) - TRUNC(SYSDATE,'IW')    FROM dual ;
     
     
    Last edited: Sep 14, 2017
    mukulverma2408 likes this.
  3. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    44
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hi There,

    Thanks for the post, but my requirement is slightly different (apologies for not providing enough clarity on the first post), the actual requirement is something like below :
    Code (Text):

    a=select to_char(sysdate,'DAY') from dual
    if a='SUNDAY' then
       Execute statement 1;
    else
        Execute statement 2;
     
    These statement could be any oracle select query. Any idea how to achieve this.
     
  4. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    44
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    I tried using below case statement, but it isn't working :
    Code (Text):

    select
    case
    when to_char(sysdate,'DAY') in ('SUNDAY','THURSDAY') then 'select count(*) from employees'
    else
    Select count(*) from departments
    end
    from dual;
     
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,517
    Likes Received:
    360
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    CASE statements don't execute SQL queries. If you do this:

    Code (SQL):
    SET pagesize 0

    SELECT
    CASE
    WHEN to_char(sysdate,'DAY') IN ('SUNDAY','THURSDAY') THEN 'select count(*) from employees'
    ELSE
    'Select count(*) from departments'
    END
    FROM dual

    spool myquery.SQL
    /
    spool off

    @myquery.SQL
     
    you might get what you want.
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    715
    Likes Received:
    143
    Trophy Points:
    830
    Location:
    Russian Federation
    for example:
    Code (Text):

    select
             extractvalue
               (dbms_xmlgen.getxmltype
                 ('select count(1)  cnt  FROM '||
                    CASE
                    WHEN to_char(sysdate,'DAY') IN ('SUNDAY','THURSDAY') THEN 'employees'
                    ELSE
                    'departments'
                    END          
                 )        
                 , '/ROWSET/ROW/CNT'
               ) cnt
        from dual;

     
     
    mukulverma2408 likes this.