[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 767
  • Last Modified:

ORA-06550 error

When I ran this script I got ORA-06550 Error __ Please help

DECLARE
  n_param3           NUMBER(20,2)       :=0;
  n_count_subords    NUMBER(10)         :=0;
BEGIN

FOR skudc_planorder_cursor IN
  (
    SELECT /*+ RULE */ DISTINCT s.loc, s.item
      FROM stsc.sku s, stsc.item i
     WHERE s.scen = 0 AND i.scen = 0
       AND s.item = i.item
       AND s.loc IN ( SELECT NVL(TRIM(si_alt_loc),' ' )
                        FROM stsc.loc
                       WHERE scen = 0 AND NVL(TRIM(si_loc_type), ' ' ) =
'DC'
                    )
       AND i.inv_class IN ( SELECT inv_class FROM siviews.si_fg_cls)
       AND i.product_group IN ( SELECT 1 FROM
springs.si_jde_user_proc_params
                                 WHERE UPPER(TRIM(key1)) = 'ADJPLANLT'
                                   AND UPPER(TRIM(key2)) = 'OVERRIDE'
                                   AND UPPER(TRIM(key3)) IS NULL
                                   AND UPPER(TRIM(param1)) =
i.product_group
                              )
       AND EXISTS ( SELECT 1 FROM stsc.bom b
                     WHERE scen = 0 AND s.item = b.item
                       AND EXISTS ( SELECT 1 FROM stsc.loc
                                     WHERE scen = 0 AND b.loc =
NVL(TRIM(si_alt_loc),loc)
                                       AND TRIM(si_seq_ind) = 'Y'
                                  )
                  )
       AND NOT EXISTS ( SELECT 1 FROM stsc.bom b1, stsc.bom b2
                         WHERE  b1.item = b2.subord AND b2.scen = 0)
  )
LOOP
  FOR inner_loop IN ( SELECT DISTINCT subord
                        FROM stsc.bom
                       START WITH 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;
      IF n_count_subords = 0 THEN
         UPDATE stsc.sku
            SET planleadtime = ( n_param3 * 1440 )
          WHERE scen = 0 AND item = skudc_planorder_cursor.item
            AND loc = skudc_planorder_cursor.loc;
      END IF;
      n_count_subords := 0;
END LOOP;
END;
0
ramumorla
Asked:
ramumorla
  • 2
2 Solutions
 
jwahlCommented:
i think subqueries outside of SQL are not allowed.

as workaround

1)
define a cursor:

CURSOR item_c (p_subord IN VARCHAR2) IS
    SELECT item
    FROM   stsc.item
    WHERE  scen = 0
    AND    item = p_subord
    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
        );

2)
instead of:
      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;        
     
try:      

      FOR item_rt IN item_c (inner_loop.subord)
      LOOP
          n_count_subords := n_count_subords + 1;
      END LOOP;
0
 
ramumorlaAuthor Commented:
Subqueries arenot allowed? Can u please elaborate>?
0
 
rkrteraCommented:
You got some kind of compilation error in the script, thats the reason you are facing this problem.

You have two problems in the script.


The first problem (see listing below) is that "Start With" is missing a comparison. For instance: start with manager = 'Greg.'
FOR inner_loop IN ( SELECT DISTINCT subord
                        FROM stsc.bom
                       START WITH skudc_planorder_cursor.item
                     CONNECT BY PRIOR subord =  skudc_planorder_cursor.item

The second problem (see listing below) is that you are using the variable "inner_loop.subord" as a loop index variable. This is not allowed for an IF statement:
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;

0
 
ramumorlaAuthor Commented:
rkrtera,

Yeah you are right...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now