SQL Query

[Fordyce]

I really hope someone would help me. I had this query:

"Select distinct DVD.DVDid,Rent_ Detail.RentNo,T itle,Num_of_DVD ,(getdate()-RentDate)+1 As RentPeriod,(CAS E WHEN RentPeriod>5 THEN (RentPeriod-5)*500 ELSE RentPeriod*0 END) As Fine From Member,Rental,D VD,Rent_Detail Where DVD.DVDid=Rent_ Detail.DVDid And Rental.RentNo=R ent_Detail.Rent No And Member.MemberID =Rental.MemberI D And Member.MemberID ='" & ComboBox1.Text & "' AND Rent_Detail.DVD id='" & DGV1.Rows(e.Row Index).Cells(0) .Value & "' AND Rent_Detail.Num _of_DVD>0"

Now my question is, there are error saying that invalid column from 3 RentPeriods in CASE, but I use those just to create a column name "fine". Why is that?

[Rabbit]

You can't refer to a calculated field in the same SQL level in which it was created.

[gpl]

In the case statement, replace 'rentperiod' by the definition of rentperiod from the previous column

[pooja rani]

keyword as must be in either lower or upper but not As.