Each calculated column must have an explicit name.

system · October 19, 2006, 9:35pm

I am having an issue creating a derived table. The query I am using works in SQL Server Query Analyzer and each column has an explicit name. My query is as follows:
Select
HBS_Response.HBS_Id,
SUM(HBS_Response.Score) Total_Score,
(Case When SUM(HBS_Response.Score) Is Not Null And SUM(HBS_Response.Score)= 50 Then 1 else 0 End) Evangelical,
(Case When SUM(HBS_Response.Score) Is Not Null And SUM(HBS_Response.Score)<> 50 Then 1 else 0 End) Non_Evangelical
From
HBS_Question,
HBS_Response
Where
HBS_Question.Q_Id = HBS_Response.Q_Id
AND HBS_Question.Q_Number In (‘1’,‘2’,‘3’,‘4’,‘5’)
Group By HBS_Response.HBS_id

When I use this same query in Designer, creating a derived table, I get “Each calculated column must have an explicit name” as an error. Does anyone know why?

mcliffordgoo (BOB member since 2003-02-13)

system · October 19, 2006, 9:50pm

Try this one:


Select
HBS_Response.HBS_Id,
SUM(HBS_Response.Score) AS Total_Score,
(Case When SUM(HBS_Response.Score) Is Not Null And SUM(HBS_Response.Score)= 50 Then 1 else 0 End) AS Evangelical,
(Case When SUM(HBS_Response.Score) Is Not Null And SUM(HBS_Response.Score)<> 50 Then 1 else 0 End) AS Non_Evangelical
From
HBS_Question,
HBS_Response
Where
HBS_Question.Q_Id = HBS_Response.Q_Id
AND HBS_Question.Q_Number In ('1','2','3','4','5')
Group By HBS_Response.HBS_id

Basically I added AS. Designer likes to have aliases with AS

Marek Chladny (BOB member since 2003-11-27)

system · October 19, 2006, 9:51pm

I figured out the issue. Designer does not use native syntax the correct syntax for Designer is as follows:
Select
HBS_Response.HBS_Id,
SUM(HBS_Response.Score) As ‘Total_Score’,
(Case When SUM(HBS_Response.Score) Is Not Null And SUM(HBS_Response.Score)= 50 Then 1 else 0 End) As ‘Evangelical’,
(Case When SUM(HBS_Response.Score) Is Not Null And SUM(HBS_Response.Score)<> 50 Then 1 else 0 End) As ‘Non_Evangelical’
From
HBS_Question,
HBS_Response
Where
HBS_Question.Q_Id = HBS_Response.Q_Id
AND HBS_Question.Q_Number In (‘1’,‘2’,‘3’,‘4’,‘5’)
Group By HBS_Response.HBS_id

mcliffordgoo (BOB member since 2003-02-13)

system · October 19, 2006, 9:51pm

Thanks for the update. I guess I was typing my post when you submitted yours. CHEERS!!!

mcliffordgoo (BOB member since 2003-02-13)

system · January 19, 2009, 11:08pm

This post was hellpful!

arun9911 (BOB member since 2008-01-16)

system · August 22, 2009, 6:16pm

Hi folks,i am getting this error even i used AS for each columns,this is my SQL.please help me with correct syntax

SELECT P.SALES_ID,
       MAX(P.SALES_NBR)AS SALES DESC,
              NVL(MAX(DECODE(CL.CATEGORY_CD,
                      'CAT',
                      DECODE(P.ROW_NM || '/' || M.REFER_ID,
                             'CONSUMER/800',
                             	  S.SHIPPING_DATE,
                             'PROFIT/800',
                             S.SHIPPING_DATE,
                             NULL)))AS CON_SALES1,
           MAX(DECODE(S.CATEGORY_ID,
                      'PLACE',
                      DECODE(P.ROW_NM || '/' || M.SERIES_ID,
                             'CONSUMER/10500',
                                 S.SHIPPING_DATE,
                              'PROFIT/10500',
                            S.SHIPPING_DATE,
                             NULL))))AS CON_SALES2
  FROM SALES P,SHIPPIING S, REFERENCE    M,
       CATEGORY    CL

Naveen Vanaparthi (BOB member since 2008-07-02)

system · August 22, 2009, 8:23pm

Hi,

Does the SQL work in a query tool (SQL*Plus, Toad, SQL Navigator) without a problem?

Marek Chladny (BOB member since 2003-11-27)

system · August 22, 2009, 10:02pm

Its working,BUT coming to Derived table its NOT

Naveen Vanaparthi (BOB member since 2008-07-02)

system · August 23, 2009, 3:01am

Hi

I doubt the query works. I re-write it a little bit, just to align brackets so it looks like this:

SELECT P.SALES_ID,
       MAX(P.SALES_NBR) AS SALES DESC,
       NVL ( MAX ( DECODE (CL.CATEGORY_CD,
                           'CAT', DECODE (P.ROW_NM || '/' || M.REFER_ID,
                                          'CONSUMER/800', S.SHIPPING_DATE,
                                          'PROFIT/800', S.SHIPPING_DATE,
                                          NULL
                                         )
                          )
                 ) AS CON_SALES1,
             MAX ( DECODE (S.CATEGORY_ID,
                           'PLACE', DECODE (P.ROW_NM || '/' || M.SERIES_ID,
                                            'CONSUMER/10500', S.SHIPPING_DATE,
                                            'PROFIT/10500', S.SHIPPING_DATE,
                                            NULL
                                           )
                          )
                 )
           ) AS CON_SALES2
  FROM SALES P,
       SHIPPIING S,
       REFERENCE M,
       CATEGORY CL

and as you can see, inside of the NVL function you are using a column alias “AS CON_SALES1”. I think this is not a valid query :?

Marek Chladny (BOB member since 2003-11-27)

system · August 24, 2009, 12:58pm

Thanks,i SOLVED it!
its basically AS, i have not put “As” with space after )

Naveen Vanaparthi (BOB member since 2008-07-02)

system · March 6, 2013, 2:09pm

Thank for this - helped me with my derived table too!

krulisia2 (BOB member since 2013-01-09)

system · January 28, 2014, 2:52pm

Hey…it’s not working with my scenario…
I’m having below code on Teradata -

 SELECT
 Branch_Inv_Dt as Branch_Inv_Dt,
CASE when MONTH(fiscal_year.calendar_dt) > 3 then YEAR(fiscal_year.calendar_dt) + 1 Else YEAR(fiscal_year.calendar_dt) End as Acctg_Period_Year_Id,
max(Unit_Cost_Amt) as Unit_Cost_Amt,
max(Returns_Flg) as Returns_Flg,
max(Inventory_Flg) as Inventory_Flg,
max(Manufacturing_Flg) as Manufacturing_Flg,
max(Sales_Flg) as Sales_Flg,
max(Price_Per_Unit_Amt) as Price_Per_Unit_Amt,
sum(TableA.Imp_Available_Qty) as Inventory_Qty,
sum(TableA.Imp_Available_Qty * Unit_Cost_Amt) as Inventory_Cost
FROM
  TableA, 
  Tblcalendar_date fiscal_year 
WHERE
fiscal_year.calendar_dt = 
case
when cast(@Prompt('Select Date','D',,MONO,free,,{'{d ''1900-01-01''}'}) as date) = '1900-01-01' 
then date 
else cast(@Prompt('Select Date','D',,MONO,free,,{'{d ''1900-01-01''}'}) as date)
end  
AND TableA.Branch_Inv_Dt =fiscal_year.calendar_dt
group by 1,2

This code does works fine when we replace actual values instead of @Prompt values. Not sure why its giving error in derived table.

chitra (BOB member since 2008-08-20)

system · January 28, 2014, 3:10pm

Hi,

What is the ‘then date’ part of the CASE statement in the WHERE clause supposed to do?

Marek Chladny (BOB member since 2003-11-27)

system · January 28, 2014, 4:38pm

Date, in Teradata, returns the current data. But You should use current_date, instead, as it will perform better.

Also, you are instructing the user to Select a date, but only giving them one value to select from. Change your LOV argument for the prompt to:

‘1900-01-01’

MichaelWelter (BOB member since 2002-08-08)