#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    4
    Rep Power
    0

    Creating IF statement


    I need a SQL statement to do this:

    Parse first 7 digits of YTD_2011_QB.Account field of source to get the account number. If the account number falls in the range (6727000, 6745000, 6748000, 6768100, 6787000, 6790000, 6863000, 6864000) then populate ZLGLACCT with the concatenation of first 7 digits of YTD_2011_QB.csv.account and last 10 characters of YTD_2011_QB.csv.class field. Else, Populate ZLGLACCT with first 7 digit of YTD_2011_QB.account
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    You can use this in an update statement.
    Code:
    CASE 
    	WHEN substring(YTD_2011_QB.Account,1,7)  IN (6727000, 6745000, 6748000, 6768100, 6787000, 6790000, 6863000, 6864000) 
    		THEN substring(YTD_2011_QB.Account,1,7) + ZLGLACCT + substring(YTD_2011_QB.csv.class,??,10)
    		ELSE substring(YTD_2011_QB.account,1,7)
    END
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by Vomster
    You can use this in an update statement.
    Code:
    CASE 
    	WHEN substring(YTD_2011_QB.Account,1,7)  IN (6727000, 6745000, 6748000, 6768100, 6787000, 6790000, 6863000, 6864000) 
    		THEN substring(YTD_2011_QB.Account,1,7) + ZLGLACCT + substring(YTD_2011_QB.csv.class,??,10)
    		ELSE substring(YTD_2011_QB.account,1,7)
    END
    Thanks a lot!!

    I did and got this message

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'CASE'.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by mark1906
    Incorrect syntax near the keyword 'CASE'.
    show the entire query, please
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo