Databases
StackOverflow2013
Postsdbo
POZero iteration count in PL SQL Oracle
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POZero iteration count in PL SQL Oracle
    primarykey
    data
    text
    <p>I'm writing a query that should execute different select statements based on an IF statement. Haven't done any IFs in Oracle before and I'm not sure what's wrong, but I'm getting the error, zero iteration count and it points to the first line of code. I included the code and it's a lot, but you really only need to pay attention to the IFs.</p> <pre><code>IF :p_sales_type = 'all_cancel' THEN SELECT TO_DATE(SUBSTR(Transaction_Date,1,10), 'MM/DD/YYYY'),Item_Number, REGEXP_REPLACE(SUBSTR(Item_Or_Adj_Description,1,50),'([^[:print:]])',' ') AS Item_Or_Adj_Desctription, Customer_Type, Document_ID, Dealer_ID, Sales_Type, Item_Quantity, Total_Fee, State_Fee, Transaction_Fee, AO_Fee, WDFW_Fee FROM ( -- Sales Transactions SELECT /*+ index(IT ITEM_X4) */ TO_CHAR(IT.it_status_set_date - 2/24, 'MM/DD/YYYY HH24:MI:SS') AS Transaction_Date, -- Pacific Time TO_NUMBER(IT.ic_rcn) AS Item_Number, IT.it_descr AS Item_Or_Adj_Description, DT.di_name AS Customer_Type, IT.it_docid AS Document_ID, IT.ag_id AS Dealer_ID, CASE WHEN UPPER(IST.is_name) = 'ACTIVE' THEN 'SALE' ELSE IST.is_name END AS Sales_Type, NVL(IT.it_quantity * CASE WHEN IT.is_id = 'AC' THEN 1 WHEN IT.is_id = 'DU' THEN 1 ELSE -1 END, 0) AS Item_Quantity, -- Dups = 1 NVL(IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) + NVL(IT.it_other_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) AS Total_Fee, NVL(IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) AS State_Fee, NVL(IT.it_other_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) AS Transaction_Fee, CASE WHEN IT.it_other_fee IS NULL OR IT.it_other_fee = 0.00 THEN 0.00 ELSE ROUND(IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END * (:p_ao_trx_fee / 100), 2) END AS AO_Fee, CASE WHEN IT.it_other_fee IS NULL OR IT.it_other_fee = 0.00 THEN 0.00 ELSE (IT.it_other_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END) - ROUND((IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END * (:p_ao_trx_fee / 100)), 2) END AS WDFW_Fee FROM ITEM IT JOIN DISCOUNT_TYPE DT ON DT.di_id = IT.di_id JOIN ITEM_STATUS_TYPE IST ON IST.is_id = IT.is_id WHERE IT.it_status_ind = 'A' -- Include active ITEM rows only. AND (IT.is_id IN ('AC','DC','SC') OR (IT.is_id = 'DU' AND NVL(IT.it_state_fee, 0) != 0)) -- Exclude voids, exchanges, and false duplicates. AND IT.ic_rcn != '999' -- Exclude Dealer Fees. AND IT.it_status_set_date BETWEEN :P_beg_dt -- Pacific Time AND :P_end_dt AND (:p_discount_type IS NULL OR DT.di_id = :p_discount_type) -- AND IST.is_name = :SalesType UNION ALL -- Item Adjustments SELECT TO_CHAR(AJ.aj_adjustment_date - 2/24, 'MM/DD/YYYY HH24:MI:SS') AS Transaction_Date, -- Pacific Time TO_NUMBER(IT.ic_rcn) AS Item_Number, AJ.aj_comment AS Item_Or_Adj_Description, DT.di_name AS Customer_Type, IT.it_docid AS Document_ID, AJ.ag_id AS Dealer_ID, AJ.ajt_adjustment_type_name AS Sales_Type, 0 AS Item_Quantity, NVL(AJ.aj_state_adj_amt, 0.00) + NVL(AJ.aj_other_adj_amt, 0.00) AS Total_Fee, NVL(AJ.aj_state_adj_amt, 0.00) AS State_Fee, NVL(AJ.aj_other_adj_amt, 0.00) AS Transaction_Fee, CASE WHEN AJ.aj_other_adj_amt IS NULL OR AJ.aj_other_adj_amt = 0.00 THEN 0.00 ELSE ROUND(NVL(AJ.aj_state_adj_amt, 0.00) * (:p_ao_trx_fee / 100), 2) END AS AO_Fee, CASE WHEN AJ.aj_other_adj_amt IS NULL OR AJ.aj_other_adj_amt = 0.00 THEN 0.00 ELSE AJ.aj_other_adj_amt - ROUND(NVL(AJ.aj_state_adj_amt, 0.00) * (:p_ao_trx_fee / 100), 2) END AS WDFW_Fee FROM ADJUSTMENT AJ JOIN ITEM IT ON IT.it_id = AJ.it_id JOIN DISCOUNT_TYPE DT ON DT.di_id = IT.di_id WHERE AJ.aj_status_ind = 'A' -- Include active adjustments only. AND AJ.it_id IS NOT NULL -- Include Item Adjustments only; rows with a foreign key to the ITEM table are Item Adjustments. AND AJ.ajt_id != '0' -- Unreturned Doc Charge is not an Item Adjustment, it's a Lump Sum Adjustment to the Dealers account. AND IT.ic_rcn != '999' -- Exclude Dealer Fees. AND AJ.aj_adjustment_date BETWEEN :P_beg_dt -- Pacific Time AND :P_end_dt AND (:p_discount_type IS NULL OR DT.di_id = :p_discount_type) --AND AJ.Ajt_Adjustment_Type_Name = :SalesType ) ReportDetails WHERE-- Sales_Type = :SalesType (:p_ic_rcn is null OR :p_ic_rcn = Item_Number) AND (:p_dealer_id IS NULL OR Dealer_ID = :p_dealer_id) AND Sales_Type in ('DEALER CANCEL', 'STATE CANCEL') ORDER BY TO_DATE(SUBSTR(Transaction_Date,1,10), 'MM/DD/YYYY'), Item_Number, -- Transaction Date, RCN (numerical order), order RCN was purchased. TO_CHAR(TO_DATE(Transaction_Date, 'MM/DD/YYYY HH24:MI:SS'), 'SSSSS'); ELSE IF :p_sales_type = 'item_adjustment' THEN -- Item Adjustments SELECT TO_CHAR(AJ.aj_adjustment_date - 2/24, 'MM/DD/YYYY HH24:MI:SS') AS Transaction_Date, -- Pacific Time TO_NUMBER(IT.ic_rcn) AS Item_Number, AJ.aj_comment AS Item_Or_Adj_Description, DT.di_name AS Customer_Type, IT.it_docid AS Document_ID, AJ.ag_id AS Dealer_ID, AJ.ajt_adjustment_type_name AS Sales_Type, 0 AS Item_Quantity, NVL(AJ.aj_state_adj_amt, 0.00) + NVL(AJ.aj_other_adj_amt, 0.00) AS Total_Fee, NVL(AJ.aj_state_adj_amt, 0.00) AS State_Fee, NVL(AJ.aj_other_adj_amt, 0.00) AS Transaction_Fee, CASE WHEN AJ.aj_other_adj_amt IS NULL OR AJ.aj_other_adj_amt = 0.00 THEN 0.00 ELSE ROUND(NVL(AJ.aj_state_adj_amt, 0.00) * (:p_ao_trx_fee / 100), 2) END AS AO_Fee, CASE WHEN AJ.aj_other_adj_amt IS NULL OR AJ.aj_other_adj_amt = 0.00 THEN 0.00 ELSE AJ.aj_other_adj_amt - ROUND(NVL(AJ.aj_state_adj_amt, 0.00) * (:p_ao_trx_fee / 100), 2) END AS WDFW_Fee FROM ADJUSTMENT AJ JOIN ITEM IT ON IT.it_id = AJ.it_id JOIN DISCOUNT_TYPE DT ON DT.di_id = IT.di_id WHERE AJ.aj_status_ind = 'A' -- Include active adjustments only. AND AJ.it_id IS NOT NULL -- Include Item Adjustments only; rows with a foreign key to the ITEM table are Item Adjustments. AND AJ.ajt_id != '0' -- Unreturned Doc Charge is not an Item Adjustment, it's a Lump Sum Adjustment to the Dealers account. AND IT.ic_rcn != '999' -- Exclude Dealer Fees. AND AJ.aj_adjustment_date BETWEEN :P_beg_dt -- Pacific Time AND :P_end_dt AND (:p_discount_type IS NULL OR DT.di_id = :p_discount_type) WHERE-- Sales_Type = :SalesType (:p_ic_rcn is null OR :p_ic_rcn = Item_Number) AND (:p_dealer_id IS NULL OR Dealer_ID = :p_dealer_id) AND Sales_Type = 'ADJUSTMENT' ORDER BY TO_DATE(SUBSTR(Transaction_Date,1,10), 'MM/DD/YYYY'), Item_Number, -- Transaction Date, RCN (numerical order), order RCN was purchased. TO_CHAR(TO_DATE(Transaction_Date, 'MM/DD/YYYY HH24:MI:SS'), 'SSSSS'); ELSE IF :p_sales_type IS NULL SELECT TO_DATE(SUBSTR(Transaction_Date,1,10), 'MM/DD/YYYY'),Item_Number, REGEXP_REPLACE(SUBSTR(Item_Or_Adj_Description,1,50),'([^[:print:]])',' ') AS Item_Or_Adj_Desctription, Customer_Type, Document_ID, Dealer_ID, Sales_Type, Item_Quantity, Total_Fee, State_Fee, Transaction_Fee, AO_Fee, WDFW_Fee FROM ( -- Sales Transactions SELECT /*+ index(IT ITEM_X4) */ TO_CHAR(IT.it_status_set_date - 2/24, 'MM/DD/YYYY HH24:MI:SS') AS Transaction_Date, -- Pacific Time TO_NUMBER(IT.ic_rcn) AS Item_Number, IT.it_descr AS Item_Or_Adj_Description, DT.di_name AS Customer_Type, IT.it_docid AS Document_ID, IT.ag_id AS Dealer_ID, CASE WHEN UPPER(IST.is_name) = 'ACTIVE' THEN 'SALE' ELSE IST.is_name END AS Sales_Type, NVL(IT.it_quantity * CASE WHEN IT.is_id = 'AC' THEN 1 WHEN IT.is_id = 'DU' THEN 1 ELSE -1 END, 0) AS Item_Quantity, -- Dups = 1 NVL(IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) + NVL(IT.it_other_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) AS Total_Fee, NVL(IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) AS State_Fee, NVL(IT.it_other_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) AS Transaction_Fee, CASE WHEN IT.it_other_fee IS NULL OR IT.it_other_fee = 0.00 THEN 0.00 ELSE ROUND(IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END * (:p_ao_trx_fee / 100), 2) END AS AO_Fee, CASE WHEN IT.it_other_fee IS NULL OR IT.it_other_fee = 0.00 THEN 0.00 ELSE (IT.it_other_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END) - ROUND((IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END * (:p_ao_trx_fee / 100)), 2) END AS WDFW_Fee FROM ITEM IT JOIN DISCOUNT_TYPE DT ON DT.di_id = IT.di_id JOIN ITEM_STATUS_TYPE IST ON IST.is_id = IT.is_id WHERE IT.it_status_ind = 'A' -- Include active ITEM rows only. AND (IT.is_id IN ('AC','DC','SC') OR (IT.is_id = 'DU' AND NVL(IT.it_state_fee, 0) != 0)) -- Exclude voids, exchanges, and false duplicates. AND IT.ic_rcn != '999' -- Exclude Dealer Fees. AND IT.it_status_set_date BETWEEN :P_beg_dt -- Pacific Time AND :P_end_dt AND (:p_discount_type IS NULL OR DT.di_id = :p_discount_type) -- AND IST.is_name = :SalesType UNION ALL -- Item Adjustments SELECT TO_CHAR(AJ.aj_adjustment_date - 2/24, 'MM/DD/YYYY HH24:MI:SS') AS Transaction_Date, -- Pacific Time TO_NUMBER(IT.ic_rcn) AS Item_Number, AJ.aj_comment AS Item_Or_Adj_Description, DT.di_name AS Customer_Type, IT.it_docid AS Document_ID, AJ.ag_id AS Dealer_ID, AJ.ajt_adjustment_type_name AS Sales_Type, 0 AS Item_Quantity, NVL(AJ.aj_state_adj_amt, 0.00) + NVL(AJ.aj_other_adj_amt, 0.00) AS Total_Fee, NVL(AJ.aj_state_adj_amt, 0.00) AS State_Fee, NVL(AJ.aj_other_adj_amt, 0.00) AS Transaction_Fee, CASE WHEN AJ.aj_other_adj_amt IS NULL OR AJ.aj_other_adj_amt = 0.00 THEN 0.00 ELSE ROUND(NVL(AJ.aj_state_adj_amt, 0.00) * (:p_ao_trx_fee / 100), 2) END AS AO_Fee, CASE WHEN AJ.aj_other_adj_amt IS NULL OR AJ.aj_other_adj_amt = 0.00 THEN 0.00 ELSE AJ.aj_other_adj_amt - ROUND(NVL(AJ.aj_state_adj_amt, 0.00) * (:p_ao_trx_fee / 100), 2) END AS WDFW_Fee FROM ADJUSTMENT AJ JOIN ITEM IT ON IT.it_id = AJ.it_id JOIN DISCOUNT_TYPE DT ON DT.di_id = IT.di_id WHERE AJ.aj_status_ind = 'A' -- Include active adjustments only. AND AJ.it_id IS NOT NULL -- Include Item Adjustments only; rows with a foreign key to the ITEM table are Item Adjustments. AND AJ.ajt_id != '0' -- Unreturned Doc Charge is not an Item Adjustment, it's a Lump Sum Adjustment to the Dealers account. AND IT.ic_rcn != '999' -- Exclude Dealer Fees. AND AJ.aj_adjustment_date BETWEEN :P_beg_dt -- Pacific Time AND :P_end_dt AND (:p_discount_type IS NULL OR DT.di_id = :p_discount_type) --AND AJ.Ajt_Adjustment_Type_Name = :SalesType ) ReportDetails WHERE (:p_ic_rcn is null OR :p_ic_rcn = Item_Number) AND (:p_dealer_id IS NULL OR Dealer_ID = :p_dealer_id) ORDER BY TO_DATE(SUBSTR(Transaction_Date,1,10), 'MM/DD/YYYY'), Item_Number, -- Transaction Date, RCN (numerical order), order RCN was purchased. TO_CHAR(TO_DATE(Transaction_Date, 'MM/DD/YYYY HH24:MI:SS'), 'SSSSS'); ELSE SELECT TO_DATE(SUBSTR(Transaction_Date,1,10), 'MM/DD/YYYY'),Item_Number, REGEXP_REPLACE(SUBSTR(Item_Or_Adj_Description,1,50),'([^[:print:]])',' ') AS Item_Or_Adj_Desctription, Customer_Type, Document_ID, Dealer_ID, Sales_Type, Item_Quantity, Total_Fee, State_Fee, Transaction_Fee, AO_Fee, WDFW_Fee FROM ( -- Sales Transactions SELECT /*+ index(IT ITEM_X4) */ TO_CHAR(IT.it_status_set_date - 2/24, 'MM/DD/YYYY HH24:MI:SS') AS Transaction_Date, -- Pacific Time TO_NUMBER(IT.ic_rcn) AS Item_Number, IT.it_descr AS Item_Or_Adj_Description, DT.di_name AS Customer_Type, IT.it_docid AS Document_ID, IT.ag_id AS Dealer_ID, CASE WHEN UPPER(IST.is_name) = 'ACTIVE' THEN 'SALE' ELSE IST.is_name END AS Sales_Type, NVL(IT.it_quantity * CASE WHEN IT.is_id = 'AC' THEN 1 WHEN IT.is_id = 'DU' THEN 1 ELSE -1 END, 0) AS Item_Quantity, -- Dups = 1 NVL(IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) + NVL(IT.it_other_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) AS Total_Fee, NVL(IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) AS State_Fee, NVL(IT.it_other_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) AS Transaction_Fee, CASE WHEN IT.it_other_fee IS NULL OR IT.it_other_fee = 0.00 THEN 0.00 ELSE ROUND(IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END * (:p_ao_trx_fee / 100), 2) END AS AO_Fee, CASE WHEN IT.it_other_fee IS NULL OR IT.it_other_fee = 0.00 THEN 0.00 ELSE (IT.it_other_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END) - ROUND((IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END * (:p_ao_trx_fee / 100)), 2) END AS WDFW_Fee FROM ITEM IT JOIN DISCOUNT_TYPE DT ON DT.di_id = IT.di_id JOIN ITEM_STATUS_TYPE IST ON IST.is_id = IT.is_id WHERE IT.it_status_ind = 'A' -- Include active ITEM rows only. AND (IT.is_id IN ('AC','DC','SC') OR (IT.is_id = 'DU' AND NVL(IT.it_state_fee, 0) != 0)) -- Exclude voids, exchanges, and false duplicates. AND IT.ic_rcn != '999' -- Exclude Dealer Fees. AND IT.it_status_set_date BETWEEN :P_beg_dt -- Pacific Time AND :P_end_dt AND (:p_discount_type IS NULL OR DT.di_id = :p_discount_type) -- AND IST.is_name = :SalesType UNION ALL -- Item Adjustments SELECT TO_CHAR(AJ.aj_adjustment_date - 2/24, 'MM/DD/YYYY HH24:MI:SS') AS Transaction_Date, -- Pacific Time TO_NUMBER(IT.ic_rcn) AS Item_Number, AJ.aj_comment AS Item_Or_Adj_Description, DT.di_name AS Customer_Type, IT.it_docid AS Document_ID, AJ.ag_id AS Dealer_ID, AJ.ajt_adjustment_type_name AS Sales_Type, 0 AS Item_Quantity, NVL(AJ.aj_state_adj_amt, 0.00) + NVL(AJ.aj_other_adj_amt, 0.00) AS Total_Fee, NVL(AJ.aj_state_adj_amt, 0.00) AS State_Fee, NVL(AJ.aj_other_adj_amt, 0.00) AS Transaction_Fee, CASE WHEN AJ.aj_other_adj_amt IS NULL OR AJ.aj_other_adj_amt = 0.00 THEN 0.00 ELSE ROUND(NVL(AJ.aj_state_adj_amt, 0.00) * (:p_ao_trx_fee / 100), 2) END AS AO_Fee, CASE WHEN AJ.aj_other_adj_amt IS NULL OR AJ.aj_other_adj_amt = 0.00 THEN 0.00 ELSE AJ.aj_other_adj_amt - ROUND(NVL(AJ.aj_state_adj_amt, 0.00) * (:p_ao_trx_fee / 100), 2) END AS WDFW_Fee FROM ADJUSTMENT AJ JOIN ITEM IT ON IT.it_id = AJ.it_id JOIN DISCOUNT_TYPE DT ON DT.di_id = IT.di_id WHERE AJ.aj_status_ind = 'A' -- Include active adjustments only. AND AJ.it_id IS NOT NULL -- Include Item Adjustments only; rows with a foreign key to the ITEM table are Item Adjustments. AND AJ.ajt_id != '0' -- Unreturned Doc Charge is not an Item Adjustment, it's a Lump Sum Adjustment to the Dealers account. AND IT.ic_rcn != '999' -- Exclude Dealer Fees. AND AJ.aj_adjustment_date BETWEEN :P_beg_dt -- Pacific Time AND :P_end_dt AND (:p_discount_type IS NULL OR DT.di_id = :p_discount_type) --AND AJ.Ajt_Adjustment_Type_Name = :SalesType ) ReportDetails WHERE Sales_Type = :p_sales_type AND (:p_ic_rcn is null OR :p_ic_rcn = Item_Number) AND (:p_dealer_id IS NULL OR Dealer_ID = :p_dealer_id) ORDER BY TO_DATE(SUBSTR(Transaction_Date,1,10), 'MM/DD/YYYY'), Item_Number, -- Transaction Date, RCN (numerical order), order RCN was purchased. TO_CHAR(TO_DATE(Transaction_Date, 'MM/DD/YYYY HH24:MI:SS'), 'SSSSS'); END IF; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USA.B.Cade
    1. USRageQwit
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POZero iteration count in PL SQL Oracle
      1. This table or related slice is empty.
      1. VTDownMod
    1. COWhat is the actual error code and statement? In a PL/SQL context SQL statements such as these would need a `SELECT .. INTO ..` or some other way of handling the result set.
      singulars
      1. POZero iteration count in PL SQL Oracle
      1. USWolf
    2. COWhat ORA error number are you getting?
      singulars
      1. POZero iteration count in PL SQL Oracle
      1. USOllie
    3. COError 24333 @Ollie
      singulars
      1. POZero iteration count in PL SQL Oracle
      1. USRageQwit
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload