Solved

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

Posted on 2013-01-22
14
364 Views
Last Modified: 2013-01-22
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.
0
Comment
Question by:Sherry
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +1
14 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 38806800
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
 

Author Comment

by:Sherry
ID: 38806810
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 38806814
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38806822
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
 

Author Comment

by:Sherry
ID: 38806853
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38806877
then please try to put
ORDER BY IBS_DEBT_REFERENCE , IBS_DEBT_TYPE_CODE
to the final query
0
 

Author Comment

by:Sherry
ID: 38806957
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 38807016
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
 

Author Comment

by:Sherry
ID: 38807061
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
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 250 total points
ID: 38807323
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
 

Author Comment

by:Sherry
ID: 38807433
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
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 250 total points
ID: 38807447
Hi slusher,

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


Kent
0
 

Author Comment

by:Sherry
ID: 38807453
Ok, thanks.  I'll let you know.
0
 

Author Comment

by:Sherry
ID: 38807850
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

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Job Hung 17 37
Error building VS2105 solution from repository 1 38
SQL Distinct Question 3 15
UPDATE JOIN multiple tables 5 22
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question