Aliasing in SQL Server

Question

I wrote this query in Oracle and want to compile it as well on SQL Server:

SELECT DISTINCT 
    HOLDER_CODE, CALCULATED_AMOUNT
FROM 
    (SELECT 
         DA.HOLDER_CODE, 
         ROUND(FAB.AMOUNT * FCE.EXCHANGE_RATE,0) AS CALCULATED_AMOUNT, 
         FAB.BALANCE_DATE_ID as FAB_DATE,
         MAX(FAB.BALANCE_DATE_ID) OVER (PARTITION BY DA.HOLDER_CODE) as MAX_DATE_BALANCE
     FROM 
         DIM_ACCOUNT DA 
     JOIN 
         FACT_AS_BALANCE FAB ON FAB.ACCOUNT_ID  = DA.ID
     JOIN 
         DIM_AS_CHARACTERISTICS DAC ON DAC.ID = FAB.BALANCE_TYPE_ID
     LEFT JOIN 
         FACT_CURRENCY_EXCHANGE FCE ON FCE.FROM_CURRENCY_ID = FAB.CURRENCY_ID
     WHERE 
         DAC.BALANCE_CLOSING_FLAG = 'Y' 
         AND TO_CURRENCY_ID = (SELECT DC.ID  
                               FROM DIM_CURRENCY DC
                               WHERE DC.IS_DEFAULT_CURRENCY = 'Y')
         AND FAB.AMOUNT > 0)
WHERE 
    FAB_DATE = MAX_DATE_BALANCE
ORDER BY 
    CALCULATED_AMOUNT DESC;

But when I run it I get the following exception:

Error: Incorrect syntax near the keyword 'WHERE'. SQLState: S0001 ErrorCode: 156

So I guess, it's the last where clause, which indicates this. What is wrong and what should it be?

Just give a alias to your big sub query select distinct ... from ( ... ) as K where K.FAB_DATE ... — narendra
– narendra, Commented Oct 13, 2015 at 14:09

Jamiec · Accepted Answer · 2015-10-13 14:18:56Z

11

You just need to alias the subquery

SELECT DISTINCT HOLDER_CODE, CALCULATED_AMOUNT
FROM (
  SELECT DA.HOLDER_CODE, 
         //// snip subquery
    AND FAB.AMOUNT > 0
 ) alias_name_here //<--- here
WHERE FAB_DATE = MAX_DATE_BALANCE
ORDER BY CALCULATED_AMOUNT DESC;

You can use as alias_name_here or just alias_name_here - SQL Server allows either syntax.

edited Oct 13, 2015 at 14:18

answered Oct 13, 2015 at 14:07

Jamiec

136k15 gold badges143 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mattia Caputo · Accepted Answer · 2015-10-13 14:07:49Z

you must give a name at your "table"
i give is with "as T"

SELECT DISTINCT HOLDER_CODE, CALCULATED_AMOUNT
FROM (
  SELECT DA.HOLDER_CODE, 
         ROUND(FAB.AMOUNT * FCE.EXCHANGE_RATE,0) AS CALCULATED_AMOUNT, 
         FAB.BALANCE_DATE_ID as FAB_DATE,
         MAX(FAB.BALANCE_DATE_ID) OVER (PARTITION BY DA.HOLDER_CODE) as MAX_DATE_BALANCE
  FROM DIM_ACCOUNT DA 
        JOIN FACT_AS_BALANCE FAB ON FAB.ACCOUNT_ID  = DA.ID
        JOIN DIM_AS_CHARACTERISTICS DAC ON DAC.ID = FAB.BALANCE_TYPE_ID
        LEFT JOIN FACT_CURRENCY_EXCHANGE FCE ON FCE.FROM_CURRENCY_ID = FAB.CURRENCY_ID
  WHERE DAC.BALANCE_CLOSING_FLAG = 'Y' 
  AND TO_CURRENCY_ID = ( 
SELECT DC.ID FROM DIM_CURRENCY DC
WHERE DC.IS_DEFAULT_CURRENCY = 'Y')
    AND FAB.AMOUNT > 0
 ) as T
WHERE FAB_DATE = MAX_DATE_BALANCE
ORDER BY CALCULATED_AMOUNT DESC;

Collectives™ on Stack Overflow

Aliasing in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related