Solved

How to combine the fields and get groups values in Oracle SQL ?

Posted on 2013-06-02
26
579 Views
Last Modified: 2013-06-04
Hello ,

Please find below Oracle SQL query...

SELECT prod.mat_nbr , part.prod_full_descr , pre.part_id , prepart.prod_full_descr , pre.left_paran , pre.and_or_operation , pre.right_paran   FROM Publish pub, Product prod, Part part,  Preqs pre, Part_t prepart   WHERE pub.product_Key = prod.product_Key AND part.part_nbr = prod.mat_nbr AND part.part_id = pre.parent_part_id (+) AND part.fiscal_quarter = pre.fiscal_quarter (+) AND prepart.fiscal_quarter = '2014-2' AND prepart.part_id = pre.part_id AND part.fiscal_quarter = '2014-2' AND prod.div_Descr not in('N/A', 'Unassigned', 'Unknown', 'Not applicable') AND prod.source_System = 'SAP' AND  pub.currency = 'USD' AND  pub.pl_group_key = '137' AND  pub.publish_period_key = 675282  order by  prod.mat_nbr

SQL out put:

PRODUCT,DESCR,                PRENBR,  PREDESCR,       PARAN1,  ANDOROP,  PARAN2
101401     Opsim Op SW    1595        AccuSim II       SW              OR                  IT
101401     Opsim Op SW    3533        Eldo Ap            SW              AND               AP
101401     Opsim Op SW    3534        SimPilot           NS               AND               PT
101400     Simple               1234        samp               SW              AND                AL
101400     karupm             3213        oaera               W                OR                  AZ    

I want beow format:


PRODUCT,DESCR,                PRENBR,  PREDESCR,       Relations,  
101401     Opsim Op SW    1595        AccuSim II       SW  OR    IT
101401     Opsim Op SW    3533        Eldo Ap            SW  AND  AP
101401     Opsim Op SW    3534        SimPilot           NS   AND  PT
101400     Simple               1234        samp               SW   AND  AL
101400     karupm             3213        oaera               W     OR     AZ  

How to combine paran1,andoror,paran2 fields for associated productnbrs ?
0
Comment
Question by:Rose_Taylor
  • 12
  • 11
  • +1
26 Comments
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 39214382
What you need is concatenation..  Start here..

http://docs.oracle.com/cd/B19306_01/server.102/b14200/operators003.htm

HTH,

Kent
0
 

Author Comment

by:Rose_Taylor
ID: 39214411
I have four rows of same product nbr and i need fourth row values in first row itself.How to get all rows values when executing query ?
0
 
LVL 21

Expert Comment

by:oleggold
ID: 39214431
Use self join with Your outer join .eg.:
"
SELECT prod.mat_nbr , part.prod_full_descr , pre.part_id , prepart.prod_full_descr , pre.left_paran , pre.and_or_operation , pre.right_paran   FROM Publish pub, Product prod, Part part,  Preqs pre, Part_t prepart   WHERE pub.product_Key = prod.product_Key AND part.part_nbr = prod.mat_nbr AND part.part_id = pre.parent_part_id (+) AND part.fiscal_quarter = pre.fiscal_quarter (+) AND prepart.fiscal_quarter = '2014-2' AND prepart.part_id = pre.part_id AND part.fiscal_quarter = '2014-2' AND prod.div_Descr not in('N/A', 'Unassigned', 'Unknown', 'Not applicable') AND prod.source_System = 'SAP' AND  pub.currency = 'USD' AND  pub.pl_group_key = '137' AND  pub.publish_period_key = 675282  
and prod.mat_nbr=prod1.mat_nbr(+)
order by  prod.mat_nbr"
0
 

Author Comment

by:Rose_Taylor
ID: 39214450
Can you please give me example...I'm new to SQL.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39215003
>>How to combine paran1,andoror,paran2 fields for associated productnbrs ?
This is how you concatenate those 3 fields:

SELECT prod.mat_nbr
    , part.prod_full_descr
    , pre.part_id
    , prepart.prod_full_descr
    , pre.left_paran
      || ' '
      || pre.and_or_operation
      || ' '
      || pre.right_paran
      as Relations

>>I have four rows of same product nbr and i need fourth row values in first row itself.
You need to "show" us what you mean the "expected results" in the question does not display this.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39215032
>>I'm new to SQL.
Being new to SQL I would suggest you learn "ANSI JOINS" instead of the older Oracle join syntax. Below is what I think the ANSI equivalent syntax is for your query
SELECT prod.mat_nbr
    , part.prod_full_descr
    , pre.part_id
    , prepart.prod_full_descr
    , pre.left_paran
      || ' '
      || pre.and_or_operation
      || ' '
      || pre.right_paran
      as Relations
FROM Publish pub
INNER JOIN Product prod ON  pub.product_Key = prod.product_Key
INNER JOIN Part part ON prod.mat_nbr = part.part_nbr
LEFT JOIN Preqs pre ON  part.part_id = pre.parent_part_id AND part.fiscal_quarter = pre.fiscal_quarter
LEFT JOIN Part_t prepart ON pre.part_id = prepart.part_id AND prepart.fiscal_quarter = '2014-2'
WHERE  part.fiscal_quarter = '2014-2'
    AND prod.div_Descr NOT IN ('N/A', 'Unassigned', 'Unknown', 'Not applicable')
    AND prod.source_System = 'SAP'
    AND pub.currency = 'USD'
    AND pub.pl_group_key = '137'
    AND pub.publish_period_key = 675282
ORDER BY prod.mat_nbr

Open in new window

but please note as I don't know you data model this is a best guess only.

ANSI join syntax is really does make SQL easier, this may help:
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
0
 

Author Comment

by:Rose_Taylor
ID: 39215262
Thank you soo much for effort.

But relations are not coming in one line as like mentioned in expected output.
PRODUCT,DESCR,                PRENBR,  PREDESCR,       Relations,  
101401     Opsim Op SW    1595        AccuSim II       SW  OR    IT
101401     Opsim Op SW    3533        Eldo Ap            SW  AND  AP
101401     Opsim Op SW    3534        SimPilot           NS   AND  PT
101400     Simple               1234        samp               SW   AND  AL
101400     karupm             3213        oaera               W     OR     AZ
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39215276
? you state the output does this:
PRODUCT,DESCR,                PRENBR,  PREDESCR,       PARAN1,  ANDOROP,  PARAN2
101401     Opsim Op SW    1595        AccuSim II       SW              OR                  IT
101401     Opsim Op SW    3533        Eldo Ap            SW              AND               AP
101401     Opsim Op SW    3534        SimPilot           NS               AND               PT

and that the expected result (of just those rows) is this:
PRODUCT,DESCR,                PRENBR,  PREDESCR,       Relations,  
101401     Opsim Op SW    1595        AccuSim II       SW  OR    IT
101401     Opsim Op SW    3533        Eldo Ap            SW  AND  AP
101401     Opsim Op SW    3534        SimPilot           NS   AND  PT

are you ALSO wanting to reduce the number of rows to one, something like this?
PRODUCT,DESCR,                PRENBR,  PREDESCR,       Relations,  
101401     Opsim Op SW    1595        AccuSim II       SW OR IT, SW AND AP, NS AND PT

If this is what you want, this is a quite a different solution (that cannot be met by simple concatenation).

Please confirm the exact expected results

Also: May I ask why you must do this in SQL? (It might be easier to do this in the "presentation layer" e.g. java, php etc.)
0
 

Author Comment

by:Rose_Taylor
ID: 39215319
No, I want all rows...

Yes ,correct.but using java also i'm facing issue.if you able to help me in java , please advise me.

here is code.

Iterator<Search> iterator = pre.iterator();
List list = null;
while(iterator.hasNext())
{
Search pre= (Search)iterator.next();
pre.getProductNbr();
pre.getProductDescr();
pre.getPrereqspartNbr();
pre.getPrereqproddescr();
}

How to iterate those values in one row ?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39215335
then if you want all rows then concatenation is doing it (as far as I can see)

what is wrong with this? it has combined 3 columns into one column.

PRODUCT,DESCR,                PRENBR,  PREDESCR,       Relations,  
101401     Opsim Op SW    1595        AccuSim II       SW  OR    IT
101401     Opsim Op SW    3533        Eldo Ap            SW  AND  AP
101401     Opsim Op SW    3534        SimPilot           NS   AND  PT
101400     Simple               1234        samp               SW   AND  AL
101400     karupm             3213        oaera               W     OR     AZ
0
 

Author Comment

by:Rose_Taylor
ID: 39215345
No , your query return only one  column , its not combing.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39215350
in one column, that means it is concatenated....

what does "combining" mean to you?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Rose_Taylor
ID: 39215355
PRODUCT,DESCR,                PRENBR,  PREDESCR,       Relations,  
101401     Opsim Op SW    1595        AccuSim II       1595 SW 3533  OR 3534   IT
101401     Opsim Op SW    3533        Eldo Ap            1595 SW 3533  OR 3534   IT
101401     Opsim Op SW    3534        SimPilot           1595 SW 3533  OR 3534   IT
101400     Simple               1234        samp               1234 SW 3213  AND  AL
101400     karupm             3213        oaera               1234 SW 3213  AND  AL

I would like to get 4 rows prenbr concatenated with all relations columns.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39215379
could you please tell me if the query at ID: 39215032 (with ansi joins)  is producing the same rows as your existing query please?

also, can the relations than span 4 or more rows?
0
 

Author Comment

by:Rose_Taylor
ID: 39215389
Yes, Partially its good.but those fields are no data in the database.So Could you please help me to get the relations as like ID: 39215355. Because thats field very important to in this column.

pre.part_id and pre.and_or_operation must return some values as like 1111 OR 234 AND 213 OR 123 OR like that.

Please let me know if you need any input
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39216068
This isn't a simple request - in my opinion without some valid data we would be guessing to much.

Any chance of getting the full table definitions? (all tables)
and some sample data from each of the tables please (NOT the query output)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39216132
mmm, playing with listagg gets close - but it requires a grouping

PRODUCT       Relations
101400      1234 SW AND 3213 W OR
101401      1595 SW OR 3533 SW AND 3534 NS AND

select
  product
, LISTAGG(prenbr  || ' ' || paran1 || ' ' || andorop ,' ' ) WITHIN GROUP (order by prenbr)
as x
from ( select * from existing query )
group by product

http://sqlfiddle.com/#!4/ae1ad/1
0
 

Author Comment

by:Rose_Taylor
ID: 39216207
but its not going return morethan one rows ? like if i have 5 rows same produc number , but its going to return only one row , correct ?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39216211
I may have an approach, it uses your existing query but places this into a subquery using subquery factorization, then listagg is used against that derived result, then the 2 are combined. This is simulated at: http://sqlfiddle.com/#!4/ae1ad/10
results:
PRODUCT      DESCR            PRENBR      PREDESCR            RELATIONS
101400      karupm                  3213      oaera            1234 SW AND 3213 W
101400      Simple                  1234      samp            1234 SW AND 3213 W
101401      Opsim Op SW      3534      SimPilo            1595 SW OR 3533 SW AND 3534 NS
101401      Opsim Op SW      3533      Eldo Ap            1595 SW OR 3533 SW AND 3534 NS
101401      Opsim Op SW      1595      AccuSim II      1595 SW OR 3533 SW AND 3534 NS
with
  SQF1 as (
           YOUR EXISTING QUERY HERE
          )
, SQF2 as (
          select
            product
          , LISTAGG(prenbr  || ' ' || paran1 || ' ' || andorop ,' ' ) WITHIN GROUP (order by prenbr)
          as relate
          from SQF1
          group by product
          )
select
  sqf1.product
, sqf1.descr
, sqf1.prenbr
, sqf1.predescr
, case
    when substr(sqf2.relate,-2)= 'OR' then substr(sqf2.relate, 1, length(sqf2.relate)-3)
    when substr(sqf2.relate,-3)='AND' then substr(sqf2.relate, 1, length(sqf2.relate)-4)
  end relations
from sqf1
inner join sqf2 on sqf1.product = sqf2.product
;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39216254
I have asked if you wanted one row and you said no, you wanted all rows

I am trying to match the results you asked for most recently, where the relations are repeated on each row for the same product (this should not be described as one row)

If you have changed the requirement again I think will allow others to meet that new requirement.

Please refer to the results you see above, and to the URL provided above. That is what I believe you asked for.
0
 

Author Comment

by:Rose_Taylor
ID: 39216303
Great for your effort.
Could you please help me to combine these queries.
-----------------------------------------------------------------------------------------------------
 YOUR EXISTING QUERY HERE - which i have posted in Original question ?
-------------------------------------------------------------------------------------------------------
SQF2 as (
          select
            product
          , LISTAGG(prenbr  || ' ' || paran1 || ' ' || andorop ,' ' ) WITHIN GROUP (order by prenbr)
          as relate
          from SQF1
          group by product
          )

------------------------------------------------------------------------------------------------------
Are you mean product field and listagg field from different tables.
Product prod - product,
,Preqs pre - prenbr , andorop  

This is the original fields structure come from tables.
0
 

Author Comment

by:Rose_Taylor
ID: 39216351
whatever you showed results in ID:39216211 , the same result i'm looking.Great.
Please help me to merge these two queries.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39217584
I do hope you have Oracle 11g - the LISTAGG function is in Oracle 11g Release 2 and only available since that version - I forgot to mention that above

If you do not have Oracle 11g then more is needed, and for this we need sample data (see: ID: 39216068). Please also provide the Oracle version you do have.

here is the overall query for 11g (using listagg)
WITH
  SQF1 AS (
            /* this is the original query (in the question) - without the order by */
            SELECT prod.mat_nbr
                , part.prod_full_descr
                , pre.part_id
                , prepart.prod_full_descr
                , pre.left_paran
                , pre.and_or_operation
                , pre.right_paran
            FROM Publish pub
                , Product prod
                , Part part
                , Preqs pre
                , Part_t prepart
            WHERE pub.product_Key = prod.product_Key
                AND part.part_nbr = prod.mat_nbr
                AND part.part_id = pre.parent_part_id(+)
                AND part.fiscal_quarter = pre.fiscal_quarter(+)
                AND prepart.fiscal_quarter = '2014-2'
                AND prepart.part_id = pre.part_id
                AND part.fiscal_quarter = '2014-2'
                AND prod.div_Descr NOT IN ('N/A', 'Unassigned', 'Unknown', 'Not applicable')
                AND prod.source_System = 'SAP'
                AND pub.currency = 'USD'
                AND pub.pl_group_key = '137'
                AND pub.publish_period_key = 675282
          )
, SQF2 AS (
           SELECT
              product
            , CASE
                WHEN substr(relate,-2)= 'OR' THEN substr(relate, 1, length(relate)-3)
                WHEN substr(relate,-3)='AND' THEN substr(relate, 1, length(relate)-4)
              END relations
           FROM (
                  SELECT
                    product
                  , LISTAGG(prenbr  || ' ' || paran1 || ' ' || andorop ,' ' ) WITHIN GROUP (ORDER BY prenbr)
                  AS relate
                  FROM SQF1
                  GROUP BY product
                )
          )
SELECT
  sqf1.product
, sqf1.descr
, sqf1.prenbr
, sqf1.predescr
, sqf2.relations
FROM sqf1
INNER JOIN sqf2 ON sqf1.product = sqf2.product
;

Open in new window

0
 

Author Closing Comment

by:Rose_Taylor
ID: 39217605
Good
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39217711
A should be the default grade awarded unless the answer is deficient.
Why the grading of B? that is not an appropriate grading for this answer in my view
B is the grade given for acceptable solutions, or a link to an acceptable solution. A B grade means the solution given lacked some information or required you to do a good amount of extra work to resolve the problem. When closing the question, the asker should explain why a B grade was awarded.
http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-

What information was lacking? Did you have to do a "good amount of work" to resolve the problem?

I even inserted the existing query for you (and at the same time also improved the overall solution efficiency). Why was I penalized?
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video teaches viewers about errors in exception handling.

743 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

14 Experts available now in Live!

Get 1:1 Help Now