Converting Crystal Reports Expression into SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting Crystal Reports Expression into SQL

Author

Topic

tupacmoche
Starting Member

7 Posts

Posted - 2013-11-23 : 20:11:10

I have the following code that I use in Crystal reports to exclude records based on its outcome. If the expression evaluates to 1 it does one thing, if it evaluates to 2 another and so on. I am converting the report into an SQL script but do not know how to implement this expression in sql. Any direction or assistance on this?

if (Date(WorkAssignment.StartDateTime) <= Date(WorkSet.ScheduledReadDate) and
(WorkAssignment.WorkFilterName) <> 'DNRs' and
(WorkAssignment.WorkFilterName) <> 'Type 2s/3s')) or
(val(right(WorkSet.WorkSetID, 2)) < 50 and mid(WorkSet.WorkSetID), len(WorkSet.WorkSetID) - 3, 1) = "0") then
1

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-24 : 04:10:21

In your example, the soft brackets don't match.
But I think you might be looking for something like:


case
   when cast (workassignment.startdatetime as date)<=cast(workset.scheduledreaddate as date)
         and  workassignment.workfiltername not in ('DNRs','Type 2s/3s')
             )
          or (workset.worksetid%100<50
         and  substring(workset.worksetid,length(workset.worksetid)-3,1)='0'
             )
      then 1
end

tupacmoche
Starting Member

7 Posts

Posted - 2013-11-24 : 08:55:28

In the Crystal reporting tool the above expression is given a name say RouteType and can be evaluated so that something like Where RouteType = 1 can limit the record if it is true. How can I implement this case expression in an SQL where clause?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-24 : 09:10:33

Try this:


where case
         when cast (workassignment.startdatetime as date)<=cast(workset.scheduledreaddate as date)
              and  workassignment.workfiltername not in ('DNRs','Type 2s/3s')
                  )
               or (workset.worksetid%100<50
              and  substring(workset.worksetid,length(workset.worksetid)-3,1)='0'
                  )
           then 1
      end=1

tupacmoche
Starting Member

7 Posts

Posted - 2013-11-24 : 14:49:44

I have joined two tables together and added the where clause suggested but get the following error messages:

1 - "non-boolean type specified in a context where a condition is expected". This error is on the word "case"

2 - "Incorrect syntax near then expecting ')', And, or OR".

I posted this question because, I ran into this error message before. Can the case statement be put into an expression called RouteType and then be used like this:
WHERE Route_Type = 1 ?

Select ws.P4DistrictNumber as BranchNo, ws.Cycle as Trip
from fcs.dbo.WorkSet as ws
left outer join fcs.dbo.WorkAssignment as wa
on ws.WorkSetKey = wa.WorkSetKey

where
case
when
cast(wa.startdatetime as date) <= cast(ws.scheduledreaddate as date) and
wa.workfiltername not in ('DNRs','Type 2s/3s') or
((ws.worksetid %100 < 50 and
substring(ws.worksetid, len(ws.worksetid) - 3, 1) = 0)
then 1
End = 1

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-24 : 16:46:06

Ups, seems like my soft bracket didn't match either.
Try this:


where case
         when cast ((workassignment.startdatetime as date)<=cast(workset.scheduledreaddate as date)
               and  workassignment.workfiltername not in ('DNRs','Type 2s/3s')
                   )
                or (workset.worksetid%100<50
               and  substring(workset.worksetid,length(workset.worksetid)-3,1)='0'
                   )
           then 1
      end=1

tupacmoche
Starting Member

7 Posts

Posted - 2013-11-24 : 17:15:53

Yes, that fixed it. Can you comment on creating an expression that can contain this code and be called just like the built in function of sql. Examples are substring(), length(),
cast(), ect.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-24 : 17:39:51

quote:
Originally posted by tupacmoche

Yes, that fixed it. Can you comment on creating an expression that can contain this code and be called just like the built in function of sql. Examples are substring(), length(),
cast(), ect.

You could put your query, as a subquery, and then be able to refer to the result of the expresion, as a normal table field. Like this:


select *
  from (select ws.P4DistrictNumber as BranchNo
              ,ws.Cycle as Trip
              ,case
                  when cast ((wa.startdatetime as date)<=cast(ws.scheduledreaddate as date)
                        and  wa.workfiltername not in ('DNRs','Type 2s/3s')
                            )
                         or (ws.worksetid%100<50
                        and  substring(ws.worksetid,length(ws.worksetid)-3,1)='0'
                            )
                    then 1
               end as Route_Type
          from fcs.dbo.WorkSet as ws
               left outer join fcs.dbo.WorkAssignment as wa
                            on ws.WorkSetKey=wa.WorkSetKey
       ) as a
 where a.Route_Type=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources