CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Arun Palanisamy
Arun Palanisamy

Reputation: 5459

TRUNC(date) with subquery in oracle

I have a IF condition which checks the dates in the subquery as shown below

IF (TRUNC(lv_d_itr) IN
(SELECT next_day(TRUNC(to_date('01-01-17','dd-mm-yy'),'YYYY') + 14*(level-1), 'WED' )+7
 FROM dual
 CONNECT BY level <= 26)) THEN

Why am i getting the below error? usage is wrong?

PLS-00405: subquery not allowed in this context

What could be the alternate solution?

Upvotes: 2

Views: 799

Answers (2)

MT0
MT0

Reputation: 167867

You cannot use a sub-query in a pl/sql IF statement:

BEGIN
  IF 'X' IN ( SELECT DUMMY FROM DUAL ) THEN
    DBMS_OUTPUT.PUT_LINE( 'X' );
  END IF;
END;
/

Will error with PLS-00405: subquery not allowed in this context

You can refactor it to get the same effect:

DECLARE
  p_exists NUMBER;
BEGIN
  SELECT CASE WHEN 'X' IN ( SELECT DUMMY FROM DUAL )
              THEN 1
              ELSE 0
         END
  INTO   p_exists
  FROM   DUAL;

  IF p_exists = 1 THEN
    DBMS_OUTPUT.PUT_LINE( 'X' );
  END IF;
END;
/

Upvotes: 3

Aleksej
Aleksej

Reputation: 22949

You can edit this in different ways, a simple one could be something like:

declare
  n number;
begin
    select count(1)
    into n
     from
        (       
         SELECT next_day(TRUNC(to_date('01-01-17','dd-mm-yy'),'YYYY') + 14*(level-1), 'MER' )+7 as x
         FROM dual
         CONNECT BY level <= 26
        )
     where x = TRUNC(lv_d_itr);
    if n > 0
      then ...
    end if;
    ...
end;

Upvotes: 1

Related Questions