Home » SQL & PL/SQL » SQL & PL/SQL » Help with joins
Help with joins [message #280119] Mon, 12 November 2007 09:54 Go to next message
jordan_dba
Messages: 19
Registered: May 2007
Location: SF, CA
Junior Member
Folks don't kill me... I am simply because I am stuck Smile I really honestly appreciate any help.


Can you please help me with the sql below? I am trying to do a self-join to get the output from the table to look like this

SS-1004~Vitamin C~8.33~500~Mg~(As Sodium Ascorbate)

when the sku_id has more than one row.. I get all the different combinations.. is it my joins or can I just not achieve that output this way?

select distinct (a.sku_id ||'~'||b.ingredient_name ||'~'|| c.rda_percent ||'~'|| d.amount_contained ||'~'||e.units||'~'||f.ingredient_details) as ingredient_facts
from sku_facts a, sku_facts b,sku_facts c,sku_facts d,
sku_facts e,sku_facts f
where a.sku_id = b.sku_id
and b.sku_id = c.sku_id
and c.sku_id = d.sku_id
and d.sku_id = e.sku_id
and e.sku_id = f.sku_id
and a.sku_id = 'SS-1004'
/
Re: Help with joins [message #280120 is a reply to message #280119] Mon, 12 November 2007 09:59 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
To be honest i have not understood a bit what you want to achieve. Unless and until you are going to help me out with what you want (preferably with some test data and expected results) and justifying your output, it will be a pure guess and i personally feel it's waste of my time and your time. Also please read the forum guidelines before you post again.

Regards

Raj
Re: Help with joins [message #280121 is a reply to message #280119] Mon, 12 November 2007 10:03 Go to previous messageGo to next message
jordan_dba
Messages: 19
Registered: May 2007
Location: SF, CA
Junior Member
test data coming up
Re: Help with joins [message #280123 is a reply to message #280119] Mon, 12 November 2007 10:12 Go to previous messageGo to next message
jordan_dba
Messages: 19
Registered: May 2007
Location: SF, CA
Junior Member
create table sku_facts
( sku_id VARCHAR2(40),
ingredient_name VARCHAR2(80),
rda_percent NUMBER(6,3),
amount_contained NUMBER(12,3),
units VARCHAR2(50),
ingredient_details VARCHAR2(150))


insert into sku_facts ( 'SU-1004','Vitamin C',8.33,500,'Mg','As Sodium Ascorbate);
insert into sku_facts ( 'SU-1005','Rose Hips',0,25,'Mg','Rosa');
insert into sku_facts ( 'SU-1005','Vitamin D',6,75,'Mg','Ascorbic Acid');
Re: Help with joins [message #280124 is a reply to message #280120] Mon, 12 November 2007 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

I don't understand what you want and with the data you provided your query will return nothing as sku_id is different.

Regards
Michel
Re: Help with joins [message #280132 is a reply to message #280124] Mon, 12 November 2007 10:41 Go to previous messageGo to next message
jordan_dba
Messages: 19
Registered: May 2007
Location: SF, CA
Junior Member
My apologies... Hope I am clear this time. And I appreciate you being patient with me.


create table sku_facts
( sku_id VARCHAR2(40),
ingredient_name VARCHAR2(80),
rda_percent NUMBER(6,3),
amount_contained NUMBER(12,3),
units VARCHAR2(50),
ingredient_details VARCHAR2(150))


insert into sku_facts ( 'SS-1004','Vitamin C',8.33,500,'Mg','As Sodium Ascorbate);
insert into sku_facts ( 'SS-1005','Rose Hips',0,25,'Mg','Rosa');
insert into sku_facts ( 'SS-1005','Vitamin D',6,75,'Mg','Ascorbic Acid');



Can you please help me with the sql below? I am trying to do a self-join to get the output from the table to look like this

SS-1004~Vitamin C~8.33~500~Mg~(As Sodium Ascorbate)

when the sku_id has more than one row.. I get all the different combinations.. is it my joins or can I just not achieve that output this way?

select distinct (a.sku_id ||'~'||b.ingredient_name ||'~'|| c.rda_percent ||'~'|| d.amount_contained ||'~'||e.units||'~'||f.ingredient_details) as ingredient_facts
from sku_facts a, sku_facts b,sku_facts c,sku_facts d,
sku_facts e,sku_facts f
where a.sku_id = b.sku_id
and b.sku_id = c.sku_id
and c.sku_id = d.sku_id
and d.sku_id = e.sku_id
and e.sku_id = f.sku_id
and a.sku_id = 'SS-1004'
/



I would like the output to be like this

SS-1004~Vitamin C~8.33~500~Mg~(As Sodium Ascorbate)
SS-1005~Rose Hips~0~25~Mg~Rosa | SS-1005~Vitamin D~6~75~Mg~Ascorbic Acid



This is what I get
INGREDIENT_FACTS
SS-1004~Vitamin C~8.33~500~Mg~(As Sodium Ascorbate)
SS-1005~Rose Hips~6~25~Mg~Rosa
SS-1005~Rose Hips~6~25~Mg~Ascorbic Acid
SS-1005~Rose Hips~0~25~Mg~Ascorbic Acid
SS-1005~Rose Hips~0~75~Mg~Ascorbic Acid
SS-1005~Vitamin D~0~75~Mg~Ascorbic Acid
SS-1005~Vitamin D~6~25~Mg~Rosa
SS-1005~Vitamin D~0~25~Mg~Rosa
SS-1005~Vitamin D~0~75~Mg~Rosa
SS-1005~Vitamin D~6~25~Mg~Ascorbic Acid
SS-1005~Vitamin D~0~25~Mg~Ascorbic Acid
SS-1005~Rose Hips~0~25~Mg~Rosa
SS-1005~Vitamin D~6~75~Mg~Rosa
SS-1005~Rose Hips~6~75~Mg~Ascorbic Acid
SS-1005~Vitamin D~6~75~Mg~Ascorbic Acid
SS-1005~Rose Hips~6~75~Mg~Rosa
SS-1005~Rose Hips~0~75~Mg~Rosa


I used the preview this time and it looks good Smile
Re: Help with joins [message #280137 is a reply to message #280132] Mon, 12 November 2007 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You seem to not having seen code tags.

What you want is not a self-join it is a pivot (you want to pivot 2 lines into 2 columns).
Search for this word.

Regards
Michel
Re: Help with joins [message #280143 is a reply to message #280119] Mon, 12 November 2007 11:04 Go to previous messageGo to next message
jordan_dba
Messages: 19
Registered: May 2007
Location: SF, CA
Junior Member
cheers!
Re: Help with joins [message #280364 is a reply to message #280143] Tue, 13 November 2007 06:03 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
Hi U can Try the query as follows

SELECT RESULT
FROM   (SELECT LEAD(a.sku_id) OVER(ORDER BY a.sku_id) AS sku1_id
             , LEAD(   a.sku_id
                    || '~'
                    || a.ingredient_name
                    || '~'
                    || a.rda_percent
                    || '~'
                    || amount_contained
                    || '~'
                    || units
                    || '~'
                    || ingredient_details
                   ) OVER(ORDER BY a.sku_id) AS leadorder
             , CASE
                  WHEN LEAD(a.sku_id) OVER(ORDER BY a.sku_id) IS NOT NULL
                     THEN(CASE
                             WHEN a.sku_id =
                                           LEAD(a.sku_id) OVER(ORDER BY a.sku_id)
                                THEN    a.sku_id
                                     || '~'
                                     || a.ingredient_name
                                     || '~'
                                     || a.rda_percent
                                     || '~'
                                     || amount_contained
                                     || '~'
                                     || units
                                     || '~'
                                     || ingredient_details
                                     || ' '
                                     || '|'
                                     || ' '
                                     || LEAD(   a.sku_id
                                             || '~'
                                             || a.ingredient_name
                                             || '~'
                                             || a.rda_percent
                                             || '~'
                                             || amount_contained
                                             || '~'
                                             || units
                                             || '~'
                                             || ingredient_details
                                            ) OVER(ORDER BY a.sku_id)
                             ELSE    a.sku_id
                                  || '~'
                                  || a.ingredient_name
                                  || '~'
                                  || a.rda_percent
                                  || '~'
                                  || amount_contained
                                  || '~'
                                  || units
                                  || '~'
                                  || ingredient_details
                          END
                         )
               END AS RESULT
        FROM   sku_facts a)

[Updated on: Tue, 13 November 2007 06:56] by Moderator

Report message to a moderator

Re: Help with joins [message #280432 is a reply to message #280364] Tue, 13 November 2007 09:40 Go to previous messageGo to next message
jordan_dba
Messages: 19
Registered: May 2007
Location: SF, CA
Junior Member
Thank you very much for your response. The code you sent comes close but it still returns the sku-id's on multiple lines. I made some modifications after reading up on the lead function but it still doesn't do what I need. I also read up on Pivoting and to no avail. I really appreciate the effort.
I am going to send an email to the developer to see if his program can parse the data if I use the collect function.
Re: Help with joins [message #280519 is a reply to message #280432] Tue, 13 November 2007 23:41 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
u can go thru this link
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
Re: Help with joins [message #280542 is a reply to message #280519] Wed, 14 November 2007 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use IM speak.

Regards
Michel
Re: Help with joins [message #280652 is a reply to message #280542] Wed, 14 November 2007 05:41 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
Hi Michel, if u don't mine may i know why did you mention that "Don't use IM speak".what does it mean....?

Hi first create the function as follows...
create or replace function sku_fun(sid in sku_facts.sku_id%type) return clob
is
l_text clob;
l_count number;
begin
select count(*) into l_count from sku_facts ;
for i in (select * from sku_facts where sku_id=sid) loop
l_text := l_text || '|' || i.sku_id||'~'||i.ingredient_name||'~'||i.rda_percent
||'~'||i.amount_contained||'~'||i.units||'~'||i.ingredient_details;
l_text := ltrim(l_text,'|');
end loop;
return l_text;
end;


Then execute the Query

select sku_fun(sku_id) from sku_facts group by sku_id;

[Updated on: Wed, 14 November 2007 05:54]

Report message to a moderator

Re: Help with joins [message #280692 is a reply to message #280652] Wed, 14 November 2007 09:23 Go to previous messageGo to next message
jordan_dba
Messages: 19
Registered: May 2007
Location: SF, CA
Junior Member
This is what I get from the string Aggregate Func.

Row# SKU_FUN(SKU_ID)
1 (ORACLOB)
2 (ORACLOB)



What I am trying to achieve is

SS-1004~Vitamin C~8.33~500~Mg~(As Sodium Ascorbate)
SS-1005~Rose Hips~0~25~Mg~Rosa | SS-1005~Vitamin D~6~75~Mg~Ascorbic Acid
SS-1006~Vitamin C~8.33~500~Mg~(As Sodium Ascorbate)| SS-1005~Vitamin D~6~75~Mg~Ascorbic Acid |~Rose Hips~0~25~Mg~Rosa



something like that.
Re: Help with joins [message #280717 is a reply to message #280652] Wed, 14 November 2007 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Hi Michel, if u don't mine may i know why did you mention that "Don't use IM speak".what does it mean....?

Read OraFAQ Forum Guide

Regards
Michel
Re: Help with joins [message #280754 is a reply to message #280652] Wed, 14 November 2007 11:58 Go to previous messageGo to next message
jordan_dba
Messages: 19
Registered: May 2007
Location: SF, CA
Junior Member
Thank you very much Ramya... this works exactly the way I want it to. I really appreciate your help with this.
Re: Help with joins [message #280839 is a reply to message #280119] Thu, 15 November 2007 00:09 Go to previous message
pengamen2007
Messages: 3
Registered: November 2007
Junior Member
Have You ever Tried To Join Them By Join Using? if you have.. would you like you to give its syntax..? Confused
Previous Topic: Hierarchy Builder SQL (merged 2 cross-posts)
Next Topic: query Hierarchical data
Goto Forum:
  


Current Time: Sun Dec 11 04:39:08 CST 2016

Total time taken to generate the page: 0.09253 seconds