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

I'm trying to write a recursive script that will give me items in the order I need them.

Debt
Payment
Debt
Payment, etc.

I get the correct data, but can't seem to get this in the order I want.  Can anyone help or is this possible with this type of query?  This is a frozen database, so no changes will happen to the data.
SherryDeveloperAsked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi slusher,

Either approach should work, as long as you identify the grouping/sort rules.


Kent
0
 
Jim P.Commented:
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.
0
 
SherryDeveloperAuthor Commented:
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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
 
SherryDeveloperAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then please try to put
ORDER BY IBS_DEBT_REFERENCE , IBS_DEBT_TYPE_CODE
to the final query
0
 
SherryDeveloperAuthor Commented:
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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
SherryDeveloperAuthor Commented:
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
0
 
Jim P.Commented:
You could always add a sequence number by using the ROW_NUMBER ( )  OVER . Then you can order by that number.

I've done it more than once where I have three sub-queries in a union and then order by the PKs and then the sequence number.

ROW_NUMBER ( )      OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

Open in new window

0
 
SherryDeveloperAuthor Commented:
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.
0
 
SherryDeveloperAuthor Commented:
Ok, thanks.  I'll let you know.
0
 
SherryDeveloperAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.