Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Calculaed field Post Reply Post New Topic
Author Message
Tupacmoche
Groupie
Groupie
Avatar

Joined: 04 Apr 2007
Online Status: Offline
Posts: 52
Quote Tupacmoche Replybullet Topic: Calculaed field
     Posted: 18 Feb 2014 at 3:39am
I have this simple select that uses a case statement to populate the column RouteType. It works fine it shows me types 1, 3, and 33 in the column. But, when , I try to filter on it by saying where RouteType = 3, I get an error message as indicated below INVALID COLUMN NAME. What, am, I doing wrong ?

Select wa.StartDateTime, ws.Scheduledreaddate, wa.WorkFilterName, ws.WorkSetID, RouteType =

      Case
           when
             wa.StartDateTime IS NULL or ws.ScheduledReadDate IS NULL
           then 33
           
           when
             cast(wa.startdatetime as date) <= cast(ws.scheduledreaddate as date) and
             wa.workfiltername IN ('DNRs','Type 2s/3s') or
             ((ws.worksetid %100 >= 50 and
             ((ws.worksetid %100 <= 69 and
             substring(ws.worksetid, len(ws.worksetid) - 3, 1) = 0))))
           then 1
              
           when
             cast(wa.startdatetime as date) <= cast(ws.scheduledreaddate as date) and
             wa.workfiltername IN ('DNRs','Type 2s/3s') or
             ((ws.worksetid %100 >= 50 and
             ((ws.worksetid %100 <= 69 and
             substring(ws.worksetid, len(ws.worksetid) - 3, 1) = 0))))
             
           then 2

      else
        3
    End
    
From fcs.dbo.WorkAssignment as wa

left outer join fcs.dbo.workset as ws
on wa.workSetID = ws.WorkSetID

-- where RouteType = 1 -- WHY IS IT THAT THIS DOES NOT WORK, INVALID COLUMN NAME????

order by 5
Rob
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 18 Feb 2014 at 4:37am
because the column RouteType does not exist at the time of the select...it only exist AFTER the select has executed.

Now we can get around it by doing something like:
with cte as (
   your select here
)
select * from cte where RouteType = 1

because SQL will create a table in memory and use that, and in that table RouteType exists.

HTH
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum