Solved

SQL Query syntax question

Posted on 2012-03-14
3
209 Views
Last Modified: 2012-06-27
Hello all,

I have the following query and what I need to also get in the top SELECT results is a COUNT of the total "column_name for a particular source_id" in the table data_source_detail you see here in the query which exists in the nested query with UNION.  The UNION is getting a distinct list for me of the Source and Column Name so I only want the count number to be from the DSD Data_Source_Detail table records, I don't want it to add the column row from the Default_Source_Mapping table.  I end up passing in a source id to the params so not sure if I need that in the query or not.  Hope this makes sense.

SELECT SM.ID AS SM_ID, X.SOURCE_ID AS SM_SOURCE_ID, X.COLUMN_NAME AS SM_COLUMN_NAME,
                                                                 SM.OBJECT_MAP_NAME AS SM_OBJECT_MAP_NAME, SM.UNIT_COLUMN AS SM_UNIT_COLUMN,
                                                                 SM.VARIABLE_ID AS SM_VARIABLE_ID, SM.DATA_FORMAT_ID AS SM_DATA_FORMAT_ID,
                                                                 OM.NAME AS OM_NAME, OM.OBJECT_TYPE AS OM_OBJECT_TYPE, OM.DISPLAY_NAME AS OM_DISPLAY_NAME,
                                                                 DF.ID AS DF_ID, DF.FORMAT AS DF_FORMAT, CDIP.EXTERNAL_REF_MAP_MGMT.FORMAT_APPVD( DF.ID ) DF_TOTAL_APPROVED,
                                                                 DT.ID AS DT_ID, DT.NAME AS DT_NAME, DT.ORACLE_NAME AS DT_ORACLE_NAME
                                                       FROM CDIP.SV_DEFAULT_SOURCE_MAPPING SM
                                                            LEFT JOIN CDIP.SV_DATA_FORMAT DF ON (SM.DATA_FORMAT_ID = DF.ID)
                                                            LEFT JOIN CDIP.SV_DATA_TYPE DT ON (DF.DATA_TYPE_ID = DT.ID)
                                                            LEFT JOIN CDIP.SV_OBJECT_MAPPING OM ON (SM.OBJECT_MAP_NAME = OM.NAME)
                                                            RIGHT JOIN (SELECT DS.SOURCE_ID, DSD.COLUMN_NAME
                                                            FROM CDIP.SV_DATA_SOURCE DS INNER JOIN CDIP.SV_DATA_SOURCE_DETAIL DSD
                                                            ON DS.ID = DSD.DATA_SOURCE_ID WHERE DSD.VC_FLAG = 0  UNION
                                                            SELECT SOURCE_ID, COLUMN_NAME
                                                            FROM CDIP.SV_DEFAULT_SOURCE_MAPPING) X
                                                            ON X.SOURCE_ID = SM.SOURCE_ID
                                                            AND X.COLUMN_NAME = SM.COLUMN_NAME
                                                            WHERE X.SOURCE_ID = 10060
0
Comment
Question by:sbornstein2
3 Comments
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 37723545
your query will not work, if you are using union operator must have equal number of expression in all select statement
eg.
Select col1, col2 from TableName
union
Select col1 from TableName

Open in new window

gives error

select statment should have same number of columns to use union

Select col1, col2 from TableName
union
Select col1,'' from TableName

Open in new window

0
 
LVL 9

Accepted Solution

by:
macarrillo1 earned 200 total points
ID: 37724813
I am not sure I fully understand what you are trying to do.  From what you said it sounds like you are trying to add a Count by Column name for a specific Source_ID to the top query.  If this is the case you could use a sub query to accomplish this.

(SELECT COLUMN_NAME,  Count(*)  as Ct
FROM CDIP.SV_DEFAULT_SOURCE_MAPPING) X
Inner Join CDIP.SV_DEFAULT_SOURCE_MAPPING SM
ON X.SOURCE_ID = SM.SOURCE_ID AND X.COLUMN_NAME = SM.COLUMN_NAME
WHERE X.SOURCE_ID = 10060
Group By COLUMN_NAME) SQ

Then join this to your upper query as a table and reference the columns as SQ.COLUMN_NAME and SQ.Ct

So your join would look something like this:

CDIP.SV_DEFAULT_SOURCE_MAPPING SM INNER JOIN

(SELECT COLUMN_NAME,  Count(*)  as Ct
FROM CDIP.SV_DEFAULT_SOURCE_MAPPING) X
Inner Join CDIP.SV_DEFAULT_SOURCE_MAPPING SM
ON X.SOURCE_ID = SM.SOURCE_ID AND X.COLUMN_NAME = SM.COLUMN_NAME
WHERE X.SOURCE_ID = 10060
Group By COLUMN_NAME) SQ

on SM.COLUMN_NAME=SQ.COLUMN_NAME
0
 

Author Closing Comment

by:sbornstein2
ID: 37747819
this was it thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

757 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

17 Experts available now in Live!

Get 1:1 Help Now