[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

Oracle 9i - Select Two rows into columns

Experts,
I have a simple table (My_Table). The table records movement between accounts. For each movement, there are always 2 rows (a debit and a credit). I need to write a query to return 1 row per movement with the debit and credit accounts in colums

example table
TRANS_ID      REF_NUM       ACCT      AMT
0001            ABC123            F1234      -107
0001            ABC123            X666      107
0002            XYZ456            Q2525      -91
0002            XYZ456            D2000      91
0003            ZZZ222            y1234      198
0003            ZZZ2222              F1234      -198

Query should return like this

TRANS_ID      REF_NUM       FROM_ACCT      FROM_AMT       TO_ACCT            TO_AMT
0001            ABC123            F1234            -107             X666            107
0002            XYZ456            Q2525            -91                     D2000            91
0003            ZZZ222            F1234            -198                   y1234            198

Please help
0
JDCam
Asked:
JDCam
  • 5
  • 4
  • 3
  • +3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Therewith be several ways to write this query.
Here is my suggestion
SELECT trans_id, ref_num
, case when amt < 0 then acct end to_account
, case when amt < 0 then amt end to_amount
, case when amt > 0 then acct end from_account
, case when amt > 0 then  amt end from_amount
From portable
Group by trans_id, ref_num
0
 
MikeOM_DBACommented:
Mmmmm.... Looks like SQL 101 homework.
What have you coded so far?

Hints: Use aggregate functions ( vg. SUM / MAX ) combined with CASE or DECODE.

;)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Indeed I forgot something in my query... to be fixed by you as part of your homework.
0
Industry Leaders: 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!

 
johnsoneSenior Oracle DBACommented:
Well, I did it will an old fashioned self join.

SELECT a.trans_id, 
       a.ref_num, 
       b.acct from_acct, 
       b.amt  from_amt, 
       a.acct to_acct, 
       a.amt  to_amt 
FROM   example_table a, 
       example_table b 
WHERE  a.trans_id = b.trans_id 
       AND a.amt > 0 
       AND b.amt < 0; 

Open in new window


There are certainly many ways to crack this one.  With an index on TRANS_ID or even better (TRANS_ID, AMT), the self join shouldn't be that bad.
0
 
JDCamAuthor Commented:
Homework?.... far from it. I just simplified the scenario so it could be understood.... my mistake.
The real scenario is within our warehouse system involving the movement of inventory.
I will post the real query shortly but it will involve 20 to 30 columns that are irrelevant to the question.

there is just under a million rows of data. I started with this in Crystal reports but once a subreport was added. the query was 20 mins+.
0
 
JDCamAuthor Commented:
here is the real query stripped down as far as I can.

select 
DOC_NUM,
MVT_EFF_TRANS_DATE,
INVT_LEV1,
INVT_LEV2,
INVT_LEV3,
LOC_CODE, 
TRANS_UNIT
from c_MVT_H where comp_code = 'A1' and cust_Code = 'PROTOR' 
and mvt_trans_tp = 'RL'
and MVT_EFF_TRANS_DATE > '01-AUG-14'
order by DOC_NUM

Open in new window


This will return 2 rows for every movement. DOC_NUM is what links them.
The last two columns LOC_CODE and TRANS_UNIT are what need to be duplicated as TO/FROM
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Syntax wise, suggestions have been given.
Performance wise, you may check out materialized views
0
 
JDCamAuthor Commented:
I disagree...
I was looking for an answer (Syntax). Not to be told what I need to go study to solve it myself.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
My very first post is very concrete syntax, just missing MAX(   )  around the CASE ... END parts
What else are you looking for?
0
 
PortletPaulCommented:
Given you started with a simplified financial example, but in reality it's an inventory requirement, are there more facts we need?

Can you provide a sample of that stripped down query and required result?

But, if the essence of the need to to pivot as shown initially, the approach by angeliii is very likely to be relevant
(but he had done what I often fall for - forgot the max( ... ) )

SELECT trans_id, ref_num
, MAX( case when amt < 0 then acct end ) to_account
, MAX( case when amt < 0 then amt end ) to_amount
, MAX( case when amt > 0 then acct end ) from_account
, MAX( case when amt > 0 then  amt end ) from_amount
From portable
Group by trans_id, ref_num

------------------

Note: the simple example shows only "one to one" adjustments,
Are there circumstances where a single debit result in 2 or more credits? (or vice versa)
0
 
johnsoneSenior Oracle DBACommented:
The "please do my job for me for free", comments aren't going to get you very far on this site.  We are all volunteers here.  We want to teach you how to figure it out, not do it for you.  There really isn't much in this for us.  There are multiple syntax examples for you to use to construct your query.  Since you aren't sharing the exact syntax of the original query, there is going to be some work on your part anyway.

Given your new example, I believe the questions are going to be are the 4 columns (other than DOC_NUM) always going to be the same?  Guaranteed?  If not, which set do you want?  The from set or the to set?

If they are guaranteed to be the same this is an example with Guy's method:

select 
DOC_NUM,
MVT_EFF_TRANS_DATE,
INVT_LEV1,
INVT_LEV2,
INVT_LEV3,
max(case when trans_unit < 0 then LOC_CODE end) to_account, 
max(case when trans_unit < 0 then TRANS_UNIT end) to_amount,
max(case when trans_unit > 0 then LOC_CODE end) from_account, 
max(case when trans_unit > 0 then TRANS_UNIT end) from_amount
from c_MVT_H where comp_code = 'A1' and cust_Code = 'PROTOR' 
and mvt_trans_tp = 'RL'
and MVT_EFF_TRANS_DATE > '01-AUG-14'
group by doc_num, mvt_eff_trans_date, invt_lev1, invt_lev2, invt_lev3
order by DOC_NUM

Open in new window


If not guaranteed to be the same, then a self join may be the better method.  This method would also work if the extra columns are guaranteed to be the same.  This would give the from set using my example.

SELECT b.doc_num, 
       b.mvt_eff_trans_date, 
       b.invt_lev1,
       b.invt_lev2,
       b.invt_lev3,
       b.acct from_loc_code, 
       b.amt  from_trans_unit, 
       a.acct to_loc_code, 
       a.amt  to_trans_unit 
from c_MVT_H a, c_mvt_h b where b.comp_code = 'A1' and b.cust_Code = 'PROTOR' 
and b.mvt_trans_tp = 'RL'
and b.MVT_EFF_TRANS_DATE > to_date('01-AUG-14','dd-mon-yy')
and  a.trans_id = b.trans_id 
       AND a.amt > 0 
       AND b.amt < 0;

Open in new window


These are only 2 examples of how to write this query.  There are many other ways to do this.
0
 
awking00Commented:
>>here is the real query stripped down as far as I can.<<
select DOC_NUM, MVT_EFF_TRANS_DATE, INVT_LEV1, INVT_LEV2, INVT_LEV3, LOC_CODE, TRANS_UNIT
from c_MVT_H where comp_code = 'A1' and cust_Code = 'PROTOR' and mvt_trans_tp = 'RL' and MVT_EFF_TRANS_DATE > '01-AUG-14'
order by DOC_NUM
It seems most solutions offered are using an amount field, which I don't readily see in your query. Can you provide some sample data for this query (include multiple negative values per single positive values and vice versa if they exist) and your expected output?
0
 
johnsoneSenior Oracle DBACommented:
It looks like I missed an edit on the self join of the new example.

SELECT b.doc_num, 
       b.mvt_eff_trans_date, 
       b.invt_lev1,
       b.invt_lev2,
       b.invt_lev3,
       b.loc_code from_loc_code, 
       b.trans_unit  from_trans_unit, 
       a.loc_code to_loc_code, 
       a.trans_unit  to_trans_unit 
from c_MVT_H a, c_mvt_h b where b.comp_code = 'A1' and b.cust_Code = 'PROTOR' 
and b.mvt_trans_tp = 'RL'
and b.MVT_EFF_TRANS_DATE > to_date('01-AUG-14','dd-mon-yy')
and  a.doc_num = b.doc_num 
       AND a.trans_unit > 0 
       AND b.trans_unit < 0;

Open in new window

0
 
JDCamAuthor Commented:
Thank you all for the assistance. I will try your suggestions later today.
0
 
JDCamAuthor Commented:
I tried this as the first solution presented and it works great. Performance is decent.
I will add this solution to my knowledge base for future reference
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now