Need help understanding a query involving "case" and "select into" clauses

Question

I am puzzled by the output of the following query:

select 
  'Eq Type' =
  case
    when 
      substring([Eq Type], 1, charindex('-', [Eq Type]) - 1) = 
      substring([Eq Type], charindex('-', [Eq Type]) + 1, len([Eq Type])) 
    then
      substring([Eq Type], 1, charindex('-', [Eq Type]) - 1)
    when
      substring([Eq Type], 1, charindex('-', [Eq Type]) - 1) <> 
      substring([Eq Type], charindex('-', [Eq Type]) + 1, len([Eq Type]))
    then
      replace([Eq Type], '-', '')
    else
      null
  end,
  'Failure_Class' =
  case
    when charindex('-', [Eq Type]) <> 0 and
      (substring([Eq Type], 1, charindex('-', [Eq Type]) - 1) = 
      substring([Eq Type], charindex('-', [Eq Type]) + 1, len([Eq Type])))
    then
      substring([Eq Type], 1, charindex('-', [Eq Type]) - 1)
    when charindex('-', [Eq Type]) <> 0 and
      (substring([Eq Type], 1, charindex('-', [Eq Type]) - 1) <>
      substring([Eq Type], charindex('-', [Eq Type]) + 1, len([Eq Type])))
    then
      substring([Eq Type], 1, charindex('-', [Eq Type]) - 1) +
      '\' +
      replace([Eq Type], '-', '')
    when CHARINDEX('-', [Eq Type]) = 0
    then 
      Failure_Class
    else
      null
  end
from dbo.Location

The Location table contains 25385 records but only 8157 records are returned. Why are records filtered out?
When I try to add into dbo.ModifiedLocation to the above query, it fails with the following error: "Invalid length parameter passed to the LEFT or SUBSTRING function". The message is pretty descriptive but why this error is raised when I add the into clause? Why is the query executed normally without the into clause?

Edit I would like to explain what I am trying to achieve. The original dataset has two columns I am interested in, Eq Type and Failure_Class. The data looks as follows:

Eq Type, Failure_Class
ACCU-ACCU, ACCU
AUX-AUX, AUX
VA-BA, VA
VA-CH, VA
IP-LS, IP
null, null
VE, VE
JB, JB
VA, null

Because the data is maintained by hand, it's inconsistent. I need the data in the following format, in order to be able to import it into their asset management system.

Eq Type, Failure_Class
ACCU, ACCU
AUX, AUX
VABA, VA\VABA
VACH, VA\VACH
IPLS, IP\IPLS
null, null
VE, VE
JB, JB
VA, VA

Edit 2 It seems that I have found the problem. I was running this query in the freeware version of Toad 5.6 for SQL Server. When I have switched to SSMS and removed the "into dbo.ModifiedLocation" the query raised the familiar "Invalid length parameter passed to the LEFT or SUBSTRING function" error. This answers my second question. I am guessing that if I resolve this error, I'll get the desired output. Thank you for your help.

Can you post the data or a sample of the data from dbo.Location and the result you get when running the statement to allow someone to help — Trevor North, Dec 31 '11 at 00:57
@Trevor Done. Hopefully, it clarifies what I am trying to accomplish. — Sergey Koulikov, Dec 31 '11 at 01:32

score 3 · Accepted Answer · answered Dec 31 '11 at 00:35

3

To insert into an existing table, you need INSERT INTO dbo.ModifiedLocation SELECT ...

The SELECT ... INTO dbo.ModifiedLocation FROM ... syntax is for creating the table as well as inserting into it.

As for the number of records returned. Unless you have a JOIN, or a WHERE clause, or a GROUP BY, or a DISTINCT, this should return exactly the same number of records as exists in the source table.

Is this exactly the query you are running?

answered Dec 31 '11 at 00:35

MatBailie

83,401
18
103
137

Yes, it's exactly the query I am running. I would like to create a new table ModifiedLocation and insert the modified data into it. It's an ETL exercise but I don't have access to SQL Server integration services which is why I am doing it "by hand". – Sergey Koulikov Dec 31 '11 at 01:10

Need help understanding a query involving "case" and "select into" clauses

1 Answers1