September 22, 2016 at 12:14 pm
I get the error when i query all or any data with F or S at the end.
Some new ID numbers are now having S or F being populated.
ADP-000077S or ADP-000077F.
The CASE statement is what the computed column BARCODE_COMP is using. I'm trying to make the change so the DB can accept the F or S
(case when [ID_NUM] like 'AE%' then CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0))+right(CONVERT([varchar],CONVERT([int],substring(CONVERT([varchar],(((((case when CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(12),(1)),(0))*(2)>(9) then CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(12),(1)),(0))*(2)-(9) else CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(12),(1)),(0))*(2) end+case when CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(10),(1)),(0))*(2)>(9) then CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(10),(1)),(0))*(2)-(9) else CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(10),(1)),(0))*(2) end)+case when CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(8),(1)),(0))*(2)>(9) then CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(8),(1)),(0))*(2)-(9) else CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(8),(1)),(0))*(2) end)+CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(11),(1)),(0)))+CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(9),(1)),(0)))+CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(7),(1)),(0)))+(10),(0)),(2),(1)),(0))-(10),(0)),(1)) when [ID_NUM] like 'ADP%' AND [OLDID] IS NULL AND [CLASSIFY]='FACULTY' then ('111221'+substring([ID_NUM],(5),(6)))+right(CONVERT([varchar],CONVERT([int],substring(CONVERT([varchar],(((((case when CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(12),(1)),(0))*(2)>(9) then CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(12),(1)),(0))*(2)-(9) else CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(12),(1)),(0))*(2) end+case when CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(10),(1)),(0))*(2)>(9) then CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(10),(1)),(0))*(2)-(9) else CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(10),(1)),(0))*(2) end)+case when CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(8),(1)),(0))*(2)>(9) then CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(8),(1)),(0))*(2)-(9) else CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(8),(1)),(0))*(2) end)+CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(11),(1)),(0)))+CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(9),(1)),(0)))+CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(7),(1)),(0)))+(17),(0)),(2),(1)),(0))-(10),(0)),(1)) when [ID_NUM] like 'ADP%' AND [OLDID] IS NULL AND [CLASSIFY]='STAFF'
then ('111222'+substring([ID_NUM],(5),(6)))+right(CONVERT([varchar],CONVERT([int],substring(CONVERT([varchar],(((((case when CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(12),(1)),(0))*(2)>(9) then CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(12),(1)),(0))*(2)-(9) else CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(12),(1)),(0))*(2) end+case when CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(10),(1)),(0))*(2)>(9) then CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(10),(1)),(0))*(2)-(9) else CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(10),(1)),(0))*(2) end)+case when CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(8),(1)),(0))*(2)>(9) then CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(8),(1)),(0))*(2)-(9) else CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(8),(1)),(0))*(2) end)+CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(11),(1)),(0)))+CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(9),(1)),(0)))+CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(7),(1)),(0)))+(17),(0)),(2),(1)),(0))-(10),(0)),(1)) when [ID_NUM] like 'ADP%' AND [OLDID] IS NOT NULL then ('11122800'+substring([OLDID],(5),(4)))+right(CONVERT([varchar],CONVERT([int],substring(CONVERT([varchar],(((((case when CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(12),(1)),(0))*(2)>(9) then CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(12),(1)),(0))*(2)-(9) else CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(12),(1)),(0))*(2) end+case when CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(10),(1)),(0))*(2)>(9) then CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(10),(1)),(0))*(2)-(9) else CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(10),(1)),(0))*(2) end)+case when CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(8),(1)),(0))*(2)>(9) then CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(8),(1)),(0))*(2)-(9) else CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(8),(1)),(0))*(2) end)+CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(11),(1)),(0)))+CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(9),(1)),(0)))+CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(7),(1)),(0)))+(17),(0)),(2),(1)),(0))-(10),(0)),(1)) when [ID_NUM] like 'PC%' then ('111220'+substring([ID_NUM],(6),(6)))+right(CONVERT([varchar],CONVERT([int],substring(CONVERT([varchar],(((((case when CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(12),(1)),(0))*(2)>(9) then CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(12),(1)),(0))*(2)-(9) else CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(12),(1)),(0))*(2) end+case when CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(10),(1)),(0))*(2)>(9) then CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(10),(1)),(0))*(2)-(9) else CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(10),(1)),(0))*(2) end)+case
when CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(8),(1)),(0))*(2)>(9) then CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(8),(1)),(0))*(2)-(9) else CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(8),(1)),(0))*(2) end)+CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(11),(1)),(0)))+CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(9),(1)),(0)))+CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(7),(1)),(0)))+(10),(0)),(2),(1)),(0))-(10),(0)),(1)) end)
September 22, 2016 at 12:33 pm
I'm sorry, but this is hands down the worst sample of SQL posted ever. It looks like a hex dump. Conversion error in Line 2? Where is Line 2? :ermm:
Rather than attempting to make sense of this code, let's instead identify which rows contain a value that fails to convert.
You can use the TRY_CAST function to identify column values that won't cast (convert) to a specific type. You specify two parameters, a table column and the datatype you're wanting to cast to. If a cast fails, the function returns NULL. For example, the query below will return all rows from Table1 where Col1 cannot be cast as an integer.
select * from Table1 where try_cast( Col1 as int ) is null;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 22, 2016 at 12:39 pm
Eric M Russell (9/22/2016)
I'm sorry, but this is hands down the worst sample of SQL posted ever. It looks like a hex dump. Conversion error in Line 2? Where is Line 2? :ermm:
It was probably a posting error, there is clearly only one line of code here.
September 22, 2016 at 12:42 pm
I have reformatted this post to help SSC's team of experts assist the OP
(case when [ID_NUM] like 'AE%' then CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0))+right(CONVERT([varchar],CONVERT([int],substring(CONVERT([varchar],(((((case when CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(12),(1)),(0))*(2)>(9) then CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(12),(1)),(0))*(2)-(9) else CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(12),(1)),(0))*(2) end+case when CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(10),(1)),(0))*(2)>(9) then CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(10),(1)),(0))*(2)-(9) else CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(10),(1)),(0))*(2) end)+case when CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(8),(1)),(0))*(2)>(9) then CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(8),(1)),(0))*(2)-(9) else CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(8),(1)),(0))*(2) end)+CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(11),(1)),(0)))+CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(9),(1)),(0)))+CONVERT([int],substring(CONVERT([varchar],(111210000000.)+CONVERT([int],substring([ID_NUM],(3),(20)),(0)),(0)),(7),(1)),(0)))+(10),(0)),(2),(1)),(0))-(10),(0)),(1)) when [ID_NUM] like 'ADP%' AND [OLDID] IS NULL AND [CLASSIFY]='FACULTY' then ('111221'+substring([ID_NUM],(5),(6)))+right(CONVERT([varchar],CONVERT([int],substring(CONVERT([varchar],(((((case when CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(12),(1)),(0))*(2)>(9) then CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(12),(1)),(0))*(2)-(9) else CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(12),(1)),(0))*(2) end+case when CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(10),(1)),(0))*(2)>(9) then CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(10),(1)),(0))*(2)-(9) else CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(10),(1)),(0))*(2) end)+case when CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(8),(1)),(0))*(2)>(9) then CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(8),(1)),(0))*(2)-(9) else CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(8),(1)),(0))*(2) end)+CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(11),(1)),(0)))+CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(9),(1)),(0)))+CONVERT([int],substring('111221'+substring([ID_NUM],(5),(6)),(7),(1)),(0)))+(17),(0)),(2),(1)),(0))-(10),(0)),(1)) when [ID_NUM] like 'ADP%' AND [OLDID] IS NULL AND [CLASSIFY]='STAFF'
then ('111222'+substring([ID_NUM],(5),(6)))+right(CONVERT([varchar],CONVERT([int],substring(CONVERT([varchar],(((((case when CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(12),(1)),(0))*(2)>(9) then CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(12),(1)),(0))*(2)-(9) else CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(12),(1)),(0))*(2) end+case when CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(10),(1)),(0))*(2)>(9) then CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(10),(1)),(0))*(2)-(9) else CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(10),(1)),(0))*(2) end)+case when CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(8),(1)),(0))*(2)>(9) then CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(8),(1)),(0))*(2)-(9) else CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(8),(1)),(0))*(2) end)+CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(11),(1)),(0)))+CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(9),(1)),(0)))+CONVERT([int],substring('111222'+substring([ID_NUM],(5),(6)),(7),(1)),(0)))+(17),(0)),(2),(1)),(0))-(10),(0)),(1)) when [ID_NUM] like 'ADP%' AND [OLDID] IS NOT NULL then ('11122800'+substring([OLDID],(5),(4)))+right(CONVERT([varchar],CONVERT([int],substring(CONVERT([varchar],(((((case when CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(12),(1)),(0))*(2)>(9) then CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(12),(1)),(0))*(2)-(9) else CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(12),(1)),(0))*(2) end+case when CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(10),(1)),(0))*(2)>(9) then CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(10),(1)),(0))*(2)-(9) else CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(10),(1)),(0))*(2) end)+case when CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(8),(1)),(0))*(2)>(9) then CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(8),(1)),(0))*(2)-(9) else CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(8),(1)),(0))*(2) end)+CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(11),(1)),(0)))+CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(9),(1)),(0)))+CONVERT([int],substring('11122800'+substring([OLDID],(5),(4)),(7),(1)),(0)))+(17),(0)),(2),(1)),(0))-(10),(0)),(1)) when [ID_NUM] like 'PC%' then ('111220'+substring([ID_NUM],(6),(6)))+right(CONVERT([varchar],CONVERT([int],substring(CONVERT([varchar],(((((case when CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(12),(1)),(0))*(2)>(9) then CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(12),(1)),(0))*(2)-(9) else CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(12),(1)),(0))*(2) end+case when CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(10),(1)),(0))*(2)>(9) then CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(10),(1)),(0))*(2)-(9) else CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(10),(1)),(0))*(2) end)+case
when CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(8),(1)),(0))*(2)>(9) then CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(8),(1)),(0))*(2)-(9) else CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(8),(1)),(0))*(2) end)+CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(11),(1)),(0)))+CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(9),(1)),(0)))+CONVERT([int],substring('111220'+substring([ID_NUM],(6),(6)),(7),(1)),(0)))+(10),(0)),(2),(1)),(0))-(10),(0)),(1)) end)
September 22, 2016 at 12:51 pm
sorry I meant to delete that once I added the txt file.
September 22, 2016 at 12:54 pm
nilknarf (9/22/2016)
sorry I meant to delete that once I added the txt file.
I see no text file.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 22, 2016 at 12:55 pm
I ran
select * from IDCARD where try_cast( barcode_comp as int ) is null;
and still get
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'F' to data type int.
This TRY_CAST should show me what's failing correct?
September 22, 2016 at 12:56 pm
Forgot to hit the upload button.
September 22, 2016 at 1:08 pm
That's only a snippet. It's missing the SELECT.
There's no way I'm going through all that code. Format it if you expect any help.
All the results from a CASE expression should have the same data type. If they don't, SQL Server will implicitly convert them using Data Type Precedence, so be sure to convert all integers into strings.
September 22, 2016 at 1:10 pm
nilknarf (9/22/2016)
I ranselect * from IDCARD where try_cast( barcode_comp as int ) is null;
and still get
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'F' to data type int.
This TRY_CAST should show me what's failing correct?
If your computed column is having difficulties, it won't make it as far as the 'try_cast' in your select statement criteria. Are you sure your computed column is not having difficulties in actually computing the column?
If you accumulate a nontrivial amount of programming text, you need to be able to independently test individual sections of it, otherwise you cannot effectively search for malfunctioning code, it becomes an all or nothing affair. The way your computed column is presented, there doesn't seem to be any effective method to test any given portion of it. It might be if it was formatted somehow, like individual case "clauses" on each line, then you could test the individual conditions that each "when" clause evaluates true for.
If I were lucky enough to be handed this assignment, the very first item of business would be to put that computed column code into a test rig and that would include splitting it up into manageable sections (after reformatting in such a way that I could even identify the candidates for manageable sections).
September 22, 2016 at 1:23 pm
Holy what the???? There are so many issues with that code it is hard to know where to start. First of all...you are storing more than 1 value in a single cell. This violates 1NF. This is obvious with the multitude of substrings for the same column over and over. Then you have varchar all over the place with no length specified. This has all the appearance of generated code that is spit out from a database that did not get much design and everything is fixed width columns with a complete lack of normalization.
What you really need to do is hire a consultant to come in and fix this database. The time and cost is going to be significant though if this is typical of the things you find in your system.
_______________________________________________________________
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 22, 2016 at 2:25 pm
This looks like generated code.
1. Extreme overuse of parens. NOBODY writes SUBSTRING([ID_NUM], (3), (20)) when SUBSTRING([ID_NUM], 3, 20) means the same thing, is easier to read, and takes fewer keystrokes.
2. Use of inapplicable optional parameters. NOBODY specifies an optional parameter when it cannot possibly make a difference. When converting to INT, the style parameter is ignored, so there is no reason to include it. CONVERT([INT], SUBSTRING([ID_NUM], (3), (20)), (0)) is the same as CONVERT([INT], SUBSTRING([ID_NUM], 3, 20)).
3. Endlessly repeating the same calculations. SUBSTRING(ID_NUM, 3, 20) is executed 13 separate times. Use a CTE, derived table, ILTV function, or CROSS APPLY to "store" that value in a field and then use that field.
I could go on, but I don't want to look at that monstrosity anymore.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 22, 2016 at 2:55 pm
not sure at all what you are trying to do.......maybe the OP can explain.
heres some set up and OP code reformatted with results
CREATE TABLE #sometable(
ID_NUM VARCHAR(50)
,OLDID VARCHAR(50)
,CLASSIFY VARCHAR(50)
);
INSERT INTO #sometable(ID_NUM,OLDID,CLASSIFY) VALUES
('ADP-000077',NULL,'FACULTY')
,('ADP-000077','jls',NULL)
,('ADP-000077','ABC123',NULL)
,('ADP-000077',NULL,'STAFF')
,('ADP-000077S',NULL,'FACULTY')
,('ADP-000077S','jls',NULL)
,('ADP-000077S',NULL,'STAFF')
,('ADP-000077S','ABC123','FACULTY')
,('ADP-000077F',NULL,'FACULTY')
,('ADP-000077F','jls',NULL)
,('ADP-000077F',NULL,'STAFF')
,('ADP-000077F','ABC123','FACULTY')
;
SELECT *,
(CASE
WHEN [ID_NUM] LIKE 'AE%'
THEN CONVERT( [VARCHAR], (111210000000.) + CONVERT([INT], SUBSTRING([ID_NUM], (3), (20)), (0)), (0)) + RIGHT(CONVERT([VARCHAR], CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (((((CASE
WHEN CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (111210000000.)+CONVERT([INT], SUBSTRING([ID_NUM], (3), (20)), (0)), (0)), (12), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (111210000000.)+CONVERT([INT], SUBSTRING([ID_NUM], (3), (20)), (0)), (0)), (12), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (111210000000.)+CONVERT([INT], SUBSTRING([ID_NUM], (3), (20)), (0)), (0)), (12), (1)), (0))*(2)
END+CASE
WHEN CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (111210000000.)+CONVERT([INT], SUBSTRING([ID_NUM], (3), (20)), (0)), (0)), (10), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (111210000000.)+CONVERT([INT], SUBSTRING([ID_NUM], (3), (20)), (0)), (0)), (10), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (111210000000.)+CONVERT([INT], SUBSTRING([ID_NUM], (3), (20)), (0)), (0)), (10), (1)), (0))*(2)
END)+CASE
WHEN CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (111210000000.)+CONVERT([INT], SUBSTRING([ID_NUM], (3), (20)), (0)), (0)), (8), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (111210000000.)+CONVERT([INT], SUBSTRING([ID_NUM], (3), (20)), (0)), (0)), (8), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (111210000000.)+CONVERT([INT], SUBSTRING([ID_NUM], (3), (20)), (0)), (0)), (8), (1)), (0))*(2)
END)+CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (111210000000.)+CONVERT([INT], SUBSTRING([ID_NUM], (3), (20)), (0)), (0)), (11), (1)), (0)))+CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (111210000000.)+CONVERT([INT], SUBSTRING([ID_NUM], (3), (20)), (0)), (0)), (9), (1)), (0)))+CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (111210000000.)+CONVERT([INT], SUBSTRING([ID_NUM], (3), (20)), (0)), (0)), (7), (1)), (0)))+(10), (0)), (2), (1)), (0)) - (10), (0)), (1))
WHEN [ID_NUM] LIKE 'ADP%'
AND [OLDID] IS NULL
AND [CLASSIFY] = 'FACULTY'
THEN('111221'+SUBSTRING([ID_NUM], (5), (6)))+RIGHT(CONVERT([VARCHAR], CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (((((CASE
WHEN CONVERT([INT], SUBSTRING('111221'+SUBSTRING([ID_NUM], (5), (6)), (12), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING('111221'+SUBSTRING([ID_NUM], (5), (6)), (12), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING('111221'+SUBSTRING([ID_NUM], (5), (6)), (12), (1)), (0))*(2)
END+CASE
WHEN CONVERT([INT], SUBSTRING('111221'+SUBSTRING([ID_NUM], (5), (6)), (10), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING('111221'+SUBSTRING([ID_NUM], (5), (6)), (10), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING('111221'+SUBSTRING([ID_NUM], (5), (6)), (10), (1)), (0))*(2)
END)+CASE
WHEN CONVERT([INT], SUBSTRING('111221'+SUBSTRING([ID_NUM], (5), (6)), (8), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING('111221'+SUBSTRING([ID_NUM], (5), (6)), (8), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING('111221'+SUBSTRING([ID_NUM], (5), (6)), (8), (1)), (0))*(2)
END)+CONVERT([INT], SUBSTRING('111221'+SUBSTRING([ID_NUM], (5), (6)), (11), (1)), (0)))+CONVERT([INT], SUBSTRING('111221'+SUBSTRING([ID_NUM], (5), (6)), (9), (1)), (0)))+CONVERT([INT], SUBSTRING('111221'+SUBSTRING([ID_NUM], (5), (6)), (7), (1)), (0)))+(17), (0)), (2), (1)), (0))-(10), (0)), (1))
WHEN [ID_NUM] LIKE 'ADP%'
AND [OLDID] IS NULL
AND [CLASSIFY] = 'STAFF'
THEN('111222'+SUBSTRING([ID_NUM], (5), (6)))+RIGHT(CONVERT([VARCHAR], CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (((((CASE
WHEN CONVERT([INT], SUBSTRING('111222'+SUBSTRING([ID_NUM], (5), (6)), (12), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING('111222'+SUBSTRING([ID_NUM], (5), (6)), (12), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING('111222'+SUBSTRING([ID_NUM], (5), (6)), (12), (1)), (0))*(2)
END+CASE
WHEN CONVERT([INT], SUBSTRING('111222'+SUBSTRING([ID_NUM], (5), (6)), (10), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING('111222'+SUBSTRING([ID_NUM], (5), (6)), (10), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING('111222'+SUBSTRING([ID_NUM], (5), (6)), (10), (1)), (0))*(2)
END)+CASE
WHEN CONVERT([INT], SUBSTRING('111222'+SUBSTRING([ID_NUM], (5), (6)), (8), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING('111222'+SUBSTRING([ID_NUM], (5), (6)), (8), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING('111222'+SUBSTRING([ID_NUM], (5), (6)), (8), (1)), (0))*(2)
END)+CONVERT([INT], SUBSTRING('111222'+SUBSTRING([ID_NUM], (5), (6)), (11), (1)), (0)))+CONVERT([INT], SUBSTRING('111222'+SUBSTRING([ID_NUM], (5), (6)), (9), (1)), (0)))+CONVERT([INT], SUBSTRING('111222'+SUBSTRING([ID_NUM], (5), (6)), (7), (1)), (0)))+(17), (0)), (2), (1)), (0))-(10), (0)), (1))
WHEN [ID_NUM] LIKE 'ADP%'
AND [OLDID] IS NOT NULL
THEN('11122800'+SUBSTRING([OLDID], (5), (4)))+RIGHT(CONVERT([VARCHAR], CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (((((CASE
WHEN CONVERT([INT], SUBSTRING('11122800'+SUBSTRING([OLDID], (5), (4)), (12), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING('11122800'+SUBSTRING([OLDID], (5), (4)), (12), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING('11122800'+SUBSTRING([OLDID], (5), (4)), (12), (1)), (0))*(2)
END+CASE
WHEN CONVERT([INT], SUBSTRING('11122800'+SUBSTRING([OLDID], (5), (4)), (10), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING('11122800'+SUBSTRING([OLDID], (5), (4)), (10), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING('11122800'+SUBSTRING([OLDID], (5), (4)), (10), (1)), (0))*(2)
END)+CASE
WHEN CONVERT([INT], SUBSTRING('11122800'+SUBSTRING([OLDID], (5), (4)), (8), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING('11122800'+SUBSTRING([OLDID], (5), (4)), (8), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING('11122800'+SUBSTRING([OLDID], (5), (4)), (8), (1)), (0))*(2)
END)+CONVERT([INT], SUBSTRING('11122800'+SUBSTRING([OLDID], (5), (4)), (11), (1)), (0)))+CONVERT([INT], SUBSTRING('11122800'+SUBSTRING([OLDID], (5), (4)), (9), (1)), (0)))+CONVERT([INT], SUBSTRING('11122800'+SUBSTRING([OLDID], (5), (4)), (7), (1)), (0)))+(17), (0)), (2), (1)), (0))-(10), (0)), (1))
WHEN [ID_NUM] LIKE 'PC%'
THEN('111220'+SUBSTRING([ID_NUM], (6), (6)))+RIGHT(CONVERT([VARCHAR], CONVERT([INT], SUBSTRING(CONVERT([VARCHAR], (((((CASE
WHEN CONVERT([INT], SUBSTRING('111220'+SUBSTRING([ID_NUM], (6), (6)), (12), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING('111220'+SUBSTRING([ID_NUM], (6), (6)), (12), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING('111220'+SUBSTRING([ID_NUM], (6), (6)), (12), (1)), (0))*(2)
END+CASE
WHEN CONVERT([INT], SUBSTRING('111220'+SUBSTRING([ID_NUM], (6), (6)), (10), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING('111220'+SUBSTRING([ID_NUM], (6), (6)), (10), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING('111220'+SUBSTRING([ID_NUM], (6), (6)), (10), (1)), (0))*(2)
END)+CASE
WHEN CONVERT([INT], SUBSTRING('111220'+SUBSTRING([ID_NUM], (6), (6)), (8), (1)), (0))*(2) > (9)
THEN CONVERT([INT], SUBSTRING('111220'+SUBSTRING([ID_NUM], (6), (6)), (8), (1)), (0))*(2)-(9)
ELSE CONVERT([INT], SUBSTRING('111220'+SUBSTRING([ID_NUM], (6), (6)), (8), (1)), (0))*(2)
END)+CONVERT([INT], SUBSTRING('111220'+SUBSTRING([ID_NUM], (6), (6)), (11), (1)), (0)))+CONVERT([INT], SUBSTRING('111220'+SUBSTRING([ID_NUM], (6), (6)), (9), (1)), (0)))+CONVERT([INT], SUBSTRING('111220'+SUBSTRING([ID_NUM], (6), (6)), (7), (1)), (0)))+(10), (0)), (2), (1)), (0))-(10), (0)), (1))
END) as YOUR_OUTPUT
FROM #sometable;
DROP TABLE #sometable
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 22, 2016 at 3:55 pm
ADP-000077S or ADP-000077F
So are the 'S' and 'F' for Staff and Faculty?
I would also take a look and see if ADP-000077 exists to begin with.
Your issues might run a bit deeper than just the computed column.
Like mentioned before, it's not a good practice to store multiple things in one field.
This adds complexity most times, and parsing strategies tend to break down.
Better design would be Item Number and Categories for additional descriptive properties.
And fields designed to hold data of defined length and type.
You could add logic to look for the suffix, then handle those accordingly.
September 23, 2016 at 2:19 am
The problem is not with ADP-000077S or ADP-000077F but with AE-000077S and AE-000077F
Unless the format of AE is different to ADP then the AE substrings should be 4,6 not 3,20
What is the format of AE data?
Is it fixed format?
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply