Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: DB2 Case Statment

    Running DB2 8.1 fixpack 5
    Windows 2003 Enterprise Server
    Trying to run a case statment
    Getting Error:
    Code:
     SQL0104N  An unexpected token "END-OF-STATEMENT" was found 
    following "END".  Expected tokens may include:  "JOIN 
    <joined_table>                             ".
    I am trying to run the following case statment:
    Code:
    Update stg_joc_tbl
    Set Sub_Trade = Case
    		When 	-- (PACIFIC TRADE GENERAL):
    			(COUNTRY IN ('588','570','580','582','583','560','559','549','565','557','552',  
    		 		    '538','555','579','566','546','574','561','684','553')
    				AND ULTPORT NOT IN ('MARSHAL ISL','KWAJALEIN')) 
    			OR 
    			-- (PACIFIC RUSSIA):
    			(COUNTRY = 462 AND ULTPORT IN ('ABAKAN','KHABAROVSK','KORSAKOV','MAGADAN',
    							'PETROPAVLOVSK','VLADIVOSTOK','VOSTOCHNY',
    							'YUZHNO SAKHAL','YUZHNO SAKHALINSK'))
    		Then 'PACIFIC'
    		When 	-- (WEST MED GENERAL TRADE):
    			COUNTRY IN ('471','735','733','469','467','759','475') 
    			OR 
    			(COUNTRY = '427' AND ULTCODE IN ('42776','42782')) 
    			OR 
    			-- (WEST MED FRANCE):
    			COUNTRY = '427' 
    			AND (ULTCODE IN ('42776','46905','46961','48945','46931','47527',
    					'50220','46994','27900','47500','47531','47300',
    					'47507','47537','47125','47500','46935','48939',
    					'50801','48452','42782')
    			     OR   
    			    (ULTPORT = 'MONACO'))
    		Then 'MECL-WEST MED'
    		When    -- (ATLANTIC TRADE GENERAL):
    			COUNTRY IN ('101','400','401','403','405','409','412','416','419','421','423','425','428',
    				    '429','433','435','437','441','447','449','451','455') 
    			OR                            
    			-- (ATLANTIC FRANCE):
    			(COUNTRY = '427' 
    			AND ULTCODE NOT IN ('42776','46905','46961','48945','46931','47527','50220',
    					  '46994','27900','47500','47531','47300','47507','47537',
    					  '47125','47500','46935','48939','50801','48452','42782'))               
    			OR
    			-- (ATLANTIC RUSSIA):
    			(COUNTRY = '462' 
    			AND ULTPORT NOT IN ('ABAKAN','KHABAROVSK','KORSAKOV','MAGADAN','PETROPAVLOVSK',
    					    'VLADIVOSTOK','VOSTOCHNY','YUZHNO SAKHAL','YUZHNO SAKHALINSK',
    					    'ARMENIA','YEREVAN','AZERBAIJAN','BAKU','TIMASEVSK','STAVROPOL',
    					    'KRASNODAR','NOVOROSSIYSK','KRIVOJ ROG','YALTA','SIMFEROPOL',
    					    'CHERKASSY','ODESSA','DNEPROPETROVS','ZHDANOV','DONECK',
    					    'BORISPOL','KIEV','ILLYICHEVSK','ILICHEVSK','KREMENCHUG',
    					    'ALMA ATA','ALMATY','KZYL ORDA','KAZAKHSTAN','BISHKEK','OSH',
    					    'KYRGYZSTAN','KULYAB','DUSANBE','TAJIKISTAN','DUSHANBE',
    					    'TADZHIKISTAN','TURKMENISTAN','UZBEKISTAN',
    					    'TIRASPOL','MOLDOVA'))
    		Then 'TRANS-ATLANTIC'
    		When 	-- (OTHER MED GENERAL TRADE):
    			COUNTRY IN ('508','489','729','484','504','479','714','491','485','463','721', 
    				    '723','487','481','472','502','725','473','737') 
    			OR
    			-- (OTHER MED ARMENIA):
    			(COUNTRY = '462' AND ULTPORT IN ('ARMENIA','YEREVAN'))
    			OR
    			-- (OTHER MED AZERBAIJAN):
    			(COUNTRY = '462' AND ULTPORT IN ('AZERBAIJAN','BAKU'))
    			OR
    			-- (OTHER MED MOLDOVA):
    			(COUNTRY IN ('462','485') AND ULTPORT IN ('TIRASPOL','MOLDOVA'))
    			OR
    			-- (OTHER MED RUSSIA):
    			(COUNTRY = '462' AND ULTPORT IN ('TIMASEVSK','STAVROPOL','KRASNODAR','NOVOROSSIYSK'))                             
    			OR
    			-- (OTHER MED UKRAINE):
    			(COUNTRY = '462' AND ULTPORT IN ('KRIVOJ ROG','YALTA','SIMFEROPOL','CHERKASSY',
    							'DNEPROPETROVS','ZHDANOV','DONECK','BORISPOL',
    							'KIEV','ILLYICHEVSK','ILICHEVSK','ODESSA',
    							'KREMENCHUG',
    							-- Moved from MEIP on 03-30-2004
    							'ALMA ATA','KZYL ORDA','KAZAKHSTAN','ALMATY',
    							'BISHKEK','OSH','KYRGYZSTAN','KULYAB','DUSANBE',
    							'TAJIKISTAN','DUSHANBE','TADZHIKISTAN',
    							'TURKMENISTAN','UZBEKISTAN'))
    		Then 'MECL-OTHER MED'
    		When 	(COUNTRY IN ('533','517','520','535','513','511','525','523','518','542',
    				     '521','507','505','568','777','732','531','536','774'))
    		Then 'MECL-MEIP60'
    		When Sline = 'DFFC' 
    		       and (  [Name] in ('DOLE PURCHASING'
    					,'DOLE FRESH FRUIT'
    					,'DOLE FRESH FRUIT INTL LTD'
    					,'DOLE OCEAN LINER'
    					,'PRODUCTORA CARTONERA'
    					,'STANDARD FRUIT STEAMS HI'
    					,'STANDARD FRUITSTEAMSHI'
    					,'STANDARD FRUIT   STEAMSHI'
    					,'STANDARD FRUIT & STEAMSHI') 
    			    or FName in ('DOLE PURCHASING'))
    		Then 'DOLE'
    		When Sline = 'GWFL' 
    		       and (  [Name] in ('CHIQUITA BRANDS'
    					,'CHIQUITA UNIFRUTTI JAPAN'
    					,'CHIQUITA ITALIA S P A'
    					,'CHIQUITA ITALIA SPA'
    					,'MARITROP TRDG') 
    			   or FName = 'CHIQUITA BRANDS')
    		Then 'CHIQUITA'
    		When Sline = 'SOLY' 
    		       and ([Name] = 'SOLTECH' or [FNAME] = 'PARADISE FARMS')
    		Then 'SOLTECH'
    		When Sline = 'NETS' 
    		       and [Name] = 'DEL MONTE'
    		Then 'DEL MONTE'
    		When Sline = 'ECUA' 
    		       and [Name] = 'PACIFIC FRUIT'
    		Then 'PACIFIC FRUIT'
    		When Country in ('232','236','239','241','243','244','245'
    				,'247','248','272','274','277','283','307'
    				,'312','315','317','911')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-CARIB'
    		When Country in ('301','331','333','335','337')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-WCSA'
    		When Country in ('351','353','355','357')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-ECSA'
    		When Country in ('201','205','208','211','215','219','223','225')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-CAMR'
    		When Country in ('738','741','742','744','745','746','747','748','749'
    				,'750','751','752','753','754','755','756','758','760'
    				,'761','762','764','763','765','766') 
    		Then 'AFR-WEST AFRICA'
    		
    		When Country in ('780','785','787','788','789','791','792','793','794','795','796','797','799')
    			OR (Country = '790' and ULTPORT = 'REUNION')
    		Then 'AFR-SOUTH AFRICA'
    		When Country in ('767','769','778','779','783','790') AND ULTPORT != 'REUNION'
    		Then 'AFR-EAST AFRICA'
    		
    		When Country in ('602','604','614','615','622','641','686','951')
    			OR (COUNTRY = '684' AND ULTPORT IN ('MARSHAL ISL','KWAJALEIN'))
    		Then 'OCEANIA'
    		When Country in ('201','205','208','211','215','219','223','225')
    		Then 'NON-STD CENTRAL AMERICA'
    		
    		When Country in ('232','236','241','245','247','248','272','274'
    				,'277','283','307','312','315','317')
    		Then 'NON-STD CARIBBEAN'
    		
    		When Country in ('301','331','333','335','337')
    		Then 'NON-STD W. COAST S. AMERICA'
    		
    		When Country in ('351','353','355','357')
    		Then 'NON-STD E. COAST S. AMERICA'
    				
    		When Country in ('461','462','463','467','469','471','472','473'
    				,'475','479','481','484','485','487','489','491'
    				,'504','508','714','721','723','729','733')
    		Then 'NON-STD MED'
    		
    		When Country in ('533','535','538','542')
    		Then 'NON-STD ISC'
    		
    		When Country in ('511','513','517','518','520','521','523','525')
    		Then 'NON-STD MIDEAST'
    		
    		When Country in ('546','549','552','555','557','559','560','561'
    				,'565','568')
    		Then 'NON-STD SOUTH ASIA'
    		
    		When Country in ('566','570','574','580','582','583','588')
    		Then 'NON-STD EAST ASIA'
    		
    		When Country in ('015','018','101','122','015','016','017','018','025'
    				,'026','035','055','065','066','067','085','090','096'
    				,'124','134','138','140','144','152','243','244','502'
    				,'507','531','536','579','604','614','615','622','641'
    				,'684','686','735','759','760','764','770','777','790'
    				,'911','935','951','999')
    		Then 'NON-STD OTHER'
                                                              Else 'NON-STD OTHER'
    		
          END;
    and Im getting the previous error when i run it. Im very new to db2 but from the help that i have read it seems to me that I am running this correctly. I am confused.
    Any help would be great
    Jim

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by JDionne
    Running DB2 8.1 fixpack 5
    Windows 2003 Enterprise Server
    Trying to run a case statment
    Getting Error:
    Code:
     SQL0104N  An unexpected token "END-OF-STATEMENT" was found 
    following "END".  Expected tokens may include:  "JOIN 
    <joined_table>                             ".
    I am trying to run the following case statment:
    Code:
    Update stg_joc_tbl
    Set Sub_Trade = Case
    		When 	-- (PACIFIC TRADE GENERAL):
    			(COUNTRY IN ('588','570','580','582','583','560','559','549','565','557','552',  
    		 		    '538','555','579','566','546','574','561','684','553')
    				AND ULTPORT NOT IN ('MARSHAL ISL','KWAJALEIN')) 
    			OR 
    			-- (PACIFIC RUSSIA):
    			(COUNTRY = 462 AND ULTPORT IN ('ABAKAN','KHABAROVSK','KORSAKOV','MAGADAN',
    							'PETROPAVLOVSK','VLADIVOSTOK','VOSTOCHNY',
    							'YUZHNO SAKHAL','YUZHNO SAKHALINSK'))
    		Then 'PACIFIC'
    		When 	-- (WEST MED GENERAL TRADE):
    			COUNTRY IN ('471','735','733','469','467','759','475') 
    			OR 
    			(COUNTRY = '427' AND ULTCODE IN ('42776','42782')) 
    			OR 
    			-- (WEST MED FRANCE):
    			COUNTRY = '427' 
    			AND (ULTCODE IN ('42776','46905','46961','48945','46931','47527',
    					'50220','46994','27900','47500','47531','47300',
    					'47507','47537','47125','47500','46935','48939',
    					'50801','48452','42782')
    			     OR   
    			    (ULTPORT = 'MONACO'))
    		Then 'MECL-WEST MED'
    		When    -- (ATLANTIC TRADE GENERAL):
    			COUNTRY IN ('101','400','401','403','405','409','412','416','419','421','423','425','428',
    				    '429','433','435','437','441','447','449','451','455') 
    			OR                            
    			-- (ATLANTIC FRANCE):
    			(COUNTRY = '427' 
    			AND ULTCODE NOT IN ('42776','46905','46961','48945','46931','47527','50220',
    					  '46994','27900','47500','47531','47300','47507','47537',
    					  '47125','47500','46935','48939','50801','48452','42782'))               
    			OR
    			-- (ATLANTIC RUSSIA):
    			(COUNTRY = '462' 
    			AND ULTPORT NOT IN ('ABAKAN','KHABAROVSK','KORSAKOV','MAGADAN','PETROPAVLOVSK',
    					    'VLADIVOSTOK','VOSTOCHNY','YUZHNO SAKHAL','YUZHNO SAKHALINSK',
    					    'ARMENIA','YEREVAN','AZERBAIJAN','BAKU','TIMASEVSK','STAVROPOL',
    					    'KRASNODAR','NOVOROSSIYSK','KRIVOJ ROG','YALTA','SIMFEROPOL',
    					    'CHERKASSY','ODESSA','DNEPROPETROVS','ZHDANOV','DONECK',
    					    'BORISPOL','KIEV','ILLYICHEVSK','ILICHEVSK','KREMENCHUG',
    					    'ALMA ATA','ALMATY','KZYL ORDA','KAZAKHSTAN','BISHKEK','OSH',
    					    'KYRGYZSTAN','KULYAB','DUSANBE','TAJIKISTAN','DUSHANBE',
    					    'TADZHIKISTAN','TURKMENISTAN','UZBEKISTAN',
    					    'TIRASPOL','MOLDOVA'))
    		Then 'TRANS-ATLANTIC'
    		When 	-- (OTHER MED GENERAL TRADE):
    			COUNTRY IN ('508','489','729','484','504','479','714','491','485','463','721', 
    				    '723','487','481','472','502','725','473','737') 
    			OR
    			-- (OTHER MED ARMENIA):
    			(COUNTRY = '462' AND ULTPORT IN ('ARMENIA','YEREVAN'))
    			OR
    			-- (OTHER MED AZERBAIJAN):
    			(COUNTRY = '462' AND ULTPORT IN ('AZERBAIJAN','BAKU'))
    			OR
    			-- (OTHER MED MOLDOVA):
    			(COUNTRY IN ('462','485') AND ULTPORT IN ('TIRASPOL','MOLDOVA'))
    			OR
    			-- (OTHER MED RUSSIA):
    			(COUNTRY = '462' AND ULTPORT IN ('TIMASEVSK','STAVROPOL','KRASNODAR','NOVOROSSIYSK'))                             
    			OR
    			-- (OTHER MED UKRAINE):
    			(COUNTRY = '462' AND ULTPORT IN ('KRIVOJ ROG','YALTA','SIMFEROPOL','CHERKASSY',
    							'DNEPROPETROVS','ZHDANOV','DONECK','BORISPOL',
    							'KIEV','ILLYICHEVSK','ILICHEVSK','ODESSA',
    							'KREMENCHUG',
    							-- Moved from MEIP on 03-30-2004
    							'ALMA ATA','KZYL ORDA','KAZAKHSTAN','ALMATY',
    							'BISHKEK','OSH','KYRGYZSTAN','KULYAB','DUSANBE',
    							'TAJIKISTAN','DUSHANBE','TADZHIKISTAN',
    							'TURKMENISTAN','UZBEKISTAN'))
    		Then 'MECL-OTHER MED'
    		When 	(COUNTRY IN ('533','517','520','535','513','511','525','523','518','542',
    				     '521','507','505','568','777','732','531','536','774'))
    		Then 'MECL-MEIP60'
    		When Sline = 'DFFC' 
    		       and (  [Name] in ('DOLE PURCHASING'
    					,'DOLE FRESH FRUIT'
    					,'DOLE FRESH FRUIT INTL LTD'
    					,'DOLE OCEAN LINER'
    					,'PRODUCTORA CARTONERA'
    					,'STANDARD FRUIT STEAMS HI'
    					,'STANDARD FRUITSTEAMSHI'
    					,'STANDARD FRUIT   STEAMSHI'
    					,'STANDARD FRUIT & STEAMSHI') 
    			    or FName in ('DOLE PURCHASING'))
    		Then 'DOLE'
    		When Sline = 'GWFL' 
    		       and (  [Name] in ('CHIQUITA BRANDS'
    					,'CHIQUITA UNIFRUTTI JAPAN'
    					,'CHIQUITA ITALIA S P A'
    					,'CHIQUITA ITALIA SPA'
    					,'MARITROP TRDG') 
    			   or FName = 'CHIQUITA BRANDS')
    		Then 'CHIQUITA'
    		When Sline = 'SOLY' 
    		       and ([Name] = 'SOLTECH' or [FNAME] = 'PARADISE FARMS')
    		Then 'SOLTECH'
    		When Sline = 'NETS' 
    		       and [Name] = 'DEL MONTE'
    		Then 'DEL MONTE'
    		When Sline = 'ECUA' 
    		       and [Name] = 'PACIFIC FRUIT'
    		Then 'PACIFIC FRUIT'
    		When Country in ('232','236','239','241','243','244','245'
    				,'247','248','272','274','277','283','307'
    				,'312','315','317','911')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-CARIB'
    		When Country in ('301','331','333','335','337')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-WCSA'
    		When Country in ('351','353','355','357')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-ECSA'
    		When Country in ('201','205','208','211','215','219','223','225')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-CAMR'
    		When Country in ('738','741','742','744','745','746','747','748','749'
    				,'750','751','752','753','754','755','756','758','760'
    				,'761','762','764','763','765','766') 
    		Then 'AFR-WEST AFRICA'
    		
    		When Country in ('780','785','787','788','789','791','792','793','794','795','796','797','799')
    			OR (Country = '790' and ULTPORT = 'REUNION')
    		Then 'AFR-SOUTH AFRICA'
    		When Country in ('767','769','778','779','783','790') AND ULTPORT != 'REUNION'
    		Then 'AFR-EAST AFRICA'
    		
    		When Country in ('602','604','614','615','622','641','686','951')
    			OR (COUNTRY = '684' AND ULTPORT IN ('MARSHAL ISL','KWAJALEIN'))
    		Then 'OCEANIA'
    		When Country in ('201','205','208','211','215','219','223','225')
    		Then 'NON-STD CENTRAL AMERICA'
    		
    		When Country in ('232','236','241','245','247','248','272','274'
    				,'277','283','307','312','315','317')
    		Then 'NON-STD CARIBBEAN'
    		
    		When Country in ('301','331','333','335','337')
    		Then 'NON-STD W. COAST S. AMERICA'
    		
    		When Country in ('351','353','355','357')
    		Then 'NON-STD E. COAST S. AMERICA'
    				
    		When Country in ('461','462','463','467','469','471','472','473'
    				,'475','479','481','484','485','487','489','491'
    				,'504','508','714','721','723','729','733')
    		Then 'NON-STD MED'
    		
    		When Country in ('533','535','538','542')
    		Then 'NON-STD ISC'
    		
    		When Country in ('511','513','517','518','520','521','523','525')
    		Then 'NON-STD MIDEAST'
    		
    		When Country in ('546','549','552','555','557','559','560','561'
    				,'565','568')
    		Then 'NON-STD SOUTH ASIA'
    		
    		When Country in ('566','570','574','580','582','583','588')
    		Then 'NON-STD EAST ASIA'
    		
    		When Country in ('015','018','101','122','015','016','017','018','025'
    				,'026','035','055','065','066','067','085','090','096'
    				,'124','134','138','140','144','152','243','244','502'
    				,'507','531','536','579','604','614','615','622','641'
    				,'684','686','735','759','760','764','770','777','790'
    				,'911','935','951','999')
    		Then 'NON-STD OTHER'
                                                              Else 'NON-STD OTHER'
    		
          END;
    and Im getting the previous error when i run it. Im very new to db2 but from the help that i have read it seems to me that I am running this correctly. I am confused.
    Any help would be great
    Jim
    I think it's the comments.... "--" is only valid at the start of a line.

    I tried a simple test without the comments:

    update caseupd
    set b = case when
    a = 'test' then 'test' else 'notest' end;
    -> works!

    update caseupd
    set b = case when -- test
    a = 'test' then 'test' else 'notest' end;
    -> SQL0104N

    Very misleading error if I'm right.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    I have removed all of the --
    Code:
    connect to namdwh user conjdi using Jenn4me11;
    Update stg_joc_tbl
    Set Sub_Trade = Case
    		When 	
    			(COUNTRY IN ('588','570','580','582','583','560','559','549','565','557','552',  
    		 		    '538','555','579','566','546','574','561','684','553')
    				AND ULTPORT NOT IN ('MARSHAL ISL','KWAJALEIN')) 
    			OR 
    			
    			(COUNTRY = 462 AND ULTPORT IN ('ABAKAN','KHABAROVSK','KORSAKOV','MAGADAN',
    							'PETROPAVLOVSK','VLADIVOSTOK','VOSTOCHNY',
    							'YUZHNO SAKHAL','YUZHNO SAKHALINSK'))
    		Then 'PACIFIC'
    		When 	
    			COUNTRY IN ('471','735','733','469','467','759','475') 
    			OR 
    			(COUNTRY = '427' AND ULTCODE IN ('42776','42782')) 
    			OR 
    			
    			COUNTRY = '427' 
    			AND (ULTCODE IN ('42776','46905','46961','48945','46931','47527',
    					'50220','46994','27900','47500','47531','47300',
    					'47507','47537','47125','47500','46935','48939',
    					'50801','48452','42782')
    			     OR   
    			    (ULTPORT = 'MONACO'))
    		Then 'MECL-WEST MED'
    		When    
    			COUNTRY IN ('101','400','401','403','405','409','412','416','419','421','423','425','428',
    				    '429','433','435','437','441','447','449','451','455') 
    			OR                            
    			
    			(COUNTRY = '427' 
    			AND ULTCODE NOT IN ('42776','46905','46961','48945','46931','47527','50220',
    					  '46994','27900','47500','47531','47300','47507','47537',
    					  '47125','47500','46935','48939','50801','48452','42782'))               
    			OR
    			
    			(COUNTRY = '462' 
    			AND ULTPORT NOT IN ('ABAKAN','KHABAROVSK','KORSAKOV','MAGADAN','PETROPAVLOVSK',
    					    'VLADIVOSTOK','VOSTOCHNY','YUZHNO SAKHAL','YUZHNO SAKHALINSK',
    					    'ARMENIA','YEREVAN','AZERBAIJAN','BAKU','TIMASEVSK','STAVROPOL',
    					    'KRASNODAR','NOVOROSSIYSK','KRIVOJ ROG','YALTA','SIMFEROPOL',
    					    'CHERKASSY','ODESSA','DNEPROPETROVS','ZHDANOV','DONECK',
    					    'BORISPOL','KIEV','ILLYICHEVSK','ILICHEVSK','KREMENCHUG',
    					    'ALMA ATA','ALMATY','KZYL ORDA','KAZAKHSTAN','BISHKEK','OSH',
    					    'KYRGYZSTAN','KULYAB','DUSANBE','TAJIKISTAN','DUSHANBE',
    					    'TADZHIKISTAN','TURKMENISTAN','UZBEKISTAN',
    					    'TIRASPOL','MOLDOVA'))
    		Then 'TRANS-ATLANTIC'
    		When 	
    			COUNTRY IN ('508','489','729','484','504','479','714','491','485','463','721', 
    				    '723','487','481','472','502','725','473','737') 
    			OR
    			
    			(COUNTRY = '462' AND ULTPORT IN ('ARMENIA','YEREVAN'))
    			OR
    			
    			(COUNTRY = '462' AND ULTPORT IN ('AZERBAIJAN','BAKU'))
    			OR
    			
    			(COUNTRY IN ('462','485') AND ULTPORT IN ('TIRASPOL','MOLDOVA'))
    			OR
    			
    			(COUNTRY = '462' AND ULTPORT IN ('TIMASEVSK','STAVROPOL','KRASNODAR','NOVOROSSIYSK'))                             
    			OR
    			
    			(COUNTRY = '462' AND ULTPORT IN ('KRIVOJ ROG','YALTA','SIMFEROPOL','CHERKASSY',
    							'DNEPROPETROVS','ZHDANOV','DONECK','BORISPOL',
    							'KIEV','ILLYICHEVSK','ILICHEVSK','ODESSA',
    							'KREMENCHUG',
    							
    							'ALMA ATA','KZYL ORDA','KAZAKHSTAN','ALMATY',
    							'BISHKEK','OSH','KYRGYZSTAN','KULYAB','DUSANBE',
    							'TAJIKISTAN','DUSHANBE','TADZHIKISTAN',
    							'TURKMENISTAN','UZBEKISTAN'))
    		Then 'MECL-OTHER MED'
    		When 	(COUNTRY IN ('533','517','520','535','513','511','525','523','518','542',
    				     '521','507','505','568','777','732','531','536','774'))
    		Then 'MECL-MEIP60'
    		When Sline = 'DFFC' 
    		       and (  [Name] in ('DOLE PURCHASING'
    					,'DOLE FRESH FRUIT'
    					,'DOLE FRESH FRUIT INTL LTD'
    					,'DOLE OCEAN LINER'
    					,'PRODUCTORA CARTONERA'
    					,'STANDARD FRUIT STEAMS HI'
    					,'STANDARD FRUITSTEAMSHI'
    					,'STANDARD FRUIT   STEAMSHI'
    					,'STANDARD FRUIT & STEAMSHI') 
    			    or FName in ('DOLE PURCHASING'))
    		Then 'DOLE'
    		When Sline = 'GWFL' 
    		       and (  [Name] in ('CHIQUITA BRANDS'
    					,'CHIQUITA UNIFRUTTI JAPAN'
    					,'CHIQUITA ITALIA S P A'
    					,'CHIQUITA ITALIA SPA'
    					,'MARITROP TRDG') 
    			   or FName = 'CHIQUITA BRANDS')
    		Then 'CHIQUITA'
    		When Sline = 'SOLY' 
    		       and ([Name] = 'SOLTECH' or [FNAME] = 'PARADISE FARMS')
    		Then 'SOLTECH'
    		When Sline = 'NETS' 
    		       and [Name] = 'DEL MONTE'
    		Then 'DEL MONTE'
    		When Sline = 'ECUA' 
    		       and [Name] = 'PACIFIC FRUIT'
    		Then 'PACIFIC FRUIT'
    		When Country in ('232','236','239','241','243','244','245'
    				,'247','248','272','274','277','283','307'
    				,'312','315','317','911')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-CARIB'
    		When Country in ('301','331','333','335','337')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-WCSA'
    		When Country in ('351','353','355','357')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-ECSA'
    		When Country in ('201','205','208','211','215','219','223','225')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-CAMR'
    		When Country in ('738','741','742','744','745','746','747','748','749'
    				,'750','751','752','753','754','755','756','758','760'
    				,'761','762','764','763','765','766') 
    		Then 'AFR-WEST AFRICA'
    		
    		When Country in ('780','785','787','788','789','791','792','793','794','795','796','797','799')
    			OR (Country = '790' and ULTPORT = 'REUNION')
    		Then 'AFR-SOUTH AFRICA'
    		When Country in ('767','769','778','779','783','790') AND ULTPORT != 'REUNION'
    		Then 'AFR-EAST AFRICA'
    		
    		When Country in ('602','604','614','615','622','641','686','951')
    			OR (COUNTRY = '684' AND ULTPORT IN ('MARSHAL ISL','KWAJALEIN'))
    		Then 'OCEANIA'
    		When Country in ('201','205','208','211','215','219','223','225')
    		Then 'NON-STD CENTRAL AMERICA'
    		
    		When Country in ('232','236','241','245','247','248','272','274'
    				,'277','283','307','312','315','317')
    		Then 'NON-STD CARIBBEAN'
    		
    		When Country in ('301','331','333','335','337')
    		Then 'NON-STD W. COAST S. AMERICA'
    		
    		When Country in ('351','353','355','357')
    		Then 'NON-STD E. COAST S. AMERICA'
    				
    		When Country in ('461','462','463','467','469','471','472','473'
    				,'475','479','481','484','485','487','489','491'
    				,'504','508','714','721','723','729','733')
    		Then 'NON-STD MED'
    		
    		When Country in ('533','535','538','542')
    		Then 'NON-STD ISC'
    		
    		When Country in ('511','513','517','518','520','521','523','525')
    		Then 'NON-STD MIDEAST'
    		
    		When Country in ('546','549','552','555','557','559','560','561'
    				,'565','568')
    		Then 'NON-STD SOUTH ASIA'
    		
    		When Country in ('566','570','574','580','582','583','588')
    		Then 'NON-STD EAST ASIA'
    		
    		When Country in ('015','018','101','122','015','016','017','018','025'
    				,'026','035','055','065','066','067','085','090','096'
    				,'124','134','138','140','144','152','243','244','502'
    				,'507','531','536','579','604','614','615','622','641'
    				,'684','686','735','759','760','764','770','777','790'
    				,'911','935','951','999')
    		Then 'NON-STD OTHER'
                                                              Else 'NON-STD OTHER'
    		
          END;
    Still get the same error
    Code:
    SQL0104N  An unexpected token "END-OF-STATEMENT" was found 
    following "END".  Expected tokens may include:  "JOIN 
    <joined_table>                              ".
    any other thoughts?
    Jim

  4. #4
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    Try putting ( ) around the CASE statement:

    set .. = (CASE WHEN THEN .....END)

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Ok lets bring it back to the basics and we will go from there.
    Code:
    UPDATE DB2ADMIN.STG_JOC_TBL 
    Set j.office = s.office 
    from db2admin.stg_joc_tbl j, db2admin.REF_NA_SALES_XREF_TBL s 
    where j.district = s.district_code
    The only way for me to get this to run is for me to remove the from clause, which then removes the purpose of the update statment. How can i do a complex update statment...
    Jim

  6. #6
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Quote Originally Posted by dmmac
    Try putting ( ) around the CASE statement:

    set .. = (CASE WHEN THEN .....END)

    Different error
    Code:
    SQL0104N  An unexpected token ")" was found following 
    "BEGIN-OF-STATEMENT".  Expected tokens may include:  
    "<stmt_or_dcl>

  7. #7
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    Another possible example:

    UPDATE DB2ADMIN.STG_JOC_TBL j
    Set j.office = (select s.office from db2admin.REF_NA_SALES_XREF_TBL s
    where s.district_code = j.district)
    where exists (select 1 from db2admin.REF_NA_SALES_XREF_TBL s1 where j.district = s.district_code)

  8. #8
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Grrr

    I need a bit of help again...I got this code to work before, and I have lost my only copy!!!! It was soo long ago that I have forgoten how I fixed it. This is the code

    Code:
    connect to nam_test user conjdi using Jenn4me15;
    Update db2test.stg_joc_tbl
    Set Sub_Trade = Case
    		When 
    			(COUNTRY IN ('588','570','580','582','583','560','559','549','565','557','552',  
    		 		    '538','555','579','566','546','574','561','684','553')
    				AND ULTPORT NOT IN ('MARSHAL ISL','KWAJALEIN')) 
    			OR 
    			
    			(COUNTRY = 462 AND ULTPORT IN ('ABAKAN','KHABAROVSK','KORSAKOV','MAGADAN',
    							'PETROPAVLOVSK','VLADIVOSTOK','VOSTOCHNY',
    							'YUZHNO SAKHAL','YUZHNO SAKHALINSK'))
    		Then 'PACIFIC'
    
    
    		When 	
    			COUNTRY IN ('471','735','733','469','467','759','475') 
    			OR 
    			(COUNTRY = '427' AND ULTCODE IN ('42776','42782')) 
    			OR 
    			
    			COUNTRY = '427' 
    			AND (ULTCODE IN ('42776','46905','46961','48945','46931','47527',
    					'50220','46994','27900','47500','47531','47300',
    					'47507','47537','47125','47500','46935','48939',
    					'50801','48452','42782')
    			     OR   
    			    (ULTPORT = 'MONACO'))
    		Then 'MECL-WEST MED'
    
    
    		When    
    			COUNTRY IN ('101','400','401','403','405','409','412','416','419','421','423','425','428',
    				    '429','433','435','437','441','447','449','451','455') 
    			OR                            
    			
    			(COUNTRY = '427' 
    			AND ULTCODE NOT IN ('42776','46905','46961','48945','46931','47527','50220',
    					  '46994','27900','47500','47531','47300','47507','47537',
    					  '47125','47500','46935','48939','50801','48452','42782'))               
    			OR
    			
    			(COUNTRY = '462' 
    			AND ULTPORT NOT IN ('ABAKAN','KHABAROVSK','KORSAKOV','MAGADAN','PETROPAVLOVSK',
    					    'VLADIVOSTOK','VOSTOCHNY','YUZHNO SAKHAL','YUZHNO SAKHALINSK',
    					    'ARMENIA','YEREVAN','AZERBAIJAN','BAKU','TIMASEVSK','STAVROPOL',
    					    'KRASNODAR','NOVOROSSIYSK','KRIVOJ ROG','YALTA','SIMFEROPOL',
    					    'CHERKASSY','ODESSA','DNEPROPETROVS','ZHDANOV','DONECK',
    					    'BORISPOL','KIEV','ILLYICHEVSK','ILICHEVSK','KREMENCHUG',
    					    'ALMA ATA','ALMATY','KZYL ORDA','KAZAKHSTAN','BISHKEK','OSH',
    					    'KYRGYZSTAN','KULYAB','DUSANBE','TAJIKISTAN','DUSHANBE',
    					    'TADZHIKISTAN','TURKMENISTAN','UZBEKISTAN',
    					    'TIRASPOL','MOLDOVA'))
    		Then 'TRANS-ATLANTIC'
    
    
    		When 	
    			COUNTRY IN ('508','489','729','484','504','479','714','491','485','463','721', 
    				    '723','487','481','472','502','725','473','737') 
    			OR
    			
    			(COUNTRY = '462' AND ULTPORT IN ('ARMENIA','YEREVAN'))
    			OR
    			
    			(COUNTRY = '462' AND ULTPORT IN ('AZERBAIJAN','BAKU'))
    			OR
    			
    			(COUNTRY IN ('462','485') AND ULTPORT IN ('TIRASPOL','MOLDOVA'))
    			OR
    			
    			(COUNTRY = '462' AND ULTPORT IN ('TIMASEVSK','STAVROPOL','KRASNODAR','NOVOROSSIYSK'))                             
    			OR
    			
    			(COUNTRY = '462' AND ULTPORT IN ('KRIVOJ ROG','YALTA','SIMFEROPOL','CHERKASSY',
    							'DNEPROPETROVS','ZHDANOV','DONECK','BORISPOL',
    							'KIEV','ILLYICHEVSK','ILICHEVSK','ODESSA',
    							'KREMENCHUG',
    
    							-- Moved from MEIP on 03-30-2004
    							'ALMA ATA','KZYL ORDA','KAZAKHSTAN','ALMATY',
    							'BISHKEK','OSH','KYRGYZSTAN','KULYAB','DUSANBE',
    							'TAJIKISTAN','DUSHANBE','TADZHIKISTAN',
    							'TURKMENISTAN','UZBEKISTAN'))
    		Then 'MECL-OTHER MED'
    
    
    		When 	(COUNTRY IN ('533','517','520','535','513','511','525','523','518','542',
    				     '521','507','505','568','777','732','531','536','774'))
    		Then 'MECL-MEIP60'
    
    
    
    
    
    		When Sline = 'DFFC' 
    		       and (  [Name] in ('DOLE PURCHASING'
    					,'DOLE FRESH FRUIT'
    					,'DOLE FRESH FRUIT INTL LTD'
    					,'DOLE OCEAN LINER'
    					,'PRODUCTORA CARTONERA'
    					,'STANDARD FRUIT STEAMS HI'
    					,'STANDARD FRUITSTEAMSHI'
    					,'STANDARD FRUIT   STEAMSHI'
    					,'STANDARD FRUIT & STEAMSHI') 
    			    or FName in ('DOLE PURCHASING'))
    		Then 'DOLE'
    
    
    		When Sline = 'GWFL' 
    		       and (  [Name] in ('CHIQUITA BRANDS'
    					,'CHIQUITA UNIFRUTTI JAPAN'
    					,'CHIQUITA ITALIA S P A'
    					,'CHIQUITA ITALIA SPA'
    					,'MARITROP TRDG') 
    			   or FName = 'CHIQUITA BRANDS')
    		Then 'CHIQUITA'
    
    
    		When Sline = 'SOLY' 
    		       and ([Name] = 'SOLTECH' or [FNAME] = 'PARADISE FARMS')
    		Then 'SOLTECH'
    
    
    		When Sline = 'NETS' 
    		       and [Name] = 'DEL MONTE'
    		Then 'DEL MONTE'
    
    
    		When Sline = 'ECUA' 
    		       and [Name] = 'PACIFIC FRUIT'
    		Then 'PACIFIC FRUIT'
    
    
    		When Country in ('232','236','239','241','243','244','245'
    				,'247','248','272','274','277','283','307'
    				,'312','315','317','911')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-CARIB'
    
    
    		When Country in ('301','331','333','335','337')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-WCSA'
    
    
    		When Country in ('351','353','355','357')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-ECSA'
    
    
    		When Country in ('201','205','208','211','215','219','223','225')
    		     and Uscode not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-CAMR'
    
    
    		When Country in ('738','741','742','744','745','746','747','748','749'
    				,'750','751','752','753','754','755','756','758','760'
    				,'761','762','764','763','765','766') 
    		Then 'AFR-WEST AFRICA'
    		
    		When Country in ('780','785','787','788','789','791','792','793','794','795','796','797','799')
    			OR (Country = '790' and ULTPORT = 'REUNION')
    		Then 'AFR-SOUTH AFRICA'
    
    
    		When Country in ('767','769','778','779','783','790') AND ULTPORT != 'REUNION'
    		Then 'AFR-EAST AFRICA'
    
    		
    		When Country in ('602','604','614','615','622','641','686','951')
    			OR (COUNTRY = '684' AND ULTPORT IN ('MARSHAL ISL','KWAJALEIN'))
    		Then 'OCEANIA'
    
    
    		When Country in ('201','205','208','211','215','219','223','225')
    		Then 'NON-STD CENTRAL AMERICA'
    
    		
    		When Country in ('232','236','241','245','247','248','272','274'
    				,'277','283','307','312','315','317')
    		Then 'NON-STD CARIBBEAN'
    
    		
    		When Country in ('301','331','333','335','337')
    		Then 'NON-STD W. COAST S. AMERICA'
    		
    
    		When Country in ('351','353','355','357')
    		Then 'NON-STD E. COAST S. AMERICA'
    				
    
    		When Country in ('461','462','463','467','469','471','472','473'
    				,'475','479','481','484','485','487','489','491'
    				,'504','508','714','721','723','729','733')
    		Then 'NON-STD MED'
    		
    
    		When Country in ('533','535','538','542')
    		Then 'NON-STD ISC'
    		
    
    		When Country in ('511','513','517','518','520','521','523','525')
    		Then 'NON-STD MIDEAST'
    		
    
    		When Country in ('546','549','552','555','557','559','560','561'
    				,'565','568')
    		Then 'NON-STD SOUTH ASIA'
    		
    
    		When Country in ('566','570','574','580','582','583','588')
    		Then 'NON-STD EAST ASIA'
    		
    
    		When Country in ('015','018','101','122','015','016','017','018','025'
    				,'026','035','055','065','066','067','085','090','096'
    				,'124','134','138','140','144','152','243','244','502'
    				,'507','531','536','579','604','614','615','622','641'
    				,'684','686','735','759','760','764','770','777','790'
    				,'911','935','951','999')
    		Then 'NON-STD OTHER'
                    
    
    		Else 'NON-STD OTHER'
           end
    this is the error

    Code:
    SQL0401N  The data types of the operands for the operation "=" are not compatible.  SQLSTATE=42818
    Can anyone make a sugestion?
    Jim
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  9. #9
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    The ANSWER!!!

    Code:
    Update DB2TEST.STG_JOC_TBL
    Set SUB_TRADE = ( Case
    		When
    			(COUNTRY IN ('588','570','580','582','583','560','559','549','565','557','552',
    		 		    '538','555','579','566','546','574','561','684','553')
    				AND ULTPORT NOT IN ('MARSHAL ISL','KWAJALEIN'))
    			OR
    			(COUNTRY = '462' AND ULTPORT IN ('ABAKAN','KHABAROVSK','KORSAKOV','MAGADAN',
    							'PETROPAVLOVSK','VLADIVOSTOK','VOSTOCHNY',
    							'YUZHNO SAKHAL','YUZHNO SAKHALINSK'))
    		Then 'PACIFIC'
    		When 	
    			COUNTRY IN ('471','735','733','469','467','759','475')
    			OR
    			(COUNTRY = '427' AND ULTCODE IN ('42776','42782'))
    			OR
    			COUNTRY = '427'
    			AND (ULTCODE IN ('42776','46905','46961','48945','46931','47527',
    					'50220','46994','27900','47500','47531','47300',
    					'47507','47537','47125','47500','46935','48939',
    					'50801','48452','42782')
    			     OR
    			    (ULTPORT = 'MONACO'))
    		Then 'MECL-WEST MED'
    		When
    			COUNTRY IN ('101','400','401','403','405','409','412','416','419','421','423','425','428',
    				    '429','433','435','437','441','447','449','451','455')
    			OR
    			(COUNTRY = '427'
    			AND ULTCODE NOT IN ('42776','46905','46961','48945','46931','47527','50220',
    					  '46994','27900','47500','47531','47300','47507','47537',
    					  '47125','47500','46935','48939','50801','48452','42782'))
    			OR
    			(COUNTRY = '462'
    			AND ULTPORT NOT IN ('ABAKAN','KHABAROVSK','KORSAKOV','MAGADAN','PETROPAVLOVSK',
    					    'VLADIVOSTOK','VOSTOCHNY','YUZHNO SAKHAL','YUZHNO SAKHALINSK',
    					    'ARMENIA','YEREVAN','AZERBAIJAN','BAKU','TIMASEVSK','STAVROPOL',
    					    'KRASNODAR','NOVOROSSIYSK','KRIVOJ ROG','YALTA','SIMFEROPOL',
    					    'CHERKASSY','ODESSA','DNEPROPETROVS','ZHDANOV','DONECK',
    					    'BORISPOL','KIEV','ILLYICHEVSK','ILICHEVSK','KREMENCHUG',
    					    'ALMA ATA','ALMATY','KZYL ORDA','KAZAKHSTAN','BISHKEK','OSH',
    					    'KYRGYZSTAN','KULYAB','DUSANBE','TAJIKISTAN','DUSHANBE',
    					    'TADZHIKISTAN','TURKMENISTAN','UZBEKISTAN',
    					    'TIRASPOL','MOLDOVA'))
    		Then 'TRANS-ATLANTIC'
    		When
    			COUNTRY IN ('508','489','729','484','504','479','714','491','485','463','721',
    				    '723','487','481','472','502','725','473','737')
    			OR
    			(COUNTRY = '462' AND ULTPORT IN ('ARMENIA','YEREVAN'))
    			OR
    			(COUNTRY = '462' AND ULTPORT IN ('AZERBAIJAN','BAKU'))
    			OR
    			(COUNTRY IN ('462','485') AND ULTPORT IN ('TIRASPOL','MOLDOVA'))
    			OR
    			(COUNTRY = '462' AND ULTPORT IN ('TIMASEVSK','STAVROPOL','KRASNODAR','NOVOROSSIYSK'))
    			OR
    			(COUNTRY = '462' AND ULTPORT IN ('KRIVOJ ROG','YALTA','SIMFEROPOL','CHERKASSY',
    							'DNEPROPETROVS','ZHDANOV','DONECK','BORISPOL',
    							'KIEV','ILLYICHEVSK','ILICHEVSK','ODESSA',
    							'KREMENCHUG',
    							-- Moved from MEIP on 03-30-2004
    							'ALMA ATA','KZYL ORDA','KAZAKHSTAN','ALMATY',
    							'BISHKEK','OSH','KYRGYZSTAN','KULYAB','DUSANBE',
    							'TAJIKISTAN','DUSHANBE','TADZHIKISTAN',
    							'TURKMENISTAN','UZBEKISTAN'))
    		Then 'MECL-OTHER MED'
    		When 	(COUNTRY IN ('533','517','520','535','513','511','525','523','518','542',
    				     '521','507','505','568','777','732','531','536','774'))
    		Then 'MECL-MEIP60'
    		When SLINE = 'DFFC'
    		       and (  NAME  in ('DOLE PURCHASING'
    					,'DOLE FRESH FRUIT'
    					,'DOLE FRESH FRUIT INTL LTD'
    					,'DOLE OCEAN LINER'
    					,'PRODUCTORA CARTONERA'
    					,'STANDARD FRUIT STEAMS HI'
    					,'STANDARD FRUITSTEAMSHI'
    					,'STANDARD FRUIT   STEAMSHI'
    					,'STANDARD FRUIT & STEAMSHI')
    			    or FName in ('DOLE PURCHASING'))
    		Then 'DOLE'
    		When SLINE = 'GWFL'
    		       and (  NAME in ('CHIQUITA BRANDS'
    					,'CHIQUITA UNIFRUTTI JAPAN'
    					,'CHIQUITA ITALIA S P A'
    					,'CHIQUITA ITALIA SPA'
    					,'MARITROP TRDG')
    			   or FName = 'CHIQUITA BRANDS')
    		Then 'CHIQUITA'
    		When SLINE = 'SOLY'
    		       and (NAME = 'SOLTECH' or FNAME = 'PARADISE FARMS')
    		Then 'SOLTECH'
    		When SLINE = 'NETS'
    		       and NAME = 'DEL MONTE'
    		Then 'DEL MONTE'
    		When SLINE = 'ECUA'
    		       and NAME = 'PACIFIC FRUIT'
    		Then 'PACIFIC FRUIT'
    		When Country in ('232','236','239','241','243','244','245'
    				,'247','248','272','274','277','283','307'
    				,'312','315','317','911')
    		     and USCODE not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-CARIB'
    		When Country in ('301','331','333','335','337')
    		     and USCODE not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-WCSA'
    		When Country in ('351','353','355','357')
    		     and USCODE not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-ECSA'
    		When Country in ('201','205','208','211','215','219','223','225')
    		     and USCODE not in ('4904','4907','4908','4909','4911','4912')
    		Then 'AMR-CAMR'
    		When Country in ('738','741','742','744','745','746','747','748','749'
    				,'750','751','752','753','754','755','756','758','760'
    				,'761','762','764','763','765','766')
    		Then 'AFR-WEST AFRICA'
    		When Country in ('780','785','787','788','789','791','792','793','794','795','796','797','799')
    			OR (Country = '790' and ULTPORT = 'REUNION')
    		Then 'AFR-SOUTH AFRICA'
    		When Country in ('767','769','778','779','783','790') AND ULTPORT != 'REUNION'
    		Then 'AFR-EAST AFRICA'
    		When Country in ('602','604','614','615','622','641','686','951')
    			OR (COUNTRY = '684' AND ULTPORT IN ('MARSHAL ISL','KWAJALEIN'))
    		Then 'OCEANIA'
    		When Country in ('201','205','208','211','215','219','223','225')
    		Then 'NON-STD CENTRAL AMERICA'
    		When Country in ('232','236','241','245','247','248','272','274'
    				,'277','283','307','312','315','317')
    		Then 'NON-STD CARIBBEAN'
    		When Country in ('301','331','333','335','337')
    		Then 'NON-STD W. COAST S. AMERICA'
    		When Country in ('351','353','355','357')
    		Then 'NON-STD E. COAST S. AMERICA'
    		When Country in ('461','462','463','467','469','471','472','473'
    				,'475','479','481','484','485','487','489','491'
    				,'504','508','714','721','723','729','733')
    		Then 'NON-STD MED'
    		When Country in ('533','535','538','542')
    		Then 'NON-STD ISC'
    		When Country in ('511','513','517','518','520','521','523','525')
    		Then 'NON-STD MIDEAST'
    		When Country in ('546','549','552','555','557','559','560','561'
    				,'565','568')
    		Then 'NON-STD SOUTH ASIA'
    		When Country in ('566','570','574','580','582','583','588')
    		Then 'NON-STD EAST ASIA'
    		When Country in ('015','018','101','122','015','016','017','018','025'
    				,'026','035','055','065','066','067','085','090','096'
    				,'124','134','138','140','144','152','243','244','502'
    				,'507','531','536','579','604','614','615','622','641'
    				,'684','686','735','759','760','764','770','777','790'
    				,'911','935','951','999')
    		Then 'NON-STD OTHER'
    		Else 'NON-STD OTHER'
           END )
      ;
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •