Solved

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

Posted on 2013-01-22
14
358 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
  • 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:Kdo
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
 
LVL 142

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 142

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 45

Expert Comment

by:Kdo
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:
Kdo 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now