| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
05-21-04, 10:11
|
|
Registered User
|
|
Join Date: Aug 2002
Location: Charlotte NC
Posts: 663
|
|
|
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
|
|
05-21-04, 10:24
|
|
Registered User
|
|
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
|
|
05-21-04, 11:34
|
|
Registered User
|
|
Join Date: Aug 2002
Location: Charlotte NC
Posts: 663
|
|
|
|
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
|
|
05-21-04, 14:03
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Massachusetts, USA
Posts: 106
|
|
Try putting ( ) around the CASE statement:
set .. = (CASE WHEN THEN .....END)
|
|
05-21-04, 14:04
|
|
Registered User
|
|
Join Date: Aug 2002
Location: Charlotte NC
Posts: 663
|
|
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
|
|
05-21-04, 14:07
|
|
Registered User
|
|
Join Date: Aug 2002
Location: Charlotte NC
Posts: 663
|
|
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>
|
|
05-21-04, 14:28
|
|
Registered User
|
|
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)
|
|
09-23-04, 11:34
|
|
Registered User
|
|
Join Date: Aug 2002
Location: Charlotte NC
Posts: 663
|
|
|
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
|
|
09-23-04, 16:34
|
|
Registered User
|
|
Join Date: Aug 2002
Location: Charlotte NC
Posts: 663
|
|
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 )
;
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
DB2 Case Statment
