November 2, 2011 at 8:34 am
I'm having a problem with seeing the proper results from a concatenation using a case statement within a select.
I have a table that seeds a number sequentially based on when the procedure gets run, and the file I will be creating will need have this value padded with zeros depending on its length.
If the value is 1 digits it need to be padded with 3 zeros on the left, 2 digits then with 2 zeros, etc.
The result i get upon running the code is just the seed number, no zeros added. Can someone help me get the zeros added? lol
Code is below:
declare @seedval int;
update LexingtonBordereau_Seed set seedid = seedid + 1, seeddate = getdate()
select @seedval = seedid from LexingtonBordereau_Seed
select len(@seedval)
select
top 1
396 as Feed_Sequence_Number,
'DT' as Detail_Record_Indicator,
'000077647' as Producer_Number,
convert(varchar(8), dateadd(s,-1,dateadd(mm, datediff(m,0,getdate()),0)), 112) as Account_Current_Date,
len(@seedval) as lengthofseedval,
case len(@seedval)
when 1 then '000'+@seedval
when 2 then '00'+@seedval
when 3 then '0'+@seedval
else @seedval end as Account_Current_Sequece_Number
from invoiceheader
Results are here, notice the number 4 at the end...I need that to be 0004...
Feed_Sequence_NumberDetail_Record_IndicatorProducer_NumberAccount_Current_DatelengthofseedvalAccount_Current_Sequece_Number
396DT0000776472011103114
November 2, 2011 at 8:36 am
Don't pad with a case statement.
Do it like this:
select right('0000' + @MyStringVariable, 4);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 8:41 am
select
top 1
396 as Feed_Sequence_Number,
'DT' as Detail_Record_Indicator,
'000077647' as Producer_Number,
convert(varchar(8), dateadd(s,-1,dateadd(mm, datediff(m,0,getdate()),0)), 112) as Account_Current_Date,
len(@seedval) as lengthofseedval,
RIGHT('000'+CAST(@seedval AS VARCHAR(4)),4) as Account_Current_Sequece_Number
from invoiceheader
What's this:
convert(varchar(8), dateadd(s,-1,dateadd(mm, datediff(m,0,getdate()),0)), 112) as Account_Current_Date,
supposed to do? There's probably a faster, neater way.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 2, 2011 at 8:42 am
I added your solution, but I probably didn't get it right cause I got the same result:
declare @seedval int;
update LexingtonBordereau_Seed set seedid = seedid + 1, seeddate = getdate()
select @seedval = seedid from LexingtonBordereau_Seed
select right('0000' + @seedval, 4);
select
top 1
396 as Feed_Sequence_Number,
'DT' as Detail_Record_Indicator,
'000077647' as Producer_Number,
convert(varchar(8), dateadd(s,-1,dateadd(mm, datediff(m,0,getdate()),0)), 112) as Account_Current_Date,
@seedval as Account_Current_Sequece_Number
from invoiceheader
Can you guide me to how to implement that line you sent? Sorry for my ignorance...
November 2, 2011 at 8:44 am
ChrisM @ work, that is the what I did to get the last day of the previous month from the current month we're in. Let me know if you know of a quicker cleaner way to get that.
November 2, 2011 at 8:47 am
select right('0000' + convert(varchar(3),@seedval), 4)
convert the @seedval to varchar(3) maybe?
November 2, 2011 at 8:50 am
ChrisM, that's a pretty standard way to get the last day of the previous month, formatted as YYYYMMDD. It doesn't have any columns in it, so you can just run it in its own Select statement and see the end result.
I usually discourage formatting the date in the query, since that's usually better-done in the presentation layer, but it does work, and in this case it isn't hurting anything.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 9:01 am
I gave this a try...not seeing any change in the query results...is it the select statement that's the problem...maybe the way I'm assigning the variable and referencing it in the select statement? Do I need to convert or cast the @seedval variable to varchar(4) in the select statement?
declare @seedval int;
--update LexingtonBordereau_Seed set seedid = seedid + 1, seeddate = getdate()
select @seedval = seedid from LexingtonBordereau_Seed
select @seedval = right('0000' + convert(varchar(1), @seedval), 4)
select
top 1
396 as Feed_Sequence_Number,
'DT' as Detail_Record_Indicator,
'000077647' as Producer_Number,
convert(varchar(8), dateadd(s,-1,dateadd(mm, datediff(m,0,getdate()),0)), 112) as Account_Current_Date,
@seedval as Account_Current_Sequece_Number
from invoiceheader
November 2, 2011 at 9:07 am
I got it figured out everyone...Thanks for all your suggestions and ideas. I appreciate it a ton.
The code below is what gave me the right result. I just need to put the padding line inside the select statement and not use it outside as a standalone select one it's own. Me being a newbie sure does make stuff like this more difficult than it should be. lol
declare @seedval int;
--update LexingtonBordereau_Seed set seedid = seedid + 1, seeddate = getdate()
select @seedval = seedid from LexingtonBordereau_Seed
select
top 1
396 as Feed_Sequence_Number,
'DT' as Detail_Record_Indicator,
'000077647' as Producer_Number,
convert(varchar(8), dateadd(s,-1,dateadd(mm, datediff(m,0,getdate()),0)), 112) as Account_Current_Date,
right('0000' + convert(varchar(1), @seedval), 4) as Account_Current_Sequece_Number
from invoiceheader
November 2, 2011 at 9:13 am
You'll want to expand that conversion to something more than Varchar(1). That will only hold one character, and kind of defeats the whole purpose of what you're doing here.
Make it Varchar(4).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 9:16 am
I realized that a few minutes ago and did in fact make it varchar(4). Thanks for having my back on that one.
SQL is slowly but surely going to make some sense to me.
November 2, 2011 at 9:24 am
Takes a bit, that's for sure. There's a lot to wrap your head around.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply