having trouble with a sql SP statement
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20587
Printed Date: 18 May 2024 at 12:05am
Topic: having trouble with a sql SP statement
Posted By: otto
Subject: having trouble with a sql SP statement
Date 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 '='.
|
Replies:
Posted By: kevlray
Date 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.
|
Posted By: kostya1122
Date 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#
|
|