Syntax help needed for UPDATE Statement

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  June 2, 2016 at 12:37 pm

  #310922

  Need help with the following UPDATE statement.

  The objective here is to update one field with a | delimited statement using the rest of the fields.

  This works out well. Look at the last field ( ZiPCode ).

  Help me reconstruct this SQL so that if the ZipCode was NULL or NOT NULL there is no need to have a | at the end.

  I mean if Zipcode was not null then just append the value of Zipcode. But if ZipCode was NULL then nothing should be appended ( concatenated )

  Please Help

  Update [FirstRecovery].[FRGClaims]

  CASE WHEN PlanCode IS NOT NULL THEN PlanCode + '|' ELSE '|' END

  + CASE WHEN CompanyCode IS NOT NULL THEN CompanyCode + '|' ELSE '|' END

  + CASE WHEN GroupName IS NOT NULL THEN GroupName + '|' ELSE '|' END

  + CASE WHEN GroupCode IS NOT NULL THEN GroupCode + '|' ELSE '|' END

  + CASE WHEN MemberNbr IS NOT NULL THEN MemberNbr + '|' ELSE '|' END

  + CASE WHEN SubscriberFirstName IS NOT NULL THEN SubscriberFirstName + '|' ELSE '|' END

  + CASE WHEN SubscriberMiddleInitial IS NOT NULL THEN SubscriberMiddleInitial + '|' ELSE '|' END

  + CASE WHEN SubscriberLastName IS NOT NULL THEN SubscriberLastName + '|' ELSE '|' END

  + CASE WHEN MemberAddress1 IS NOT NULL THEN MemberAddress1 + '|' ELSE '|' END

  + CASE WHEN MemberAddress2 IS NOT NULL THEN MemberAddress2 + '|' ELSE '|' END

  + CASE WHEN MemberCity IS NOT NULL THEN MemberCity + '|' ELSE '|' END

  + CASE WHEN MemberState IS NOT NULL THEN MemberState + '|' ELSE '|' END

  + CASE WHEN MemberZipCode IS NOT NULL THEN MemberZipCode + '|' ELSE '|' END

  + CASE WHEN MemberHomePhoneNbr IS NOT NULL THEN MemberHomePhoneNbr + '|' ELSE '|' END

  + CASE WHEN SubscriberSSN IS NOT NULL THEN SubscriberSSN + '|' ELSE '|' END

  + CASE WHEN PatientID IS NOT NULL THEN PatientID + '|' ELSE '|' END

  + CASE WHEN MembersFirstName IS NOT NULL THEN MembersFirstName + '|' ELSE '|' END

  + CASE WHEN MiddleInitial IS NOT NULL THEN MiddleInitial + '|' ELSE '|' END

  + CASE WHEN MembersLastName IS NOT NULL THEN MembersLastName + '|' ELSE '|' END

  + CASE WHEN MemberSocialSecurity IS NOT NULL THEN MemberSocialSecurity + '|' ELSE '|' END

  + CASE WHEN ISDATE(PatBirthDate)=1 THEN convert( char(10) , PatBirthDate, 101 ) + '|' ELSE '|' END

  + CASE WHEN MemberSex IS NOT NULL THEN MemberSex + '|' ELSE '|' END

  + CASE WHEN CLAIMNUM IS NOT NULL THEN CLAIMNUM + '|' ELSE '|' END

  + CASE WHEN CLAIMDTLNUM IS NOT NULL THEN CLAIMDTLNUM + '|' ELSE '|' END

  + CASE WHEN ISDATE(SpecificDOS)=1 THEN convert( char(10) , SpecificDOS, 101 ) + '|' ELSE '|' END

  + CASE WHEN ISDATE(DischargeDate)=1 THEN convert( char(10) , DischargeDate, 101 ) + '|' ELSE '|' END

  + CASE WHEN ISDATE(CheckDate)=1 THEN convert( char(10) , CheckDate, 101 ) + '|' ELSE '|' END

  + CASE WHEN Diagnosis1 IS NOT NULL THEN Diagnosis1 + '|' ELSE '|' END

  + CASE WHEN Diagnosis2 IS NOT NULL THEN Diagnosis2 + '|' ELSE '|' END

  + CASE WHEN Diagnosis3 IS NOT NULL THEN Diagnosis3 + '|' ELSE '|' END

  + CASE WHEN Diagnosis4 IS NOT NULL THEN Diagnosis4 + '|' ELSE '|' END

  + CASE WHEN Diagnosis5 IS NOT NULL THEN Diagnosis5 + '|' ELSE '|' END

  + CASE WHEN PCDCD IS NOT NULL THEN PCDCD + '|' ELSE '|' END

  + CASE WHEN BilledAmt IS NOT NULL THEN CAST(BilledAmt as VARCHAR ) + '|' ELSE '|' END

  + CASE WHEN NetAmt IS NOT NULL THEN CAST(NetAmt as VARCHAR ) + '|' ELSE '|' END

  + CASE WHEN ProviderNbr IS NOT NULL THEN ProviderNbr + '|' ELSE '|' END

  + CASE WHEN ProviderName IS NOT NULL THEN ProviderName + '|' ELSE '|' END

  + CASE WHEN Address1 IS NOT NULL THEN Address1 + '|' ELSE '|' END

  + CASE WHEN Address2 IS NOT NULL THEN Address2 + '|' ELSE '|' END

  + CASE WHEN City IS NOT NULL THEN City + '|' ELSE '|' END

  + CASE WHEN State IS NOT NULL THEN State + '|' ELSE '|' END

  + CASE WHEN ZipCode IS NULL THEN '|' ELSE LTRIM(RTRIM(ZipCode)) + '|' END

  Where

  OutputFileLine IS NULL;

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 2, 2016 at 12:43 pm

  #1882900

  mw112009 (6/2/2016)

  ---

  Need help with the following UPDATE statement.

  The objective here is to update one field with a | delimited statement using the rest of the fields.

  This works out well. Look at the last field ( ZiPCode ).

  Help me reconstruct this SQL so that if the ZipCode was NULL or NOT NULL there is no need to have a | at the end.

  I mean if Zipcode was not null then just append the value of Zipcode. But if ZipCode was NULL then nothing should be appended ( concatenated )

  Please Help

  +CASE

  WHEN ZipCode IS NOT NULL -- Zipcode was not null then just append the value of Zipcode

  THEN LTRIM(RTRIM(ZipCode))

  ELSE '' --if ZipCode was NULL then nothing should be appended

  END

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• FridayNightGiant

  SSCertifiable

  Points: 7151

  More actions

  June 2, 2016 at 12:52 pm

  #1882904

  Also as you are using 2012 you could use concat to generate the string. You wouldn't have to worry about checking for null values then.

  DECLARE @FRGClaims TABLE (

  PlanCode VARCHAR(5)

  , CompanyCode VARCHAR(20)

  , GroupName VARCHAR(20)

  ,ZipCode VARCHAR(10)

  );

  INSERT INTO @FRGClaims

  (PlanCode, CompanyCode, GroupName, ZipCode)

  VALUES ('123', NULL, 'ABC','90210'),

  ('256', 'xyz', 'DEF', Null);

  SELECT CONCAT(PlanCode, '|', CompanyCode, '|', GroupName, '|', ZipCode)

  FROM @FRGClaims;

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 2, 2016 at 1:04 pm

  #1882918

  Have you ever heard of ISNULL() or COALESCE()?

  Much easier to write than a CASE statement.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  June 2, 2016 at 1:36 pm

  #1882930

  I actually prefer to use the XML concatenation method, which also automatically handles NULL values, and--unlike CONCAT--works in SQL2008, and is easier to check that you're getting the correct values.

  Update [FirstRecovery].[FRGClaims]

  SET OutputFileLine = (

  SELECT

  PlanCode AS [text()], '|'

  ,CompanyCode AS [text()], '|'

  ,GroupName AS [text()], '|'

  ,GroupCode AS [text()], '|'

  ,MemberNbr AS [text()], '|'

  ,SubscriberFirstName AS [text()], '|'

  ,SubscriberMiddleInitial AS [text()], '|'

  ,SubscriberLastName AS [text()], '|'

  ,MemberAddress1 AS [text()], '|'

  ,MemberAddress2 AS [text()], '|'

  ,MemberCity AS [text()], '|'

  ,MemberState AS [text()], '|'

  ,MemberZipCode AS [text()], '|'

  ,MemberHomePhoneNbr AS [text()], '|'

  ,SubscriberSSN AS [text()], '|'

  ,PatientID AS [text()], '|'

  ,MembersFirstName AS [text()], '|'

  ,MiddleInitial AS [text()], '|'

  ,MembersLastName AS [text()], '|'

  ,MemberSocialSecurity AS [text()], '|'

  ,convert( char(10) , PatBirthDate, 101 ) AS [text()], '|'

  ,MemberSex AS [text()], '|'

  ,CLAIMNUM AS [text()], '|'

  ,CLAIMDTLNUM AS [text()], '|'

  ,convert( char(10) , SpecificDOS, 101 ) AS [text()], '|'

  ,convert( char(10) , DischargeDate, 101 ) AS [text()], '|'

  ,convert( char(10) , CheckDate, 101 ) AS [text()], '|'

  ,Diagnosis1 AS [text()], '|'

  ,Diagnosis2 AS [text()], '|'

  ,Diagnosis3 AS [text()], '|'

  ,Diagnosis4 AS [text()], '|'

  ,Diagnosis5 AS [text()], '|'

  ,PCDCD AS [text()], '|'

  ,CAST(BilledAmt as VARCHAR ) AS [text()], '|'

  ,CAST(NetAmt as VARCHAR ) AS [text()], '|'

  ,ProviderNbr AS [text()], '|'

  ,ProviderName AS [text()], '|'

  ,Address1 AS [text()], '|'

  ,Address2 AS [text()], '|'

  ,City AS [text()], '|'

  ,State AS [text()], '|'

  ,ZipCode AS [text()]

  FOR XML PATH('')

  )

  WHERE OutputFileLine IS NULL;

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  June 2, 2016 at 1:42 pm

  #1882938

  Good,

  I was also asked to use the LTRIM(RTRIM( around the fields so that there be no spaces around. How can we handle that ?

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  June 2, 2016 at 2:37 pm

  #1882949

  mw112009 (6/2/2016)

  ---

  Good,

  I was also asked to use the LTRIM(RTRIM( around the fields so that there be no spaces around. How can we handle that ?

  [rant on]

  If you don't want leading/trailing spaces you should trim them when inserting them into the table, not when selecting them from the table.

  [/rant off].

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 2, 2016 at 2:58 pm

  #1882952

  drew.allen (6/2/2016)

  ---

  mw112009 (6/2/2016)

  ---

  Good,

  I was also asked to use the LTRIM(RTRIM( around the fields so that there be no spaces around. How can we handle that ?

  [rant on]

  If you don't want leading/trailing spaces you should trim them when inserting them into the table, not when selecting them from the table.

  [/rant off].

  Drew

  +1000! Do it up front rather than doing it every time you pull the data.