September 30, 2015 at 1:02 pm
Hello comunity
I need to build an update query for all my article beginning with '0.%' for my varchar field refkey, but depending on some conditions.
For example.
Case st.base <> '' and st.qttbase <> 0
Theo refkey = B
Case st.cos <>'' and st.qttcos <> 0
Then refkey = C
Case st.refo <> '' and st.qttrefo <> 0
Then refkey = R
But my problem are That i can have Many combination on refkey, for example:
B,BC,BCR,C,CB,CBR,R...and so on.
Also i need to separe the letters with comma, like:
B
B,C
B,C,R
.....
Someone could give me a small example about i can do that.
Many thanks,
Best regards
Lsantos
September 30, 2015 at 1:07 pm
luissantos (9/30/2015)
Hello comunityI need to build an update query for all my article beginning with '0.%' for my varchar field refkey, but depending on some conditions.
For example.
Case st.base <> '' and st.qttbase <> 0
Theo refkey = B
Case st.cos <>'' and st.qttcos <> 0
Then refkey = C
Case st.refo <> '' and st.qttrefo <> 0
Then refkey = R
But my problem are That i can have Many combination on refkey, for example:
B,BC,BCR,C,CB,CBR,R...and so on.
Also i need to separe the letters with comma, like:
B
B,C
B,C,R
.....
Someone could give me a small example about i can do that.
Many thanks,
Best regards
Lsantos
As posted it is very unclear what you are trying to do. In fact it isn't even clear what you are asking. Are you asking how to parse a delimited string? If so, then take a look at the link in my signature about splitting strings. If it is anything else, then please look at the article in my signature about 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/
September 30, 2015 at 2:43 pm
luissantos (9/30/2015)
Hello comunityI need to build an update query for all my article beginning with '0.%' for my varchar field refkey, but depending on some conditions.
For example.
Case st.base <> '' and st.qttbase <> 0
Theo refkey = B
Case st.cos <>'' and st.qttcos <> 0
Then refkey = C
Case st.refo <> '' and st.qttrefo <> 0
Then refkey = R
But my problem are That i can have Many combination on refkey, for example:
B,BC,BCR,C,CB,CBR,R...and so on.
Also i need to separe the letters with comma, like:
B
B,C
B,C,R
.....
Someone could give me a small example about i can do that.
Many thanks,
Best regards
Lsantos
It appears you know what conditions you need to worry about, but the question that your post generates is, what, exactly, do you intend to update? Do you intend to simply ADD a new refkey value to whatever values already exist in the field to be updated, or leave the existing value alone if it already contains the new value? Or is there some other consideration? If it's the former, then you have a rather seriously bad design for your table, but that may not be something you can do anything about. Please be a lot more specific about exactly what you are trying to accomplish.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 30, 2015 at 3:19 pm
Hello Sean
First, thanks for links that you send me.
In fact maybe my explanation is a little confused.
I will go to try explain better.
First, forget splitting string with comma delimiter because is not so important.
suppose that i have several article begin with '0.%', ex:
0.700/999 or 0.800/666
my field refkey must keep following values depending on:
Case st.base <> '' and st.qttbase <> 0
Theo refkey = B
Case st.cos <>'' and st.qttcos <> 0
Then refkey = C
Case st.refo <> '' and st.qttrefo <> 0
Then refkey = R
Now, i want to do and Update query to do that:
Article = '0.700/999'
st.base = 'base 0.700'
st.qttbase = 0.5
st.cos = ''
st.qttcos = 0.00
Then the field keyref must update with the following letters : B
Article = '0.800/666'
St.base = 'base 400'
st.qttbase = 0.045
St.cos = 'base 800'
st.qttcos = 0.02
Then the field keyref must update with the following letters : BC or CB (is not important the order)
Another example:
Article = '0.800/666'
St.base = 'base 400'
st.qttbase = 0.045
St.cos = 'cos alpha'
st.qttcos = 0.02
st.refo = 'Refo 111'
st.qttrefo = 0.55
Then the field keyref must update with the following letters : BCR or CBR or RBC (is not important the order)
i post this example with a TempTable where the value of Keyref are correct, now i need a query to Update several records adding the correct letters on my field keyref.
CREATE TABLE #tablearticle (ref VARCHAR(10),base VARCHAR(20), qttbase NUMERIC(10,4),COS VARCHAR(20), qttcos NUMERIC(10,4),
refo VARCHAR(20), qttref NUMERIC(10,4), keyref VARCHAR(4))
INSERT INTO #tablearticle(ref, base,qttbase,COS , qttcos, refo, qttref, keyref)
SELECT '0.700','base123',0.5,'',0.0,'',0.0,'B'
UNION ALL
SELECT '0.800','base456',0.5,'cos 123',0.25,'',0.0,'BC'
UNION ALL
SELECT '0.900','base456',0.5,'cos 123',0.25,'refo alpha',0.30,'BCR'
UNION ALL
SELECT '0.100','',0.0,'cos 123',0.25,'refo alpha',0.30,'CR'
UNION ALL
SELECT '0.400','Base 0.400',0.33,'',0.00,'refo alpha',0.30,'BR'
SELECT * FROM #tablearticle
I hope now is must easier to understand.
Many thanks ans best regards
Lsantos
September 30, 2015 at 3:20 pm
I think this is what you are looking for:
refkey = STUFF(
CASE
WHEN st.base <> '' and st.qttbase <> 0
THEN ',B'
ELSE ''
END +
CASE
WHEN st.cos <>'' and st.qttcos <> 0
THEN ',C'
ELSE ''
END +
CASE
WHEN st.refo <> '' and st.qttrefo <> 0
THEN ',R'
ELSE ''
END
, 1, 1, '')
If I understand correctly, each of the letters is independent of the others, so you need a separate CASE statement for each.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 30, 2015 at 3:41 pm
Hello Drew
This is exactly what i need, but curiously the keyref are not correct, this is the result:
[Code="other"]
ref base qttbaseCOS qttcosrefo qttref keyref
0.700 base123 0.5000 0.0000 0.0000
0.800 base456 0.5000cos 1230.2500 0.0000 C
0.900 base456 0.5000cos 1230.2500refo alpha 0.3000 CR
0.100 0.0000cos 1230.2500refo alpha 0.3000 R
0.400 Base 0.400 0.3300 0.0000refo alpha 0.3000 R
[/code]
the keyref must be:
1 line = B
2 line = BC
3 line = BCR
4 line = CR
5 line = BR
note: the comma delimiter is not important.
I hope you can explain to me why because your case statment are correct.
Best regards,
Lsantos
September 30, 2015 at 3:52 pm
Hello Drew
I read the STUFF function on SQL server, i now that´s work great.
Many thanks for your help.
Best regards,
Lsantos
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply