having trouble with a sql SP statement

Question

having trouble with a sql SP statement

otto840513

SSC Rookie

Points: 40
More actions
March 28, 2014 at 8:51 am

#305830

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 '='.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

djj SSCoach Points: 18831 More actions · Answer 1

otto840513 (3/28/2014)
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 '='.

I do not understand what you are trying to do with [font="Courier New"]stdesc = stdesc is not like '%FREIGHT'[/font]

Your CASE statement is not properly created.

otto840513 SSC Rookie Points: 40 More actions · Answer 2

for example, if I have the below data

sttckt sttot stdescr

123 0 coca cola

123 20 freight

123 0 coca cola

124 10 pepsi

124 0 freight

124 0 pepsi

I want the case returns coca cola for 123 and pepsi for 124

TY

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Hi and welcome to the forums. Unfortunately your post doesn't make a lot of sense. We can help you but you have to help us first. At the very least you have to explain the issue from the context of somebody who doesn't anything about what you are trying to do (like us).

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pietlinden SSC Guru Points: 63503 More actions · Answer 4

Here's a create table statement and the insert statements to populate your table. The column names are a guess. If they're descriptive, it helps a LOT to explain what you are modeling in your scenario. What exactly are you modeling? I'm just trying to understand what your end goal is. If you renamed your table and columns so that your table name is a real world object (like "Invoice"), it makes things infinitely clearer.

CREATE TABLE SomeObject (

TicketID INT,

Total INT,

CostCenter VARCHAR(10),

CONSTRAINT pkSomeTable PRIMARY KEY (TicketID, Total ,CostCenter)

);

GO

INSERT INTO SomeObject (TicketID,Total,CostCenter)

(SELECT 123 AS Ticket

, 0 AS Total

,'coca cola' AS stDescription

UNION ALL

SELECT 123, 20, 'freight'

UNION ALL

SELECT 123, 0, 'coca cola'

UNION ALL

SELECT 124, 10, 'pepsi'

UNION ALL

SELECT 124, 0, 'freight'

UNION

SELECT 124, 0, 'pepsi');

Hope this helps!

a4apple SSCrazy Points: 2620 More actions · Answer 5

Try this...

SELECT DISTINCT sttckt, stdescr

FROM @Input t1

WHERE stdescr <> 'freight'

AND EXISTS (SELECT 1 FROM @Input t2 WHERE t2.sttckt = t1.sttckt AND t2.stdescr = 'Freight')

Good Luck 🙂 .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.