vbemt
asked on
Oracle returning multiple rows in one line
I am trying to find a way to write a SQL statement that will allow me to do the following:
Field 1 Field 2 Field 3
------- -------- --------
a 1 stuff1, stuff 2, stuff 3
Where "Field 3" is a result of another query.
I tried to write this:
Select Field1, Field2, (Select blah from x where x.item = a.item) as Field3
From a
I get the error code ora-01427 when I attempt this. I do not know the size of the return set as it will vary depending on the query call. I don't want to export this to an Excel spreadsheet, or create a table and dump the contents to it and then query it again. Any and all help would be greatly appreciated.
Thanks in Advance,
James
Field 1 Field 2 Field 3
------- -------- --------
a 1 stuff1, stuff 2, stuff 3
Where "Field 3" is a result of another query.
I tried to write this:
Select Field1, Field2, (Select blah from x where x.item = a.item) as Field3
From a
I get the error code ora-01427 when I attempt this. I do not know the size of the return set as it will vary depending on the query call. I don't want to export this to an Excel spreadsheet, or create a table and dump the contents to it and then query it again. Any and all help would be greatly appreciated.
Thanks in Advance,
James
if field3 comes from a different table then...
SELECT a.field1, a.field2,
LTRIM(EXTRACT(XMLAGG(XMLEL EMENT("V", ',' || x.field3)),
'/V/text()'
),
','
) AS STRING
FROM yourtable a, yourothertable x
WHERE a. item = x.item
GROUP BY a.field1, a.field2;
I do highly recommend getting stragg and installing it.
Makes these types of queries ever so much easier. It's also very efficient.
SELECT a.field1, a.field2,
LTRIM(EXTRACT(XMLAGG(XMLEL
'/V/text()'
),
','
) AS STRING
FROM yourtable a, yourothertable x
WHERE a. item = x.item
GROUP BY a.field1, a.field2;
I do highly recommend getting stragg and installing it.
Makes these types of queries ever so much easier. It's also very efficient.
There is also a hierarchical way to do it too...
SELECT field1,field2 MAX(SUBSTR(SYS_CONNECT_BY_ PATH(table _name, ',') field3
FROM (SELECT a.field1, a.field2, ROW_NUMBER() OVER(ORDER BY x.field3) curr,
ROW_NUMBER() OVER(ORDER BY x.field3) - 1 prev
FROM FROM yourtable a, yourothertable x
WHERE a. item = x.item
)
START WITH curr = 1
CONNECT BY PRIOR curr = prev
SELECT field1,field2 MAX(SUBSTR(SYS_CONNECT_BY_
FROM (SELECT a.field1, a.field2, ROW_NUMBER() OVER(ORDER BY x.field3) curr,
ROW_NUMBER() OVER(ORDER BY x.field3) - 1 prev
FROM FROM yourtable a, yourothertable x
WHERE a. item = x.item
)
START WITH curr = 1
CONNECT BY PRIOR curr = prev
oops, accidentally deleted a few characters...
SELECT field1,field2 MAX(SUBSTR(SYS_CONNECT_BY_ PATH(table _name, ','),2)) field3
FROM (SELECT a.field1, a.field2, ROW_NUMBER() OVER(ORDER BY x.field3) curr,
ROW_NUMBER() OVER(ORDER BY x.field3) - 1 prev
FROM FROM yourtable a, yourothertable x
WHERE a. item = x.item
)
START WITH curr = 1
CONNECT BY PRIOR curr = prev
SELECT field1,field2 MAX(SUBSTR(SYS_CONNECT_BY_
FROM (SELECT a.field1, a.field2, ROW_NUMBER() OVER(ORDER BY x.field3) curr,
ROW_NUMBER() OVER(ORDER BY x.field3) - 1 prev
FROM FROM yourtable a, yourothertable x
WHERE a. item = x.item
)
START WITH curr = 1
CONNECT BY PRIOR curr = prev
ASKER
I've seen comments about Stragg before. I'm trying to find the exact function for it. When I do find it, I execute it so that it's on the database then I can just reference it like it's any other function in a SQL statement?
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
It's in pieces. You have to create an object type and body first, then create the function to use the type.
It's in pieces. You have to create an object type and body first, then create the function to use the type.
ASKER
Where this all stems from is a co-worker of mine, used .net 3.5 and Sql Server to create the output. I noticed that it used the following query to output the data. So I am trying to find the "Oracle" way of doing it. Is Stragg the only way?
select distinct c.TeamID,
e.equipmentid,
e.name24[Name],
e.Equipment,
'|' +
(
select distinct rtrim(ltrim(cast(f2.Functi onID as nvarchar(20)))) + '|'
from equipment e2 inner join param p2 on e2.equpimentid = p2.equipmentid
inner join [function] f2 on p2.functionid = f2.functionid
where e2.equipment = e.equipment
for xml path('')) as [FunctionID]
,(select distinct rtrim(ltrim([code])) + ', '
from equipment e1
inner join param p1 on e1.equipmentid = p1.equipmentid
inner join [function] f1 on p1.functionid = f1.functionid
where e1.equipment = e.equipment
for xml path('')
) as [FC]
from Equipment e left join Points p on e.equipmentid = p.equipmentid
left join TeamAssoc t on e.equpimentid = t.equipmentid
left join Team c on t.Players = c.Players
group by c.Teamid, e.Equpmentid, e.name24, e.Equipment
select distinct c.TeamID,
e.equipmentid,
e.name24[Name],
e.Equipment,
'|' +
(
select distinct rtrim(ltrim(cast(f2.Functi
from equipment e2 inner join param p2 on e2.equpimentid = p2.equipmentid
inner join [function] f2 on p2.functionid = f2.functionid
where e2.equipment = e.equipment
for xml path('')) as [FunctionID]
,(select distinct rtrim(ltrim([code])) + ', '
from equipment e1
inner join param p1 on e1.equipmentid = p1.equipmentid
inner join [function] f1 on p1.functionid = f1.functionid
where e1.equipment = e.equipment
for xml path('')
) as [FC]
from Equipment e left join Points p on e.equipmentid = p.equipmentid
left join TeamAssoc t on e.equpimentid = t.equipmentid
left join Team c on t.Players = c.Players
group by c.Teamid, e.Equpmentid, e.name24, e.Equipment
no, stragg isn't the only way, I posted 2 other methods above.
stragg makes it easier though...
your query would simply be....
SELECT a.field1, a.field2, stragg(x.field3)
FROM yourtable a, yourothertable x
WHERE a. item = x.item
GROUP BY a.field1, a.field2;
stragg makes it easier though...
your query would simply be....
SELECT a.field1, a.field2, stragg(x.field3)
FROM yourtable a, yourothertable x
WHERE a. item = x.item
GROUP BY a.field1, a.field2;
ASKER
Gotcha, I'm just trying to parse thru all the comments on STRAGG and figure out what I need to pull out and how to implement it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the code, got it to work (kind of), now to remove dupilicates and also to sort that string result.
SELECT DISTINCT
field1,
field2,
stragg(field3)
OVER (PARTITION BY field1, field2
ORDER BY field3
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM (SELECT DISTINCT a.field1, a.field2, x.field3
FROM yourtable a, yourothertable x
WHERE a.item = x.item)
field1,
field2,
stragg(field3)
OVER (PARTITION BY field1, field2
ORDER BY field3
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM (SELECT DISTINCT a.field1, a.field2, x.field3
FROM yourtable a, yourothertable x
WHERE a.item = x.item)
ASKER
Okay that works, one last thing (that I know of):
How can I alias that column. Right now it appears as
field1 field2 stragg(field3) OVER (PARTITION BY field1, field2 ORDER BY field3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
How can I alias that column. Right now it appears as
field1 field2 stragg(field3) OVER (PARTITION BY field1, field2 ORDER BY field3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
just put the alias after the expression....
SELECT DISTINCT
field1,
field2,
stragg(field3)
OVER (PARTITION BY field1, field2
ORDER BY field3
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) your_alias
FROM (SELECT DISTINCT a.field1, a.field2, x.field3
FROM yourtable a, yourothertable x
WHERE a.item = x.item)
ASKER
Sometimes I feel stupid..... I put my orignal alias in the wrong place. Thaks for all of your help.
ASKER
It wasn't your fault on east to understand as much as it was Oracle's way of doing things "differently".
don't worry about, we all make mistakes, and if that's as bad as it gets, you're way ahead of the rest of us!
:)
I see this was your first question, welcome to Experts-Exchange!
:)
I see this was your first question, welcome to Experts-Exchange!
ASKER
Thanks for the help before, I was wondering if you can answer one more question about STRAGG. Will it work if it's on just one table?
Example
Select x, y, stragg(z)
From my_table
Or do I need to do something special for it. I am getting an ORA-00937: not a single-group group function when I attempt this.
Example
Select x, y, stragg(z)
From my_table
Or do I need to do something special for it. I am getting an ORA-00937: not a single-group group function when I attempt this.
stragg is just like any other aggregate function, you need to have group by.
or you may use it as an analytic too
thing about it like SUM for strings.
Select x, y, stragg(z)
From my_table
group by x,y
or you may use it as an analytic too
thing about it like SUM for strings.
Select x, y, stragg(z)
From my_table
group by x,y
LTRIM(EXTRACT(XMLAGG(XMLEL
'/V/text()'
),
','
) AS STRING
FROM yourtable
GROUP BY field1,field2;
or go to asktom.oracle.com and search for STRAGG