Solved

SQL Query syntax question

Posted on 2012-03-14
3
213 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
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.

726 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