Home » SQL & PL/SQL » SQL & PL/SQL » sql to group by schema (oracle 10g)
sql to group by schema [message #316068] Wed, 23 April 2008 11:26 Go to next message
brandond
Messages: 20
Registered: March 2008
Junior Member
I have this query

SELECT DISTINCT schema.column_name FROM table_name
UNION
SELECT DISTINCT schema2.column_name FROM table_name
UNION
SELECT DISTINCT schema3.column_name FROM table_name


However I need to display which schema that each column_name is coming from.

I thought I could do a , as "Schema" in my sql, but that only adds another column to my output, and doesn't display any information. Would it be possible to add to this query so that my output looks like this

column_name    schema
value           Schema 1
value           Schema 1
value           Schema 2
value           Schema 3

Thanks
Re: sql to group by schema [message #316075 is a reply to message #316068] Wed, 23 April 2008 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ DISTINCT is useless with UNION. UNION implies DISTINCT
2/ You don't qualify a column name with a schema, you qualify a TABLE with a schema
3/ Add a column with the schema name in each select
4/ Put your statement between code tags when you post

Regards
Michel
Re: sql to group by schema [message #316091 is a reply to message #316075] Wed, 23 April 2008 13:43 Go to previous messageGo to next message
brandond
Messages: 20
Registered: March 2008
Junior Member
I'm not sure what you mean by putting a schema name in each select

I've tried this

SELECT   ALF_EAM_ASSETS.MFG_NO as ALF, NULL AS BRF, NULL AS COF, NULL AS COO, NULL AS CUF, NULL AS FMM, NULL AS GAF, NULL AS JOF, NULL AS JSF, NULL AS KIF, NULL AS PAF, NULL AS PSS, NULL AS RSO, NULL AS SHF, NULL AS TPS, NULL AS WCF FROM ALF_EAM_ASSETS 
UNION
SELECT NULL AS ALF, BRF_EAM_ASSETS.MFG_NO AS BRF, NULL AS COF, NULL AS COO, NULL AS CUF, NULL AS FMM, NULL AS GAF, NULL AS JOF, NULL AS JSF, NULL AS KIF, NULL AS PAF, NULL AS PSS, NULL AS RSO, NULL AS SHF, NULL AS TPS, NULL AS WCF FROM BRF_EAM_ASSETS 
UNION
SELECT   NULL AS ALF, NULL AS BRF, COF_EAM_ASSETS.MFG_NO AS COF, NULL AS COO, NULL AS CUF, NULL AS FMM, NULL AS GAF, NULL AS JOF, NULL AS JSF, NULL AS KIF, NULL AS PAF, NULL AS PSS, NULL AS RSO, NULL AS SHF, NULL AS TPS, NULL AS WCF  FROM COF_EAM_ASSETS 
UNION
SELECT   NULL AS ALF, NULL AS BRF, NULL AS COF, COO_EAM_ASSETS.MFG_NO AS COO, NULL AS CUF, NULL AS FMM, NULL AS GAF, NULL AS JOF, NULL AS JSF, NULL AS KIF, NULL AS PAF, NULL AS PSS, NULL AS RSO, NULL AS SHF, NULL AS TPS, NULL AS WCF FROM COO_EAM_ASSETS
UNION
etc..
etc..


but all that does is put the information in seperate columns, but I would like to have all the column information from each schema in 1 column, with a column beside it with information in it stating what schema it was from. something like

mfg_no    schema
value      alf
value      alf
value      brf
value      cof
value      cof
etc...
Re: sql to group by schema [message #316093 is a reply to message #316091] Wed, 23 April 2008 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I should add (but this is in the guide):
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

I can't read lines of 300 characters.

Regards
Michel
Re: sql to group by schema [message #316094 is a reply to message #316068] Wed, 23 April 2008 14:08 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> However I need to display which schema that each column_name is coming from.

As the 'schema' is the same (constant) in each subquery, you may add it into all subqueries as a constant column.
And, as UNION tries to remove duplicates and you will not have any, it will be better to use UNION ALL.
Consult an article about The UNION [ALL], INTERSECT, MINUS Operators in the documentation.
Re: sql to group by schema [message #316098 is a reply to message #316068] Wed, 23 April 2008 14:31 Go to previous messageGo to next message
brandond
Messages: 20
Registered: March 2008
Junior Member
I was able to figure out my answer

not sure why I missed this solution

select x, "schema" from table_name
union
select x, "schema" from table_name

sorry about the huge lines of code
Re: sql to group by schema [message #316100 is a reply to message #316098] Wed, 23 April 2008 14:39 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good, this was my point 3.

Regards
Michel
Previous Topic: Birth Date Range
Next Topic: VERY VERY COMPLEX QUERY
Goto Forum:
  


Current Time: Sat Dec 10 20:54:01 CST 2016

Total time taken to generate the page: 0.41967 seconds