Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-22
14
Medium Priority
?
378 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 46

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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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 46

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 750 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 46

Accepted Solution

by:
Kent Olsen earned 750 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

916 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