Home » SQL & PL/SQL » SQL & PL/SQL » SubQuery not running
SubQuery not running [message #192126] Mon, 11 September 2006 02:16 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I am running a query and there is a subquery in the IF statement below. In the IF statement below, I have put the subquery, but I am getting an error "PLS-00405: subquery not allowed in this context".

FOR inner_loop IN ( SELECT DISTINCT subord
                            FROM stsc.bom
                           START WITH item in         
                          (skudc_planorder_cursor.item)
        CONNECT BY PRIOR subord = skudc_planorder_cursor.item
                    )
    LOOP
      IF inner_loop.subord IN ( SELECT item FROM stsc.item
                                 WHERE scen = 0
                                   AND item.product_type IN
                              ( SELECT param2 FROM springs.si_jde_user_proc_params
                                 WHERE UPPER(TRIM(key1)) = 'ADJPLANLT'
                                   AND UPPER(TRIM(key2)) = 'OVERRIDE'
                                   AND TRIM(key3) IS NULL)
                              ) THEN
       n_count_subords := n_count_subords + 1;
      END IF;
    END LOOP;


Please advice.

Thanks,
Mona
Re: SubQuery not running [message #192133 is a reply to message #192126] Mon, 11 September 2006 02:52 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you want to use SQL, use a SELECT INTO, or a cursor. You cant just mix 'n' match SQL SELECT syntax in any PL/SQL statement.
FOR inner_loop IN ( SELECT DISTINCT subord
                            FROM stsc.bom
                           START WITH item in         
                          (skudc_planorder_cursor.item)
        CONNECT BY PRIOR subord = skudc_planorder_cursor.item
                    )
    LOOP
      BEGIN
        SELECT 1
        INTO dummy
        FROM stsc.item
        WHERE scen = 0
        AND item.product_type IN
          ( SELECT param2 FROM springs.si_jde_user_proc_params
            WHERE UPPER(TRIM(key1)) = 'ADJPLANLT'
            AND UPPER(TRIM(key2)) = 'OVERRIDE'
            AND TRIM(key3) IS NULL)
        AND item = inner_loop.subord;

        n_count_subords := n_count_subords + 1;
        
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
      END;
    END LOOP;


But even easier for your case, put the inner SQL statement as a WHERE clause on the outer cursor, and SELECT COUNT(*) into n_count_subords.

Ross Leishman
Re: SubQuery not running [message #192141 is a reply to message #192133] Mon, 11 September 2006 03:29 Go to previous message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks Ross,
This helped me solving my query.

Regards,
Mona
Previous Topic: inserting into table row-by-row.
Next Topic: GROUP BY clause
Goto Forum:
  


Current Time: Sat Dec 03 01:11:26 CST 2016

Total time taken to generate the page: 0.14152 seconds