Solved

SQL Query syntax question

Posted on 2012-03-14
3
210 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculated columns 13 61
Alter table 4 23
MS SQL - Conversion failed when converting the varchar value 3 22
Add '#' to end of file 2 29
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

911 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

18 Experts available now in Live!

Get 1:1 Help Now