Recursive query to replace loops and cursor of a PL SQL script

Oracle uses PL SQL, MS SQL Server T-SQL.

Either way can you post what you have already and we can see about sorting it out.

DECLARE   @DOC_NB      NUMERIC(10,0)
SET @DOC_NB = '263262';


WITH DEBT (DOC_NB,[LEVEL],IBS_Type,IBS_Description,IBS_Date,IBS_LOC,IBS_REF,Amount,IBS_Batch,IBS_Item,Balance_Due)
AS
      
(      SELECT
                  D.IBS_DOC_NUMBER AS DOC_NB
            --,      1 AS [LEVEL]
            ,      D.IBS_DEBT_TYPE_CODE AS IBS_Type
            ,      DT.DEBT_TYPE_DESCRIPTION  AS IBS_Description
            ,      D.IBS_DEBT_DATE AS IBS_Date
            ,      D.IBS_DEBT_LOCATION AS IBS_LOC
            ,      D.IBS_DEBT_REFERENCE  AS IBS_REF
            ,      D.IBS_DEBT_AMOUNT AS Amount
            ,      D.IBS_BATCH_NUMBER AS IBS_Batch
            ,      D.IBS_BATCH_ITEM AS IBS_Item
            ,      D.IBS_BALANCE_DUE AS Balance_Due
            
                        
      FROM OMS_OWNER.TB_IBS_DEBT D
            INNER JOIN OMS_OWNER.TB_DEBT_TYPE DT
                  ON ( DT.IBS_DEBT_TYPE_CODE = D.IBS_DEBT_TYPE_CODE )

UNION ALL

      SELECT
                  DP.IBS_DOC_NUMBER AS DOC_NB
            --,      2 AS [LEVEL]
            ,      DP.IBS_REPAY_TYPE_CODE AS IBS_Type
            ,      RT.REPAY_TYPE_DESCRIPTION AS IBS_Description
            ,      DP.IBS_REPAY_DATE AS IBS_Date
            ,      DP.IBS_REPAY_LOC AS IBS_LOC
            ,      DP.IBS_REPAY_REFERENCE AS IBS_REF
            ,      DP.IBS_REPAY_AMOUNT AS Amount
            ,      NULL
            ,      NULL
            ,      NULL
                              
      FROM OMS_OWNER.TB_IBS_DEBT_PAY DP
            INNER JOIN OMS_OWNER.TB_REPAY_TYPE RT
            ON ( RT.IBS_REPAY_TYPE_CODE = DP.IBS_REPAY_TYPE_CODE )
            INNER JOIN DEBT D ON D.IBS_Type = DP.IBS_DEBT_TYPE_CODE
                        AND D.IBS_REF = DP.IBS_DEBT_REFERENCE
                  
      WHERE DP.IBS_DOC_NUMBER = @DOC_NB
            
)
--==========================================================================
SELECT distinct
            DOC_NB,
            [LEVEL],
            IBS_Type,
            IBS_Description,
            IBS_Date,
            IBS_LOC,
            IBS_REF,
            Amount,
            IBS_Batch,
            IBS_Item,
            Balance_Due
      
FROM DEBT D

WHERE DOC_NB = @DOC_NB


      
      
GO

Hi Slusher,

That's not really a recursive query.  :)  But getting your results are pretty easy.

Keep in mind that for any result set, ALL rows have to have compatible data types in corresponding columns.  If column 1 is an integer in part of the query, it can't contain a string in another.

SELECT account, 1, {debt calculation} FROM ...
UNION ALL
SELECT account, 2, {payment calculation} FROM
ORDER BY 1, 2;

or

SELECT account, {case when type = debt the 1 else 2 end}, amount FROM
ORDER BY 1, 2;

Either approach should get you pretty close.


Good Luck,
Kent

without knowing how to "relate" the dept and the payment in regards to your sql, we cannot "help".

it will be a "ORDER BY", specifying the "rules" to relate the "2" records together.

Kent, data types are the same, so that's no issue there.  The only way I can see that they are tied together in original is by using a debt or payment rowtype with the cursors and loops, and this join

INNER JOIN DEBT D ON D.IBS_Type = DP.IBS_DEBT_TYPE_CODE
                        AND D.IBS_REF = DP.IBS_DEBT_REFERENCE

then please try to put
ORDER BY IBS_DEBT_REFERENCE , IBS_DEBT_TYPE_CODE
to the final query

It still shows all debts first then payments.  There are fields to match but then the payments also have their own.

Debts:  ibs_debt_type_code, ibs_debt_reference

Payments: ibs_debt_type_code, ibs_debt_reference, ibs_repay_type_code, ibs_repay_reference

So, the debt_type_code needs to match, but in the final result the debt_type_code and repay_type_code are in the same column, so as the debt_ref and the repay_ref.  

The original code has an order by in the first select/loop/cursor. by debt_type and date

Hi slusher,

The column names are a bit misleading to those of us not in tune with your application.  Still, it seems like the correct data should be there and it's just a matter of finding the correct sort parameters.

What's the primary grouping?  Account?  Owner?  Location?  That should be the first item in the ORDER BY clause with [LEVEL] the second item.

You might want a 3rd column (date or other incremental value) to put the payments in chronological order.


Kent

I'll see what I can do with this.  I think you're right.  I need to find a way to put the payments in some order.  I'll let you know if I come up with anything closer.  Thank you

I was just trying that, think I'm doing it wrong though.  Should I put this on just the first one or the second query?  I had it on both, got 1,2.... on the second query but got number over 6,000 on the first query.

Ok, thanks.  I'll let you know.

Ok, the row_number helped, I put in on both tables  and then did the order by the row_number, debt_type and then date.  It's really close, I just have a few on the report that would be considered "out of order"  I'll keep working on that.  I'm sure it's just how it's pulling the items.  Thank you for your help.