CODEWITHSUNDEEP
sqlsql-serverselect
floormind
floormind

Reputation: 2028

error in select statement

In the query below, i get an error on the 'LettingPercent' on the fourth line, 'invalid column name'. I want to use each result returned in the lettingpercentage to calculate a letting fee which is on the fourth line of my statement

declare @let varchar(50)
select 
CONVERT(varchar(50), InstructionLettingFee.percentage)+'%' as 'LettingPercent',
CONVERT(decimal(18,2), LettingPercent / (100 * DealFees.pddrl_TermRent)) as LettingFee
from tableOne
left outer join tableTwo LettingInstruction on LettingInstruction.ColumnOne=     tableOne.ColumnOne
left outer join TableThree InstructionLettingFee on InstructionLettingFee.ColumnForOne = LettingInstruction.ColumTwo
left outer join TableFour DealFees on DealFees.ColumnOne = pDeal.ColumnOne

Upvotes: 0

Views: 109

Answers (5)

jenson-button-event
jenson-button-event

Reputation: 18941

You cannot reference an alias like that, you will need to repeat the statement. Even if you could you have turned LettingPercent into a string. Try:

    declare @let varchar(50)
    select 
    CONVERT(varchar(50), InstructionLettingFee.percentage)+'%' as 'LettingPercent',
LettingFee =
case InstructionLettingFee.percentage
when 0 then cast( 0 as decimal(18,2) )
else  CONVERT(decimal(18,2), InstructionLettingFee.percentage / (100 * DealFees.pddrl_TermRent)) 
end
    from tableOne
    left outer join tableTwo LettingInstruction on LettingInstruction.ColumnOne=     tableOne.ColumnOne
    left outer join TableThree InstructionLettingFee on InstructionLettingFee.ColumnForOne = LettingInstruction.ColumTwo
    left outer join TableFour DealFees on DealFees.ColumnOne = pDeal.ColumnOne

Upvotes: 1

Ray 'user1578904'
Ray 'user1578904'

Reputation: 482

When using the AS keyword and not having any spaces in your alises, there is no need for quoting the string. But if you want to add quoting to your aliases then you should use double quote, and not single quotes, i.e.

... AS "LettingPercent"

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

The single quotes are unnecessary. But the problem is that you are referring to the value in the next line. Instead go back to the original data:

declare @let varchar(50)
select CONVERT(varchar(50), InstructionLettingFee.percentage)+'%') as LettingPercent,
       CONVERT(decimal(18,2), InstructionLettingFee.percentage / (100 * DealFees.pddrl_TermRent)) as LettingFee
from tableOne
left outer join tableTwo LettingInstruction on LettingInstruction.ColumnOne=     tableOne.ColumnOne
left outer join TableThree InstructionLettingFee on InstructionLettingFee.ColumnForOne = LettingInstruction.ColumTwo
left outer join TableFour DealFees on DealFees.ColumnOne = pDeal.ColumnOne;

EDIT:

To prevent divide-by-0, put the case around the calculation:

       (case when DealFees.pddrl_TermRent > 0
             then CONVERT(decimal(18,2), InstructionLettingFee.percentage / (100 * DealFees.pddrl_TermRent))
        end) as LettingFee

Upvotes: 1

Sonam
Sonam

Reputation: 3466

You are referencing an alias in the query which is wrong, please try the following query:

select CONVERT(varchar(50), LettingPercent)+'% LettingPercent',
CONVERT(decimal(18,2), LettingPercent / (100 * pddrl_TermRent)) as LettingFee
from
(select 
InstructionLettingFee.percentage as LettingPercent,
DealFees.pddrl_TermRent
from tableOne
left outer join tableTwo LettingInstruction on LettingInstruction.ColumnOne=     tableOne.ColumnOne
left outer join TableThree InstructionLettingFee on InstructionLettingFee.ColumnForOne = LettingInstruction.ColumTwo
left outer join TableFour DealFees on DealFees.ColumnOne = pDeal.ColumnOne)

Upvotes: 0

Khan
Khan

Reputation: 18142

Unfortunately, columns dynamically created cannot be accessed by other columns at the same level.

You can use a derived table to achieve this though:

SELECT 
    CONVERT(varchar(50), LettingPercent)+ '%' as LettingPercent,
    CONVERT(decimal(18,2), LettingPercent / (100 * TermRent)) as LettingFee
FROM (
    SELECT 
        InstructionLettingFee.percentage AS LettingPercent,
        DealFees.pddrl_TermRent AS TermRent
    from tableOne
    left outer join tableTwo LettingInstruction on LettingInstruction.ColumnOne = tableOne.ColumnOne
    left outer join TableThree InstructionLettingFee on InstructionLettingFee.ColumnForOne = LettingInstruction.ColumTwo
    left outer join TableFour DealFees on DealFees.ColumnOne = pDeal.ColumnOne
) A

Upvotes: 0

Related Questions