SQLTeam.com | Weblogs | Forums

Going crazy over whats wrong with my syntax!


#1

I have almost completely finished the troubleshooting with my very confusing query that has multiple nested selects....however, I have been stuck for awhile on an issue I cannot seem to figure out.

The error is the following:

All queries in a SQL statement containing set operators must have an equal number of expressions in their target lists.

I know this typically means that columns within a select inside of a union don't match the select that goes with it, but I don't see that to be true, but it is a pretty confusing query.

Here is my query:

DECLARE @compdt DATETIME

SET @compdt = dateadd(dd,-5,getdate())

SELECT
'File_Name' = name_file,
'File_Date' = dt_rcvd,
--'Instrument_Type' = case when cd_instmt_type is null then 'Not Available' else cd_instmt_type end,
'Id_Cusip' = cusip,
'Error_Description' = rtrim(desc_err) +
case when name_file not like 'RATINGS_main%' then ' || ' + rtrim(text_tag) else null end
FROM
(
SELECT fs.dt_rcvd,
fs.name_file,
ies.cd_intrfc_err_type,
ies.cd_err_lvl,
ies.desc_err,
substring (ies.id_rec,7,9) as cusip,
ies.text_tag,
--i.cd_instmt_type,
--i.cd_aldn_secr_grp,
--i.cd_aldn_secr_type,
fs.id_file,
ies.id_err
FROM (intrfc_err_stat AS ies INNER JOIN
(select ifs.id_file,
ifs.name_file,
ifs.dt_rcvd
from intrfc_file_stat ifs
where ifs.dt_rcvd >= @compdt
and ifs.name_srce_syst = 'Aladdin'
and ifs.name_file not like '%staging%'
union
select ifs.id_file,
ifs.name_file,
ifs.dt_rcvd
from intrfc_file_stat AS ifs
where ifs.dt_rcvd >= @compdt
and ifs.name_srce_syst = 'Aladdin')
AS fs ON ies.id_file = fs.id_file) LEFT JOIN instmt AS i ON substring(ies.id_rec,7,9) = i.id_aldn_cusip
WHERE ies.cd_err_lvl='C1'
--AND (i.cd_instmt_type is null or i.cd_instmt_type <> 'PRICING CUSIP')
AND ies.desc_err not like 'Required field is null! Tag Name=ACCRUAL_DT, Record ID=FG%'
AND ies.desc_err not like 'No instmt record exists for nbr_cusip=F%(TSMBSARM 17 )'
AND ies.desc_err <> 'TRAN_TYPE is not valid, can not load to trd table. TRAN_TYPE = CPLG (TRANSACTION1232)'
AND fs.name_file not like 'FMTAG%anal%'
AND fs.name_file not like '%SYNDICATE%'
AND lower(fs.name_file) not like '%economy%'
AND fs.name_file not like '%MARK_SOURCE%'
AND fs.name_file not like '%ACTUAL_PAYMENT%'
AND fs.name_file not like '%ISSUERS%'
AND ies.desc_err not like 'TRD_COMMISSION tag is null or 0 when instmt product type is DISCO%'
AND case when fs.name_file like 'RATINGS_main%' and ies.desc_err like '%cusip = B%' then 1 else 0 end <> 1
UNION select fss.dt_rcvd,
fss.name_file,null,null,
'Expctd = ' + convert(varchar,(fss.cnt_expctd_trans))
+ ' ; Actl = ' + convert(varchar,(fss.cnt_actl_trans))
+ ' ; Accptd = ' + convert(varchar,(fss.cnt_accptd_trans))
+ ' ; Sts = ' + fss.cd_sts,
null,null,null,null,null,fss.id_file,1
from
(
select ifs.id_file,
ifs.name_file,
ifs.dt_rcvd,
ifs.cd_sts,
ifs.cnt_expctd_trans,
ifs.cnt_actl_trans,
ifs.cnt_accptd_trans,
ifs.desc_act_perfd
from intrfc_file_stat AS ifs
where ifs.dt_rcvd >= @compdt
and ifs.name_srce_syst = 'Aladdin'
--and (ifs.name_file not like '%staging%' or (ifs.name_file LIKE '%staging%' and ifs.desc_act_perfd like 'main_%' and ifs.name_file not like '%adhoc_main%'))
and (ifs.name_file not like '%staging%' or (ifs.name_file LIKE '%staging%' and ifs.name_file not like '%adhoc_main%'))
) as fss
--where case
-- when (fss.name_file like '%staging%' and fss.cnt_accptd_trans <> 25) then 0
-- when (fss.cd_sts = 'F' and fss.cnt_expctd_trans = fss.cnt_actl_trans and lower(fss.name_file) like '%economy%') then 1
-- when (fss.cd_sts = 'F' and fss.cnt_expctd_trans = fss.cnt_actl_trans and fss.name_file like 'RESTRICTED_ASSETS%') then 1
-- when (fss.cd_sts = 'F' and fss.cnt_expctd_trans = fss.cnt_actl_trans and fss.name_file like 'MARK_SOURCE%') then 1
-- when fss.cd_sts = 'F' then 0
-- else 1
-- end = 0
) sq

The '--'s you see above are some lines I commented out trying to get the query to work.

Thanks for your help!


#3

The one union has twelve columns but with the commented out it does not match.

Break the code up into small parts and build from that.


#4

I have been trying to do that for the last few hours.

Could you please help me with which ones don't match?


#5

Here is a portion of the code around the UNION:

UNION select fss.dt_rcvd,
fss.name_file,null,null,
'Expctd = ' + convert(varchar,(fss.cnt_expctd_trans))
+ ' ; Actl = ' + convert(varchar,(fss.cnt_actl_trans))
+ ' ; Accptd = ' + convert(varchar,(fss.cnt_accptd_trans))
+ ' ; Sts = ' + fss.cd_sts,
null,null,null,null,null,fss.id_file,1
from
(
select ifs.id_file,
ifs.name_file,
ifs.dt_rcvd,
ifs.cd_sts,
ifs.cnt_expctd_trans,
ifs.cnt_actl_trans,
ifs.cnt_accptd_trans,
ifs.desc_act_perfd
from intrfc_file_stat ifs
where ifs.dt_rcvd >= @compdt
and ifs.name_srce_syst = 'Aladdin'
and (ifs.name_file not like '%staging%' or (ifs.name_file LIKE '%staging%' and ifs.desc_act_perfd like 'main_%' and ifs.name_file not like '%adhoc_main%'))
) as fss
where case
when (fss.name_file like '%staging%' and fss.cnt_accptd_trans <> 25) then 0
when (fss.cd_sts = 'F' and fss.cnt_expctd_trans = fss.cnt_actl_trans and lower(fss.name_file) like '%economy%') then 1
when (fss.cd_sts = 'F' and fss.cnt_expctd_trans = fss.cnt_actl_trans and fss.name_file like 'RESTRICTED_ASSETS%') then 1
when (fss.cd_sts = 'F' and fss.cnt_expctd_trans = fss.cnt_actl_trans and fss.name_file like 'MARK_SOURCE%') then 1
when fss.cd_sts = 'F' then 0
else 1
end = 0
) sq
order by name_file, cusip

Is the select statement under the union the one that it isn't thinking that matches column-wise? I cannot see the issue..


#6

See comments:

DECLARE @compdt DATETIME

SET @compdt = dateadd(dd,-5,getdate())

SELECT 'File_Name' = name_file,
       'File_Date' = dt_rcvd,
--       'Instrument_Type' = case when cd_instmt_type is null then 'Not Available' else cd_instmt_type end,
       'Id_Cusip' = cusip,
       'Error_Description' = rtrim(desc_err)
                           + case when name_file not like 'RATINGS_main%' then ' || ' + rtrim(text_tag) else null end
  FROM (SELECT fs.dt_rcvd,
               fs.name_file,
               ies.cd_intrfc_err_type,
               ies.cd_err_lvl,
               ies.desc_err,
               substring (ies.id_rec,7,9) as cusip,
               ies.text_tag,
--               i.cd_instmt_type,
--               i.cd_aldn_secr_grp,
--               i.cd_aldn_secr_type,
               fs.id_file,
               ies.id_err
          FROM (intrfc_err_stat AS ies
                INNER JOIN (select ifs.id_file,
                                   ifs.name_file,
                                   ifs.dt_rcvd
                              from intrfc_file_stat ifs
                             where ifs.dt_rcvd >= @compdt
                               and ifs.name_srce_syst = 'Aladdin'
                               and ifs.name_file not like '%staging%'
                            union
                            select ifs.id_file,
                                   ifs.name_file,
                                   ifs.dt_rcvd
                              from intrfc_file_stat AS ifs
                             where ifs.dt_rcvd >= @compdt
                               and ifs.name_srce_syst = 'Aladdin'
                           ) AS fs
                        ON ies.id_file = fs.id_file
               )
               LEFT JOIN instmt AS i
                      ON substring(ies.id_rec,7,9) = i.id_aldn_cusip
         WHERE ies.cd_err_lvl='C1'
--           AND (i.cd_instmt_type is null or i.cd_instmt_type <> 'PRICING CUSIP')
           AND ies.desc_err not like 'Required field is null! Tag Name=ACCRUAL_DT, Record ID=FG%'
           AND ies.desc_err not like 'No instmt record exists for nbr_cusip=F%(TSMBSARM 17 )'
           AND ies.desc_err <> 'TRAN_TYPE is not valid, can not load to trd table. TRAN_TYPE = CPLG (TRANSACTION1232)'
           AND fs.name_file not like 'FMTAG%anal%'
           AND fs.name_file not like '%SYNDICATE%'
           AND lower(fs.name_file) not like '%economy%'
           AND fs.name_file not like '%MARK_SOURCE%'
           AND fs.name_file not like '%ACTUAL_PAYMENT%'
           AND fs.name_file not like '%ISSUERS%'
           AND ies.desc_err not like 'TRD_COMMISSION tag is null or 0 when instmt product type is DISCO%'
           AND case when fs.name_file like 'RATINGS_main%' and ies.desc_err like '%cusip = B%' then 1 else 0 end <> 1
        UNION
        select fss.dt_rcvd,
               fss.name_file,
               null, /* cd_intrfc_err_type */
               null, /* cd_err_lvl */
               'Expctd = ' + convert(varchar,(fss.cnt_expctd_trans))
              +' ; Actl = ' + convert(varchar,(fss.cnt_actl_trans))
              +' ; Accptd = ' + convert(varchar,(fss.cnt_accptd_trans))
              +' ; Sts = ' + fss.cd_sts,
               null, /* cusip */
               null, /* text_tag */
--               null, /* This field is commented out in select above (cd_instmt_type) */
--               null, /* This field is commented out in select above (cd_aldn_secr_grp) */
--               null, /* This field is commented out in select above (cd_aldn_secr_type) */
               fss.id_file,
               1
          from (select ifs.id_file,
                       ifs.name_file,
                       ifs.dt_rcvd,
                       ifs.cd_sts,
                       ifs.cnt_expctd_trans,
                       ifs.cnt_actl_trans,
                       ifs.cnt_accptd_trans,
                       ifs.desc_act_perfd
                  from intrfc_file_stat AS ifs
                 where ifs.dt_rcvd >= @compdt
                   and ifs.name_srce_syst = 'Aladdin'
--                   and (ifs.name_file not like '%staging%' or (ifs.name_file LIKE '%staging%' and ifs.desc_act_perfd like 'main_%' and ifs.name_file not like '%adhoc_main%'))
                   and (ifs.name_file not like '%staging%'
                    or  (ifs.name_file LIKE '%staging%'
                   and   ifs.name_file not like '%adhoc_main%'
                       ))
               ) as fss
--       where case
--                when (fss.name_file like '%staging%' and fss.cnt_accptd_trans <> 25) then 0
--                when (fss.cd_sts = 'F' and fss.cnt_expctd_trans = fss.cnt_actl_trans and lower(fss.name_file) like '%economy%') then 1
--                when (fss.cd_sts = 'F' and fss.cnt_expctd_trans = fss.cnt_actl_trans and fss.name_file like 'RESTRICTED_ASSETS%') then 1
--                when (fss.cd_sts = 'F' and fss.cnt_expctd_trans = fss.cnt_actl_trans and fss.name_file like 'MARK_SOURCE%') then 1
--                when fss.cd_sts = 'F' then 0
--                else 1
--             end = 0
       ) sq


#7

I really appreciate that sir!!