101 help with CASE inside of CURSOR statement

Question

Post reply

101 help with CASE inside of CURSOR statement

celticpiping

Old Hand

Points: 386
More actions
November 3, 2015 at 2:24 pm

#399272

Howdy, I'm wanting to act on row values(Length of order_no field), but cannot seem to get along with CASE...
Here's what I'm trying to do:
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT order_no FROM shipping_data
DECLARE @ORDERID char(15),
@myLen Int
OPEN c1
FETCH NEXT FROM c1
INTO @ORDERID
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM c1
SET @myLen=LEN(@ORDERID)
CASE @myLen
WHEN @myLEN >8 '123'
END
END
CLOSE c1
DEALLOCATE c1
I get:
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'CASE'.
Thoughts?
I've tried a few ways of working the CASE statement, but am failing...
Could use a nudge in the right direction..
Thanks much,
Rich

Viewing 14 posts - 1 through 13 (of 13 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Probably could if we knew what it is you are trying to accomplish. It's possible that it may not even require a cursor.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

As Lynn said, you probably don't need a cursor.

That said, you have no THEN statement in your CASE. A WHEN must be followed by a THEN.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

celticpiping Old Hand Points: 386 More actions · Answer 3

ok, fair questions

I did have a THEN: totally left it off for no reason..

The end goal is to find those "order_no's that are over 7 characters in length,

and if so, I need to parse the values found in said column(delimited by commas), and

for each 7 digit number, create a mechanism to then create a new row for each unique

"order_no", that corresponds to the "tracking_no"

Consider the following table data:

order_notracking_no

1256484 1Z0310590352855360

1254541 1Z0310590353390173

1234567,1234567123456,567890,234456

1256635 1Z0310590354060349

1256628 1Z0310590354270952

1256641 1Z0310590354847562

1245944,1245992,9998881Z0310590354866587

For row 3 for example, I want to end up with two rows that share the tracking_no found on that line.

For row7, I would have 3 rows sharing the tracking_no

Make sense?

Should I create a new table for this task?

I understand there's a whole lot of logic that's not there, such as string manipulation etc...

Thanks much for your expertise!

Rich

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

celticpiping (11/3/2015)
ok, fair questions
I did have a THEN: totally left it off for no reason..
The end goal is to find those "order_no's that are over 7 characters in length,
and if so, I need to parse the values found in said column(delimited by commas), and
for each 7 digit number, create a mechanism to then create a new row for each unique
"order_no", that corresponds to the "tracking_no"
Consider the following table data:
order_notracking_no
1256484 1Z0310590352855360
1254541 1Z0310590353390173
1234567,1234567123456,567890,234456
1256635 1Z0310590354060349
1256628 1Z0310590354270952
1256641 1Z0310590354847562
1245944,1245992,9998881Z0310590354866587
For row 3 for example, I want to end up with two rows that share the tracking_no found on that line.
For row7, I would have 3 rows sharing the tracking_no
Make sense?
Should I create a new table for this task?
I understand there's a whole lot of logic that's not there, such as string manipulation etc...
Thanks much for your expertise!
Rich

Please take a close look at your row three. See anything unusual about the tracking number?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Now for a solution:

create table #Orders (

order_no varchar(2000),

tracking_no varchar(64)

);

insert into #Orders(order_no, tracking_no)

values ('1256484','1Z0310590352855360'),

('1254541','1Z0310590353390173'),

('1234567,1234567','1Z0310590353390174'),

('1256635','1Z0310590354060349'),

('1256628','1Z0310590354270952'),

('1256641','1Z0310590354847562'),

('1245944,1245992,999888','1Z0310590354866587');

select * from #Orders;

select

ca.order_no,

o.tracking_no

from

#Orders o

cross apply (select Item from dbo.DelimitedSplit8K(o.order_no,',')) ca(order_no)

You will need the attached function to make this work.

Be sure to read ALL the comments in the function as well as the referenced article and the discussion thread that goes with it. Much to learn.

celticpiping Old Hand Points: 386 More actions · Answer 6

yeah, I must have left off the tracking # on row3..oopsies

ok, I shall parse the supplied code...

Thanks!!!

R

celticpiping Old Hand Points: 386 More actions · Answer 7

Howdy again Lynn, things crashed around here so been ..bisy

Say, not sure if I've used up my newb-requests for this month, but

concerning the query we've put together, I was wanting to end up with

the 'order_no' columns to be unique in the destination table.

So, considering this source data:

tracking_noorder_no

1Z03105968527697431259681

1Z03105968526203581259681

1Z0310590352836961NULL

1Z03105903540195731259716

1Z03105903537681861245807,1245812,1245803

1Z03105903526827981256835

1Z03105903543634051259750

when I run the following:

insert into distinctOrderNo(order_no,tracking_no) --#Orders(order_no, tracking_no)

select

LTRIM(RTRIM(ca.order_no)),

LTRIM(RTRIM(o.tracking_no))

from

shipping_data o

cross apply (select Item from dbo.DelimitedSplit8K(LTRIM(RTRIM(o.order_no)),',')) ca(order_no)

Where o.order_no is not NULL

I get this in destination table:

tracking_noorder_no

1Z03105968527697431259681

1Z03105968526203581259681

1Z03105903540195731259716

1Z03105903537681861245807

1Z03105903537681861245812

1Z03105903537681861245803

1Z03105903526827981256835

1Z03105903543634051259750

Desired outcome is:

tracking_noorder_no

1Z0310596852620358,1Z03105968527697431259681

1Z03105903540195731259716

1Z03105903537681861245807

1Z03105903537681861245812

1Z03105903537681861245803

1Z03105903526827981256835

1Z03105903543634051259750

So, you can see the desired outcome is such that, I'd have unique ORDER_NO values,

with TRACKING_NO (that are duplicated), stuffed together with commas...

Well, many thanks for thoughts etc

Regards,

Rich

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

celticpiping (11/11/2015)
Howdy again Lynn, things crashed around here so been ..bisy
Say, not sure if I've used up my newb-requests for this month, but
concerning the query we've put together, I was wanting to end up with
the 'order_no' columns to be unique in the destination table.
So, considering this source data:
tracking_noorder_no
1Z03105968527697431259681
1Z03105968526203581259681
1Z0310590352836961NULL
1Z03105903540195731259716
1Z03105903537681861245807,1245812,1245803
1Z03105903526827981256835
1Z03105903543634051259750
when I run the following:
insert into distinctOrderNo(order_no,tracking_no) --#Orders(order_no, tracking_no)
select
LTRIM(RTRIM(ca.order_no)),
LTRIM(RTRIM(o.tracking_no))
from
shipping_data o
cross apply (select Item from dbo.DelimitedSplit8K(LTRIM(RTRIM(o.order_no)),',')) ca(order_no)
Where o.order_no is not NULL
I get this in destination table:
tracking_noorder_no
1Z03105968527697431259681
1Z03105968526203581259681
1Z03105903540195731259716
1Z03105903537681861245807
1Z03105903537681861245812
1Z03105903537681861245803
1Z03105903526827981256835
1Z03105903543634051259750
Desired outcome is:
tracking_noorder_no
1Z0310596852620358,1Z03105968527697431259681
1Z03105903540195731259716
1Z03105903537681861245807
1Z03105903537681861245812
1Z03105903537681861245803
1Z03105903526827981256835
1Z03105903543634051259750
So, you can see the desired outcome is such that, I'd have unique ORDER_NO values,
with TRACKING_NO (that are duplicated), stuffed together with commas...
Well, many thanks for thoughts etc
Regards,
Rich

That wasn't what you originally requested.

Also, please look at how I set up my code to demonstrate a solution, in particular the part where I set up the test data and the table used to support the code. You need to do this instead of relying on us to do it for you. For help on this, please read the first article referenced below in my signature block. It will walk you through what and how to post to get the best answer.

create table #Orders (

order_no varchar(2000),

tracking_no varchar(64)

);

insert into #Orders(order_no, tracking_no)

values ('1256484','1Z0310590352855360'),

('1254541','1Z0310590353390173'),

('1234567,1234568','1Z0310590353390174'),

('1256635','1Z0310590354060349'),

('1256628','1Z0310590354270952'),

('1256641','1Z0310590354847562'),

('1245944,1245992,999888','1Z0310590354866587'),

('1259681','1Z0310596852769743'),

('1259681','1Z0310596852620358');

with basedata as (

select

ca.order_no,

o.tracking_no

from

#Orders o

cross apply (select Item from dbo.DelimitedSplit8K(o.order_no,',')) ca(order_no)

), UniqueOrders as (

select distinct

order_no

from

basedata

)

select

uo.order_no,

stuff((select ',' + tracking_no from basedata bd where bd.order_no = uo.order_no for xml path(''),TYPE).value('.','varchar(8000)'),1,1,'') TrackingNumbers

from

UniqueOrders uo;

celticpiping Old Hand Points: 386 More actions · Answer 9

understood: I should have known to post table data.

I'll attempt to provide table data below.

Source table, and associated insert code:

DROP TABLE #shipping_data;

CREATE TABLE #shipping_data(

tracking_no NVARCHAR(MAX) NOT NULL

,order_no VARCHAR(MAX) NOT NULL

);

INSERT INTO #shipping_data(tracking_no,order_no) VALUES ('1Z0310590353768186','1245807,1245812,1245803');

INSERT INTO #shipping_data(tracking_no,order_no) VALUES ('1Z0310590352682798','1256835,1359465');

INSERT INTO #shipping_data(tracking_no,order_no) VALUES ('1Z0310596852769743','1259681');

INSERT INTO #shipping_data(tracking_no,order_no) VALUES ('1Z0310596852620358','1259681');

INSERT INTO #shipping_data(tracking_no,order_no) VALUES ('1Z0310590354019573','1259716');

INSERT INTO #shipping_data(tracking_no,order_no) VALUES ('1Z0310590353422629','1259743');

INSERT INTO #shipping_data(tracking_no,order_no) VALUES ('1Z0310590354363405','1259750');

INSERT INTO #shipping_data(tracking_no,order_no) VALUES ('1Z0310590354133065','1259761');

INSERT INTO #shipping_data(tracking_no,order_no) VALUES ('1Z0310590153384282','9013594');

INSERT INTO #shipping_data(tracking_no,order_no) VALUES ('1Z0310590352836961','3216544');

INSERT INTO #shipping_data(tracking_no,order_no) VALUES ('1Z0310596852620358,1Z0310596852769743','1259681');

DROP TABLE #distinctOrderNo;

CREATE TABLE #distinctOrderNo(

tracking_no NVARCHAR(MAX) NOT NULL

,order_no VARCHAR(MAX) NOT NULL

);

insert into #distinctOrderNo(order_no,tracking_no) --#Orders(order_no, tracking_no)

select

LTRIM(RTRIM(ca.order_no)),

LTRIM(RTRIM(o.tracking_no))

from

#shipping_data o

cross apply (select Item from dbo.DelimitedSplit8K(LTRIM(RTRIM(o.order_no)),',')) ca(order_no)

WHERE o.order_no is not NULL

Select * from #distinctOrderNo

order by tracking_no

So you can see, we have duplicated order_no column values, AND we have multiple order_no values on one line. My apologies for not giving the full range of possibilities at the outset.

The code you posted does great at breaking out multiple order_no values, but the part I left off was the ability to string together tracking_no values when they occur with duplicate order_no values, leaving ONE (distinct) order_no value.

**I included the last INSERT so that you could see what the desired output is for the multiple tracking_no values.

I hope this post is at least SOME improvement on the past one.

🙂

Thanks again, much appreciated all.

AQ2 Rich "Goose" Gouette

USN ATKRON 75

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

celticpiping (11/12/2015)
understood: I should have known to post table data.
I'll attempt to provide table data below.
Source table, and associated insert code:
...
CREATE TABLE #shipping_data(
tracking_no NVARCHAR(MAX) NOT NULL
,order_no VARCHAR(MAX) NOT NULL
);
...
So you can see, we have duplicated order_no column values, AND we have multiple order_no values on one line. My apologies for not giving the full range of possibilities at the outset.
The code you posted does great at breaking out multiple order_no values, but the part I left off was the ability to string together tracking_no values when they occur with duplicate order_no values, leaving ONE (distinct) order_no value.
**I included the last INSERT so that you could see what the desired output is for the multiple tracking_no values.
I hope this post is at least SOME improvement on the past one.
🙂
Thanks again, much appreciated all.
AQ2 Rich "Goose" Gouette
USN ATKRON 75

Please tell me that this is just your creation for the purpose of this forum and not the actual datatypes being used:

...

CREATE TABLE #shipping_data(

tracking_no NVARCHAR(MAX) NOT NULL -- Are these really max data types in production?

,order_no VARCHAR(MAX) NOT NULL

);

...

The split routine I provided you work with varchar values up to 8000 characters. You need the nvarchar version for nvarchar data and that only handles up to 4000 nvarchar characters.

If they are max data types, what is the max length of data in each column in production?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

One other thing, based on the sample data you provided, what is the expected results?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Lacking expected results:

CREATE TABLE #shipping_data(

tracking_no VARCHAR(8000) NOT NULL

,order_no VARCHAR(8000) NOT NULL