Select from select with case statement and union all
When I am executing query
SELECT Settlement_Fees.Participant_Name, Settlement_Fees.Account, Settlement_Fees.Billing_Account, Settlement_Fees.Descr1, Settlement_Fees.Market, Settlement_Fees.Instrum
--, IIf(Settlement_Fees.Instr_Type='Internal','bsinternal',Settlement_Fees.Instr_Type) AS Expr1
,(case when Settlement_Fees.Instr_Type='Internal' then 'bsinternal'
else Settlement_Fees.Instr_Type
end )
, Settlement_Fees.Country, Settlement_Fees.Nr_Instr_Business_Unit, Settlement_Fees.Nr_Instr_Account, Settlement_Fees.Avg_EUR_Rate, Settlement_Fees.Fee_Amount_EUR, Settlement_Fees.Value_Date_Adj
FROM Settlement_Fees)
union all
(select '','',Billing_Account,'','','',
( case when Instr_Type like '%Bridge%' or Instr_Type = '%Internal%' then 'Bszridge/Internal'
else Instr_Type
end )
,'','',Nr_Instr_Account ,'',Fe开发者_如何转开发e_Amount_EUR ,''
from Settlement_Fees group by Settlement_Fees.Billing_Account,Settlement_Fees.Instr_Type
,Settlement_Fees.Nr_Instr_Account,Fee_Amount_EUR)
union all
(select '','',Billing_Account,'','','','Total','','',sum(Nr_Instr_Account),'',sum(Fee_Amount_EUR) ,''
from Settlement_Fees group by Billing_Account
Its is working fine
but when i am executin with select * from () its giving me error "Msg 102, Level 15, State 1, Line 33 Incorrect syntax near ')'." for the following query
SELECT *
FROM ((SELECT settlement_fees.participant_name,
settlement_fees.ACCOUNT,
settlement_fees.billing_account,
settlement_fees.descr1,
settlement_fees.market,
settlement_fees.instrum
--, IIf(Settlement_Fees.Instr_Type='Internal','bsinternal',Settlement_Fees.Instr_Type) AS Expr1
,
( CASE
WHEN settlement_fees.instr_type = 'Internal' THEN
'bsinternal'
ELSE settlement_fees.instr_type
END ),
settlement_fees.country,
settlement_fees.nr_instr_business_unit,
settlement_fees.nr_instr_account,
settlement_fees.avg_eur_rate,
settlement_fees.fee_amount_eur,
settlement_fees.value_date_adj
FROM settlement_fees)
UNION ALL
(SELECT '',
'',
billing_account,
'',
'',
'',
( CASE
WHEN instr_type LIKE '%Bridge%'
OR instr_type = '%Internal%' THEN 'Bszridge/Internal'
ELSE instr_type
END ),
'',
'',
nr_instr_account,
'',
fee_amount_eur,
''
FROM settlement_fees
GROUP BY settlement_fees.billing_account,
settlement_fees.instr_type,
settlement_fees.nr_instr_account,
fee_amount_eur)
UNION ALL
(SELECT '',
'',
billing_account,
'',
'',
'',
'Total',
'',
'',
SUM(nr_instr_account),
'',
SUM(fee_amount_eur),
''
FROM settlement_fees
GROUP BY billing_account))
You shoud name your subquery:
Select * from () subqueryName
select * from(
(SELECT Settlement_Fees.Participant_Name, Settlement_Fees.Account, Settlement_Fees.Billing_Account, Settlement_Fees.Descr1, Settlement_Fees.Market, Settlement_Fees.Instrum
--, IIf(Settlement_Fees.Instr_Type='Internal','bsinternal',Settlement_Fees.Instr_Type) AS Expr1
,(case when Settlement_Fees.Instr_Type='Internal' then 'bsinternal'
else Settlement_Fees.Instr_Type
end )
, Settlement_Fees.Country, Settlement_Fees.Nr_Instr_Business_Unit, Settlement_Fees.Nr_Instr_Account, Settlement_Fees.Avg_EUR_Rate, Settlement_Fees.Fee_Amount_EUR, Settlement_Fees.Value_Date_Adj
FROM Settlement_Fees)
union all
(select '','',Billing_Account,'','','',
( case when Instr_Type like '%Bridge%' or Instr_Type = '%Internal%' then 'Bszridge/Internal'
else Instr_Type
end )
,'','',Nr_Instr_Account ,'',Fee_Amount_EUR ,''
from Settlement_Fees group by Settlement_Fees.Billing_Account,Settlement_Fees.Instr_Type
,Settlement_Fees.Nr_Instr_Account,Fee_Amount_EUR)
union all
(select '','',Billing_Account,'','','','Total','','',sum(Nr_Instr_Account),'',sum(Fee_Amount_EUR) ,''
from Settlement_Fees group by Billing_Account
)) subqueryName
You need to give a table alias to the derived table. e.g. add as t
to the very end of the query.
Additionally to fix the issue raised in the comments change
( CASE
WHEN settlement_fees.instr_type = 'Internal' THEN
'bsinternal'
ELSE settlement_fees.instr_type
END )
to
CASE
WHEN settlement_fees.instr_type = 'Internal' THEN
'bsinternal'
ELSE settlement_fees.instr_type
END AS Foo
You need to give your derived table a name. Doesn't matter what. For example:
SELECT
...
FROM
(...derived table) myDerivedTableName
You have an extra set of ()
and aliasing your inline view/derived table also helps e.g.
select * from(
SELECT
Settlement_Fees.Participant_Name,
Settlement_Fees.Account,
Settlement_Fees.Billing_Account,
Settlement_Fees.Descr1,
Settlement_Fees.Market,
Settlement_Fees.Instrum,
(case when Settlement_Fees.Instr_Type='Internal' then 'bsinternal'
else Settlement_Fees.Instr_Type
end ) as SomthingHere,
Settlement_Fees.Country,
Settlement_Fees.Nr_Instr_Business_Unit,
Settlement_Fees.Nr_Instr_Account,
Settlement_Fees.Avg_EUR_Rate,
Settlement_Fees.Fee_Amount_EUR,
Settlement_Fees.Value_Date_Adj
FROM Settlement_Fees
UNION ALL select
'',
'',
Billing_Account,
'',
'',
'',
( case when Instr_Type like '%Bridge%' or Instr_Type = '%Internal%' then 'Bszridge/Internal'
else Instr_Type
end ),
'',
'',
Nr_Instr_Account ,
'',
Fee_Amount_EUR ,
''
from
Settlement_Fees
group by
Settlement_Fees.Billing_Account,
Settlement_Fees.Instr_Type,
Settlement_Fees.Nr_Instr_Account,
Fee_Amount_EUR
union all select
'',
'',
Billing_Account,
'',
'',
'',
'Total',
'',
'',
sum(Nr_Instr_Account),
'',
sum(Fee_Amount_EUR) ,
''
from
Settlement_Fees
group by
Billing_Account
) as foo
精彩评论