SQL Query syntax question

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
sbornstein2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sachinpatil10dCommented:
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
Michael CarrilloInformation Systems ManagerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sbornstein2Author Commented:
this was it thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.