CODEWITHSUNDEEP
sqlplsql
Kevin
Kevin

Reputation: 207

For loop in a select statement?

Can you use a for loop in a select statement (specifically, a case) in order to define multiple values?

Here's my code:

select case when sysdate in ( declare k DATE:= '2015-01-01', 
 Begin 
  FOR i in 1...365 LOOP
      k:=sydate +1;
  END LOOP;
END;
) then '1' else 'n/a' end FISCAL_YEAR from dual

There may be multiple issues with my syntax..I am trying to say that when today's date is within the year, then a '1' shows up in the FISCAL_YEAR column. This would be a much cleaner way then using a case statement for each day, so any help would be appreciated. Thank you.

Upvotes: 0

Views: 283

Answers (4)

Martina
Martina

Reputation: 929

I think previous answer with time interval is the right solution to your problem.

However to answer your question whether there is a way how to loop inside select statement: No, but there is a way how to generate set of increasing numbers, so this code works like your intended pseudocode:

select case when a.c>0 then '1' else 'n/a' end FISCAL_YEAR  
 from (select count(*) c from (
    SELECT ROWNUM+to_date('2015-01-01','yyyy-mm-dd')-1 n
    FROM   ( SELECT 1 
             FROM   dual
             CONNECT BY LEVEL <= 365)) lo 
       where trunc(sysdate)=lo.n) a

Upvotes: 0

TommCatt
TommCatt

Reputation: 5636

There are two places you can put your check, depending on which rows you want in your result set. If you want all rows back to a specific date which may be beyond the current FY, use a check in a case in the selection list.

select  <whatever>,
        case when DateField between date '2015-01-01'
                            and Add_Months( date '2015-01-01', 12 ) - 1
                then 'Y' else 'N' end
            as InFY2015
from    DataSource;

If you want the result set to contain only rows with dates within the FY, put the check in the where clause.

select  <whatever>
from    DataSource
where   DateField between date '2015-01-01'
                  and Add_Months( date '2015-01-01', 12 ) - 1;

In the latter query, there is no need for an indicator that says "this row in FY2015" because they all are.

Upvotes: 0

Ditto
Ditto

Reputation: 3344

You probably just need something like:

  select case when to_char(sysdate,'YYYY') = '2015' then 1 else 0 end
    from dual

No idea what you're trying to do with that "nested" pl/sql block O.o

Just fill in "the year" in place of 2015 as you need. (ie a variable as you need).

If that's not what you need, please provide a small sample showing input and output expected.

Upvotes: 2

David Faber
David Faber

Reputation: 12486

There are easier ways of doing this:

SELECT CASE WHEN TRUNC(sysdate, 'YEAR') = DATE'2015-01-01' THEN '1' ELSE 'n/a' END AS fiscal_year
  FROM dual;

or you could use DECODE():

SELECT DECODE(TRUNC(sysdate, 'YEAR', DATE'2015-01-01', '1', 'n/a') AS fiscal_year
  FROM dual;

Instead of TRUNC(sysdate, 'YEAR') you could use TO_CHAR(sysdate, 'YYYY') etc.

Upvotes: 0

Related Questions