asked on

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

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 ?

Kent Dyer

What you need is concatenation.. Start here..

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

HTH,

Kent

Rose_Taylor

ASKER

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 ?

oleggold

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"

Rose_Taylor

ASKER

Can you please give me example...I'm new to SQL.

PortletPaul

>>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.

PortletPaul

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

Rose_Taylor

ASKER

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

PortletPaul

? 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.)

Rose_Taylor

ASKER

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 ?

PortletPaul

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

Rose_Taylor

ASKER

No , your query return only one column , its not combing.

PortletPaul

in one column, that means it is concatenated....

what does "combining" mean to you?

Rose_Taylor

ASKER

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.

PortletPaul

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?

Rose_Taylor

ASKER

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

PortletPaul

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)

PortletPaul

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

Rose_Taylor

ASKER

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 ?

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

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.

Rose_Taylor

ASKER

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.

Rose_Taylor

ASKER

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

PortletPaul

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

Rose_Taylor

ASKER

Good

PortletPaul

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?