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!