November 3, 2015 at 2:24 pm
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
November 3, 2015 at 2:42 pm
Probably could if we knew what it is you are trying to accomplish. It's possible that it may not even require a cursor.
November 3, 2015 at 3:49 pm
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.
-- Itzik Ben-Gan 2001
November 3, 2015 at 4:09 pm
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
November 3, 2015 at 5:35 pm
celticpiping (11/3/2015)
ok, fair questionsI 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?
November 3, 2015 at 5:46 pm
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.
November 4, 2015 at 6:14 am
yeah, I must have left off the tracking # on row3..oopsies
ok, I shall parse the supplied code...
Thanks!!!
R
November 11, 2015 at 2:21 pm
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
November 11, 2015 at 2:44 pm
celticpiping (11/11/2015)
Howdy again Lynn, things crashed around here so been ..bisySay, 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_no1Z03105968527697431259681
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_no1Z0310596852620358,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;
November 12, 2015 at 7:00 am
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
November 12, 2015 at 9:25 am
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?
November 12, 2015 at 9:32 am
One other thing, based on the sample data you provided, what is the expected results?
November 12, 2015 at 9:57 am
Lacking expected results:
CREATE TABLE #shipping_data(
tracking_no VARCHAR(8000) NOT NULL
,order_no VARCHAR(8000) 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');
CREATE TABLE #distinctOrderNo(
tracking_no VARCHAR(max) NOT NULL
,order_no VARCHAR(max) NOT NULL
);
with basedata as (
select distinct
ca1.order_no,
ca2.tracking_no
from
#shipping_data sd
cross apply (select Item from dbo.DelimitedSplit8K(sd.order_no,',')) ca1(order_no)
cross apply (select Item from dbo.DelimitedSplit8K(sd.tracking_no,',')) ca2(tracking_no)
), UniqueOrders as (
select distinct
order_no
from
basedata
)
insert into #distinctOrderNo(order_no,tracking_no)
select
uo.order_no order_no,
stuff((select ',' + tracking_no from basedata bd where bd.order_no = uo.order_no for xml path(''),TYPE).value('.','varchar(max)'),1,1,'') TrackingNumbers
from
UniqueOrders uo;
select * from #distinctOrderNo;
You'll have to add the drop statements for the temporary tables as I can't include those statements while posting from where I am.
November 15, 2015 at 4:31 pm
Is this modification of your original code a nudge in the right direction?
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT order_no FROM shipping_data
DECLARE @ORDERID char(15),
@myLen Int,
@Result CHAR(3)-- Modification 1 --
OPEN c1
FETCH NEXT FROM c1
INTO @ORDERID
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM c1
SET @myLen=LEN(@ORDERID)
SET @Result = CASE WHEN @myLEN >8 THEN '123'-- Modification 2 --
---ELSE '345'
END
END
CLOSE c1
DEALLOCATE c1
I remain sincerely and wish You success. J.V.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply