Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Tips and Tricks
Message Icon Topic: having trouble with a sql SP statement Post Reply Post New Topic
Author Message
otto
Groupie
Groupie


Joined: 17 Mar 2014
Online Status: Offline
Posts: 53
Quote otto Replybullet Topic: having trouble with a sql SP statement
    Posted: 28 Mar 2014 at 4:31am
I have 3 records grouped by ticket number(it is a field in my table), in 2 of them I have the same stdesc but in the other one I have a description that contains FREIGHT' word. If sttot field >0 and stdesc contains FREIGHT' word, I would like to get the other description else just stdesc

I am trying to add this

stdesc=max(case when(sttot >0 and stdesc like '%FREIGHT') then (stdesc = stdesc is not like '%FREIGHT') else stdesc end),

to a sql statement and sql gave me the following message

Incorrect syntax near '='.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 28 Mar 2014 at 5:57am
You have two assignment statements in your SQL.  Not quite sure what you are trying to accomplish on the THEN part of the case.
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 28 Mar 2014 at 2:37pm
this might work

select ticket#,
    max(case when x.stdesc is null then y.stdesc else x.stdesc end) as x.stdesc
   
                from some_table as y
left outer join(
                select ticket#,  max(stdesc) as stdesc
                from some_table
                where (sttot >0 and stdesc like '%FREIGHT')
                group by ticket#
                ) as x
on x.ticket# = y.ticket#
group by ticket#
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.035 seconds.