CODEWITHSUNDEEP
sqlsql-serverif-statementcase
Gregory Brauninger
Gregory Brauninger

Reputation: 75

Case Blank Multiple Conditions

Can anyone guide me in the right direction here. I receive a big text file with multiple lines of what looks like gibberish. For instance, here is one of the lines:

DETDD20160100388530007400000015768652700000000074433902        00000162667905028000000200020160229011750490157     1606056379591109995050                  435601530A          H19618010000000000{0000013962{0000013962{000000201601D142

Now my job is to use an SSIS package that calls a stored procedure to make sense of this and eventually put it into an excel spreadsheet. These long lines of characters begin with 5 letters which I use to determine the final output. I have a guide to tell me which characters make up the data in each column but I need help with the code. Here is what I got so far. 

Select *
insert into [dbo].[ContractDisputeResolution]
Case WHEN SUBSTRING(col001, 1,5) = 'DETDD'
     THEN (SUBSTRING(col001, 6,6) as Current_Reporting_Period,
          SUBSTRING(col001, 12,7) as Sequence_Number,
          SUBSTRING(col001, 19,8) as DDPS_System_Date,
          SUBSTRING(col001, 27,32) as DDPS_System_Time,
          SUBSTRING(col001, 33,37) as File_ID_,
          SUBSTRING(col001, 38,42) as Sub_Contract_No,
          SUBSTRING(col001, 43-250) as Filler)
END
FROM [dbo].[ContractDisputeResolutionSTAGING]

----------------------------------------------------------

Select *
insert into [dbo].[ContractDisputeResolution]
Case WHEN SUBSTRING(col001, 1,5) = 'TAMPT'
(insert more rules here)

I am getting all kind of Syntax errors and don't know if I should be even using a Case statement. Can anyone help me set this up a little better or point me in the right direction?

Thanks, Greg

Upvotes: 0

Views: 54

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

This is not valid SQL:

Select *
insert into [dbo].[ContractDisputeResolution]

Simply remove the Select *. I would recommend that you list the columns for the insert.

And then the case is all wrong too. I think you intend:

insert into [dbo].[ContractDisputeResolution](Current_Reporting_Period, Sequence_Number, DDPS_System_Date, DDPS_System_Time, DDPS_System_Time, Sub_Contract_No, Filler)
    select SUBSTRING(col001, 6, 6) as Current_Reporting_Period,
           SUBSTRING(col001, 12, 7) as Sequence_Number,
           SUBSTRING(col001, 19, 8) as DDPS_System_Date,
           SUBSTRING(col001, 27, 32) as DDPS_System_Time,
           SUBSTRING(col001, 33, 37) as DDPS_System_Time,
           SUBSTRING(col001, 38, 42) as Sub_Contract_No,
           SUBSTRING(col001, 43, 250) as Filler
    FROM [dbo].[ContractDisputeResolutionSTAGING]
    WHERE SUBSTRING(col001, 1, 5) = 'DETDD';

I'm guessing what the column names are in the real table, based on the column aliases.

Upvotes: 2

Related Questions