• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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
0
sbornstein2
Asked:
sbornstein2
1 Solution
 
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
 
macarrillo1Commented:
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
 
sbornstein2Author Commented:
this was it thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now