I need to do an update to a coloumn in a table. Its a SQL case statment that idetifies my companies trade lanes. I want to do this transfrom as I pump the data into a stage table. I was told to do this in the derivation of the coloumn. Is this true? how on earth does that work? Can any one give me a good description of how this works or if i am even on the right track?
Jim
Im confused
Moderators: chulett, rschirm, roy
You select from the target table and create a file of your rows that you want to process. Derive the updated value for your particular column and write the output to a file. How you do the derivation is up to you, either use an inline if-then-else if structure or write a DS function in Manager and use DS BASIC's case statement to more elegantly structure your logic. Load that file back against your table, using update existing rows only generated SQL.
Trying to select from a table and write back to it is not a good solution.
Kenneth Bland
Trying to select from a table and write back to it is not a good solution.
Kenneth Bland
The DS Function...I know that I cant use native SQL in that. Is there any good source for a tutorial or a help guide in writing Datastage Basic?
Jim
quote:Originally posted by kcbland
[br]You select from the target table and create a file of your rows that you want to process. Derive the updated value for your particular column and write the output to a file. How you do the derivation is up to you, either use an inline if-then-else if structure or write a DS function in Manager and use DS BASIC's case statement to more elegantly structure your logic. Load that file back against your table, using update existing rows only generated SQL.
Trying to select from a table and write back to it is not a good solution.
Kenneth Bland
Jim
quote:Originally posted by kcbland
[br]You select from the target table and create a file of your rows that you want to process. Derive the updated value for your particular column and write the output to a file. How you do the derivation is up to you, either use an inline if-then-else if structure or write a DS function in Manager and use DS BASIC's case statement to more elegantly structure your logic. Load that file back against your table, using update existing rows only generated SQL.
Trying to select from a table and write back to it is not a good solution.
Kenneth Bland
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Enrol in the DataStage Essentials class. This will equip you with the knowledge you seek.
Essentially, you can work backwards from how you'd do it in SQL. UPDATE table SET column = value WHERE key = keyvalue
In DataStage all you need to do is create a job that extracts data from source, generate the value and keyvalue values, and feed this into an SQL-generating stage type with the write rule "update existing rows".
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Essentially, you can work backwards from how you'd do it in SQL. UPDATE table SET column = value WHERE key = keyvalue
In DataStage all you need to do is create a job that extracts data from source, generate the value and keyvalue values, and feed this into an SQL-generating stage type with the write rule "update existing rows".
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
I have taken the class..but my instructor just breezed over the sql part saying that it could be done in a routine and that was that realy
thats why i am soo lost
Jim
quote:Originally posted by Ray.Wurlod
[br]Enrol in the DataStage Essentials class. This will equip you with the knowledge you seek.
Essentially, you can work backwards from how you'd do it in SQL. UPDATE table SET column = value WHERE key = keyvalue
In DataStage all you need to do is create a job that extracts data from source, generate the value and keyvalue values, and feed this into an SQL-generating stage type with the write rule "update existing rows".
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
thats why i am soo lost
Jim
quote:Originally posted by Ray.Wurlod
[br]Enrol in the DataStage Essentials class. This will equip you with the knowledge you seek.
Essentially, you can work backwards from how you'd do it in SQL. UPDATE table SET column = value WHERE key = keyvalue
In DataStage all you need to do is create a job that extracts data from source, generate the value and keyvalue values, and feed this into an SQL-generating stage type with the write rule "update existing rows".
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The only way that you can use SQL in a Routine is to use DSExecute (if it's SQL against the tables in the DataStage repository) or to use the BCI functions (if it's SQL against a database accessed through an ODBC protocol).
Derivations in DataStage are done with BASIC expressions (in server jobs), or with Orchestrate expressions (in parallel jobs), or with COBOL-compatible expressions (in mainframe jobs).
If you can describe the exact transformation that you wish to achieve, maybe we can offer suggestions. Make sure you tell us whether you're using server, parallel or mainframe - the answer will differ depending on this.
Derivations in DataStage are done with BASIC expressions (in server jobs), or with Orchestrate expressions (in parallel jobs), or with COBOL-compatible expressions (in mainframe jobs).
If you can describe the exact transformation that you wish to achieve, maybe we can offer suggestions. Make sure you tell us whether you're using server, parallel or mainframe - the answer will differ depending on this.
What I am trying to do is a complex update statment on a server setup
this is the code
quote:
[br] Update DW_JOC_ALL_IMP_DATA_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 TOTAL'
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','KZYLORDA','KAZAKHSTAN','BISHKEK','OSH',
'KYRGYZSTAN','KULYAB','DUSANBE','TAJIKISTAN','DUSHANBE',
'TADZHIKISTAN','TURKMENISTAN','UZBEKISTAN',
'TIRASPOL','MOLDOVA'))
Then 'TRANS-ATLANTIC TOTAL'
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'))
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'))
OR
(COUNTRY = '462' AND ULTPORT IN ('ALMA ATA','KZYL ORDA','KAZAKHSTAN','ALMATY',
'BISHKEK','OSH','KYRGYZSTAN','KULYAB','DUSANBE',
'TAJIKISTAN','DUSHANBE','TADZHIKISTAN',
'TURKMENISTAN','UZBEKISTAN'))
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 TOTAL'
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
where sub_trade is null
quote:Originally posted by Ray.Wurlod
[br]The only way that you can use SQL in a Routine is to use DSExecute (if it's SQL against the tables in the DataStage repository) or to use the BCI functions (if it's SQL against a database accessed through an ODBC protocol).
Derivations in DataStage are done with BASIC expressions (in server jobs), or with Orchestrate expressions (in parallel jobs), or with COBOL-compatible expressions (in mainframe jobs).
If you can describe the exact transformation that you wish to achieve, maybe we can offer suggestions. Make sure you tell us whether you're using server, parallel or mainframe - the answer will differ depending on this.
this is the code
quote:
[br] Update DW_JOC_ALL_IMP_DATA_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 TOTAL'
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','KZYLORDA','KAZAKHSTAN','BISHKEK','OSH',
'KYRGYZSTAN','KULYAB','DUSANBE','TAJIKISTAN','DUSHANBE',
'TADZHIKISTAN','TURKMENISTAN','UZBEKISTAN',
'TIRASPOL','MOLDOVA'))
Then 'TRANS-ATLANTIC TOTAL'
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'))
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'))
OR
(COUNTRY = '462' AND ULTPORT IN ('ALMA ATA','KZYL ORDA','KAZAKHSTAN','ALMATY',
'BISHKEK','OSH','KYRGYZSTAN','KULYAB','DUSANBE',
'TAJIKISTAN','DUSHANBE','TADZHIKISTAN',
'TURKMENISTAN','UZBEKISTAN'))
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 TOTAL'
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
where sub_trade is null
quote:Originally posted by Ray.Wurlod
[br]The only way that you can use SQL in a Routine is to use DSExecute (if it's SQL against the tables in the DataStage repository) or to use the BCI functions (if it's SQL against a database accessed through an ODBC protocol).
Derivations in DataStage are done with BASIC expressions (in server jobs), or with Orchestrate expressions (in parallel jobs), or with COBOL-compatible expressions (in mainframe jobs).
If you can describe the exact transformation that you wish to achieve, maybe we can offer suggestions. Make sure you tell us whether you're using server, parallel or mainframe - the answer will differ depending on this.
This is why my recommendation is to put this logic into a DS Function. You gain test capability (Test button with test cases), your can easily version this stuff by doing change management on new and improved versions of this function, as well as the ease in development because your jobs are simple.
You could take your SQL case statement presented here, and with a few simple conversions, turn it into a DS BASIC function developed in DS Manager. Your jobs are quite simple, as I stated earlier.
However, your best solution is not to put this logic into a function. Now that I see what you are doing, the solution cries to be put into a table. This logic belongs in a controlling table managed in the warehouse, not in programming logic. I would seriously rethink your approach.
Kenneth Bland
You could take your SQL case statement presented here, and with a few simple conversions, turn it into a DS BASIC function developed in DS Manager. Your jobs are quite simple, as I stated earlier.
However, your best solution is not to put this logic into a function. Now that I see what you are doing, the solution cries to be put into a table. This logic belongs in a controlling table managed in the warehouse, not in programming logic. I would seriously rethink your approach.
Kenneth Bland
I need you to eliaberate on your idea of putting this in a table in the db. Why does it cry to be put into a table?
You said that my SQL only needed a few tweeks. Can you point me in the direrction of an example so that I can tweek my code?
Regards
Jim
quote:Originally posted by kcbland
[br]This is why my recommendation is to put this logic into a DS Function. You gain test capability (Test button with test cases), your can easily version this stuff by doing change management on new and improved versions of this function, as well as the ease in development because your jobs are simple.
You could take your SQL case statement presented here, and with a few simple conversions, turn it into a DS BASIC function developed in DS Manager. Your jobs are quite simple, as I stated earlier.
However, your best solution is not to put this logic into a function. Now that I see what you are doing, the solution cries to be put into a table. This logic belongs in a controlling table managed in the warehouse, not in programming logic. I would seriously rethink your approach.
Kenneth Bland
You said that my SQL only needed a few tweeks. Can you point me in the direrction of an example so that I can tweek my code?
Regards
Jim
quote:Originally posted by kcbland
[br]This is why my recommendation is to put this logic into a DS Function. You gain test capability (Test button with test cases), your can easily version this stuff by doing change management on new and improved versions of this function, as well as the ease in development because your jobs are simple.
You could take your SQL case statement presented here, and with a few simple conversions, turn it into a DS BASIC function developed in DS Manager. Your jobs are quite simple, as I stated earlier.
However, your best solution is not to put this logic into a function. Now that I see what you are doing, the solution cries to be put into a table. This logic belongs in a controlling table managed in the warehouse, not in programming logic. I would seriously rethink your approach.
Kenneth Bland
Here's a sample of how you could construct your DS function in Manager. In a nutshell, setup arguments to pass into your function to handle your hard-coded cases. A series of If-Then-Else and Locate statements are used to determine the condition and result. A Goto statement is used to exit the checking logic. I'll answer later today regarding a table driven design.
COUNTRY = Arg1
ULTPORT = Arg2
ULTCODE = Arg3
Ans = ""
* -- (PACIFIC TRADE GENERAL):
Locate COUNTRY IN '588':@AM:'570':@AM:'580':@AM:'582':@AM:'583':@AM:'560':@AM:'559':@AM:'549':@AM:'565':@AM:'557':@AM:'552':@AM:'538':@AM:'555':@AM:'579':@AM:'566':@AM:'546':@AM:'574':@AM:'561':@AM:'684':@AM:'553' SETTING PSN Then FOUND = @TRUE Else FOUND = @FALSE
If FOUND AND (ULTPORT # 'MARSHAL ISL' AND ULTPORT # 'KWAJALEIN') Then
Ans = 'PACIFIC TOTAL'
Goto Fini
End
*-- (PACIFIC RUSSIA):
Locate ULTPORT IN 'ABAKAN':@AM:'KHABAROVSK':@AM:'KORSAKOV':@AM:'MAGADAN':@AM:'PETROPAVLOVSK':@AM:'VLADIVOSTOK':@AM:'VOSTOCHNY':@AM:'YUZHNO SAKHAL':@AM:'YUZHNO SAKHALINSK' SETTING PSN Then FOUND = @TRUE Else FOUND = @FALSE
If FOUND AND COUNTRY = 462 Then
Ans = 'PACIFIC TOTAL'
Goto Fini
End
* -- (WEST MED GENERAL TRADE):
Locate COUNTRY IN '471':@AM:'735':@AM:'733':@AM:'469':@AM:'467':@AM:'759':@AM:'475' SETTING PSN Then FOUND1 = @TRUE Else FOUND1 = @FALSE
If COUNTRY = '427' AND (ULTCODE = '42776' OR ULTCODE = '42782') Then FOUND2 = @TRUE Else FOUND2 = @FALSE
If COUNTRY = '427' Then
Locate ULTCODE IN '42776':@AM:'46905':@AM:'46961':@AM:'48945':@AM:'46931':@AM:'47527':@AM:'50220':@AM:'46994':@AM:'27900':@AM:'47500':@AM:'47531':@AM:'47300':@AM:'47507':@AM:'47537':@AM:'47125':@AM:'47500':@AM:'46935':@AM:'48939':@AM:'50801':@AM:'48452':@AM:'42782' SETTING PSN Then FOUND3 = @TRUE Else FOUND3 = @FALSE
End Else Found3 = @FALSE
If ULTPORT = 'MONACO' Then FOUND4 = @TRUE Else FOUND4 = @FALSE
If FOUND1 OR FOUND2 OR FOUND3 OR FOUND4 Then
Ans = 'MECL-WEST MED'
Goto Fini
End
Ans = "NON-STD OTHER"
Fini:
Kenneth Bland
COUNTRY = Arg1
ULTPORT = Arg2
ULTCODE = Arg3
Ans = ""
* -- (PACIFIC TRADE GENERAL):
Locate COUNTRY IN '588':@AM:'570':@AM:'580':@AM:'582':@AM:'583':@AM:'560':@AM:'559':@AM:'549':@AM:'565':@AM:'557':@AM:'552':@AM:'538':@AM:'555':@AM:'579':@AM:'566':@AM:'546':@AM:'574':@AM:'561':@AM:'684':@AM:'553' SETTING PSN Then FOUND = @TRUE Else FOUND = @FALSE
If FOUND AND (ULTPORT # 'MARSHAL ISL' AND ULTPORT # 'KWAJALEIN') Then
Ans = 'PACIFIC TOTAL'
Goto Fini
End
*-- (PACIFIC RUSSIA):
Locate ULTPORT IN 'ABAKAN':@AM:'KHABAROVSK':@AM:'KORSAKOV':@AM:'MAGADAN':@AM:'PETROPAVLOVSK':@AM:'VLADIVOSTOK':@AM:'VOSTOCHNY':@AM:'YUZHNO SAKHAL':@AM:'YUZHNO SAKHALINSK' SETTING PSN Then FOUND = @TRUE Else FOUND = @FALSE
If FOUND AND COUNTRY = 462 Then
Ans = 'PACIFIC TOTAL'
Goto Fini
End
* -- (WEST MED GENERAL TRADE):
Locate COUNTRY IN '471':@AM:'735':@AM:'733':@AM:'469':@AM:'467':@AM:'759':@AM:'475' SETTING PSN Then FOUND1 = @TRUE Else FOUND1 = @FALSE
If COUNTRY = '427' AND (ULTCODE = '42776' OR ULTCODE = '42782') Then FOUND2 = @TRUE Else FOUND2 = @FALSE
If COUNTRY = '427' Then
Locate ULTCODE IN '42776':@AM:'46905':@AM:'46961':@AM:'48945':@AM:'46931':@AM:'47527':@AM:'50220':@AM:'46994':@AM:'27900':@AM:'47500':@AM:'47531':@AM:'47300':@AM:'47507':@AM:'47537':@AM:'47125':@AM:'47500':@AM:'46935':@AM:'48939':@AM:'50801':@AM:'48452':@AM:'42782' SETTING PSN Then FOUND3 = @TRUE Else FOUND3 = @FALSE
End Else Found3 = @FALSE
If ULTPORT = 'MONACO' Then FOUND4 = @TRUE Else FOUND4 = @FALSE
If FOUND1 OR FOUND2 OR FOUND3 OR FOUND4 Then
Ans = 'MECL-WEST MED'
Goto Fini
End
Ans = "NON-STD OTHER"
Fini:
Kenneth Bland
I am looking at this and I get alot of it. Do you know of a reasource where i can decode some of the functions that you used like the @AM and the useage of the # sighn?
Jim
quote:Originally posted by kcbland
[br]Here's a sample of how you could construct your DS function in Manager. In a nutshell, setup arguments to pass into your function to handle your hard-coded cases. A series of If-Then-Else and Locate statements are used to determine the condition and result. A Goto statement is used to exit the checking logic. I'll answer later today regarding a table driven design.
COUNTRY = Arg1
ULTPORT = Arg2
ULTCODE = Arg3
Ans = ""
* -- (PACIFIC TRADE GENERAL):
Locate COUNTRY IN '588':@AM:'570':@AM:'580':@AM:'582':@AM:'583':@AM:'560':@AM:'559':@AM:'549':@AM:'565':@AM:'557':@AM:'552':@AM:'538':@AM:'555':@AM:'579':@AM:'566':@AM:'546':@AM:'574':@AM:'561':@AM:'684':@AM:'553' SETTING PSN Then FOUND = @TRUE Else FOUND = @FALSE
If FOUND AND (ULTPORT # 'MARSHAL ISL' AND ULTPORT # 'KWAJALEIN') Then
Ans = 'PACIFIC TOTAL'
Goto Fini
End
*-- (PACIFIC RUSSIA):
Locate ULTPORT IN 'ABAKAN':@AM:'KHABAROVSK':@AM:'KORSAKOV':@AM:'MAGADAN':@AM:'PETROPAVLOVSK':@AM:'VLADIVOSTOK':@AM:'VOSTOCHNY':@AM:'YUZHNO SAKHAL':@AM:'YUZHNO SAKHALINSK' SETTING PSN Then FOUND = @TRUE Else FOUND = @FALSE
If FOUND AND COUNTRY = 462 Then
Ans = 'PACIFIC TOTAL'
Goto Fini
End
* -- (WEST MED GENERAL TRADE):
Locate COUNTRY IN '471':@AM:'735':@AM:'733':@AM:'469':@AM:'467':@AM:'759':@AM:'475' SETTING PSN Then FOUND1 = @TRUE Else FOUND1 = @FALSE
If COUNTRY = '427' AND (ULTCODE = '42776' OR ULTCODE = '42782') Then FOUND2 = @TRUE Else FOUND2 = @FALSE
If COUNTRY = '427' Then
Locate ULTCODE IN '42776':@AM:'46905':@AM:'46961':@AM:'48945':@AM:'46931':@AM:'47527':@AM:'50220':@AM:'46994':@AM:'27900':@AM:'47500':@AM:'47531':@AM:'47300':@AM:'47507':@AM:'47537':@AM:'47125':@AM:'47500':@AM:'46935':@AM:'48939':@AM:'50801':@AM:'48452':@AM:'42782' SETTING PSN Then FOUND3 = @TRUE Else FOUND3 = @FALSE
End Else Found3 = @FALSE
If ULTPORT = 'MONACO' Then FOUND4 = @TRUE Else FOUND4 = @FALSE
If FOUND1 OR FOUND2 OR FOUND3 OR FOUND4 Then
Ans = 'MECL-WEST MED'
Goto Fini
End
Ans = "NON-STD OTHER"
Fini:
Kenneth Bland
Jim
quote:Originally posted by kcbland
[br]Here's a sample of how you could construct your DS function in Manager. In a nutshell, setup arguments to pass into your function to handle your hard-coded cases. A series of If-Then-Else and Locate statements are used to determine the condition and result. A Goto statement is used to exit the checking logic. I'll answer later today regarding a table driven design.
COUNTRY = Arg1
ULTPORT = Arg2
ULTCODE = Arg3
Ans = ""
* -- (PACIFIC TRADE GENERAL):
Locate COUNTRY IN '588':@AM:'570':@AM:'580':@AM:'582':@AM:'583':@AM:'560':@AM:'559':@AM:'549':@AM:'565':@AM:'557':@AM:'552':@AM:'538':@AM:'555':@AM:'579':@AM:'566':@AM:'546':@AM:'574':@AM:'561':@AM:'684':@AM:'553' SETTING PSN Then FOUND = @TRUE Else FOUND = @FALSE
If FOUND AND (ULTPORT # 'MARSHAL ISL' AND ULTPORT # 'KWAJALEIN') Then
Ans = 'PACIFIC TOTAL'
Goto Fini
End
*-- (PACIFIC RUSSIA):
Locate ULTPORT IN 'ABAKAN':@AM:'KHABAROVSK':@AM:'KORSAKOV':@AM:'MAGADAN':@AM:'PETROPAVLOVSK':@AM:'VLADIVOSTOK':@AM:'VOSTOCHNY':@AM:'YUZHNO SAKHAL':@AM:'YUZHNO SAKHALINSK' SETTING PSN Then FOUND = @TRUE Else FOUND = @FALSE
If FOUND AND COUNTRY = 462 Then
Ans = 'PACIFIC TOTAL'
Goto Fini
End
* -- (WEST MED GENERAL TRADE):
Locate COUNTRY IN '471':@AM:'735':@AM:'733':@AM:'469':@AM:'467':@AM:'759':@AM:'475' SETTING PSN Then FOUND1 = @TRUE Else FOUND1 = @FALSE
If COUNTRY = '427' AND (ULTCODE = '42776' OR ULTCODE = '42782') Then FOUND2 = @TRUE Else FOUND2 = @FALSE
If COUNTRY = '427' Then
Locate ULTCODE IN '42776':@AM:'46905':@AM:'46961':@AM:'48945':@AM:'46931':@AM:'47527':@AM:'50220':@AM:'46994':@AM:'27900':@AM:'47500':@AM:'47531':@AM:'47300':@AM:'47507':@AM:'47537':@AM:'47125':@AM:'47500':@AM:'46935':@AM:'48939':@AM:'50801':@AM:'48452':@AM:'42782' SETTING PSN Then FOUND3 = @TRUE Else FOUND3 = @FALSE
End Else Found3 = @FALSE
If ULTPORT = 'MONACO' Then FOUND4 = @TRUE Else FOUND4 = @FALSE
If FOUND1 OR FOUND2 OR FOUND3 OR FOUND4 Then
Ans = 'MECL-WEST MED'
Goto Fini
End
Ans = "NON-STD OTHER"
Fini:
Kenneth Bland
Your Online Documentation BASIC manual that comes with DataStage Client. You'll see that not equals has several equivalents: NE, #, . I like # because I'm old school. The @AM trick is using an exploitation of dynamic array logic.
There are 2 easy ways to find text using BASIC functions:
INDEX
LOCATE
INDEX is fastest, but it hits on substring matches. LOCATE is slower and requires the data in a structured form, such as an array, but it does not false positive on substring matches. I chose it for my example because it's easiest to use and understand.
The trick is to form an array dynamically. One way is the following:
Array = ""
Array = "line 1"
Array = "line 2"
Array = "line 3"
Array = "line 4"
Array = "line 5"
Another way is:
Array = ""
Array = "line 1"
Array = "line 2"
Array = "line 3"
Array = "line 4"
Array = "line 5"
Another way is:
Array = "line 1":@AM:"line 2":@AM:"line 3":@AM:"line 4":@AM:"line 5"
@AM is an internal array row delimiter value. @VM is a subfield delimiter (called a value mark for PICK junkies). @SM is a sub-subfield delimiter (called a sub-value mark for PICK junkies). I call it a cheat because DS BASIC is hardcore string manipulation, so you can manipulate arrays as strings and strings as arrays.
Good luck!
-Ken
Kenneth Bland
There are 2 easy ways to find text using BASIC functions:
INDEX
LOCATE
INDEX is fastest, but it hits on substring matches. LOCATE is slower and requires the data in a structured form, such as an array, but it does not false positive on substring matches. I chose it for my example because it's easiest to use and understand.
The trick is to form an array dynamically. One way is the following:
Array = ""
Array = "line 1"
Array = "line 2"
Array = "line 3"
Array = "line 4"
Array = "line 5"
Another way is:
Array = ""
Array = "line 1"
Array = "line 2"
Array = "line 3"
Array = "line 4"
Array = "line 5"
Another way is:
Array = "line 1":@AM:"line 2":@AM:"line 3":@AM:"line 4":@AM:"line 5"
@AM is an internal array row delimiter value. @VM is a subfield delimiter (called a value mark for PICK junkies). @SM is a sub-subfield delimiter (called a sub-value mark for PICK junkies). I call it a cheat because DS BASIC is hardcore string manipulation, so you can manipulate arrays as strings and strings as arrays.
Good luck!
-Ken
Kenneth Bland
Hi,
*** late note:
This should work if you have a set of rows you need to update as input source to your update rows, not if your update is user defined for the entire table
***
Just wanted to point out that as far as you specified the logic you need it can simply be done with stage variables!
after you do that respectivly populate your derived columns and there you have your update rows all nice and ready.
I know this is not that simple, then again so is your SQL.
i.e. SV1 would look like this:
if (Count("'588','570','580','582','583','560','559','549','565','557','552',
'538','555','579','566','546','574','561','684','553'", link.COUNTRY) > 0
AND Count("'MARSHAL ISL','KWAJALEIN'", link.ULTPORT )= 0 ) Or link.COUNTRY = 462 And Count("'ABAKAN','KHABAROVSK','KORSAKOV','MAGADAN',
'PETROPAVLOVSK','VLADIVOSTOK','VOSTOCHNY',
'YUZHNO SAKHAL','YUZHNO SAKHALINSK'", link.ULTPORT ) > 0 Then 'PACIFIC TOTAL' Else ""
** the above represents the following logic in your sql
[br] Update DW_JOC_ALL_IMP_DATA_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 TOTAL'
**
A litle trick when you have several, different, conditions for the same column put:
if ... then value else
after they are all done use another StageVar to concatinate or sum them up (depending on numeric or character value) and simply assign the final StageVar to the designated column.
p.s.
Bare in mind that the order in which the stage variables apear is important since they are calculated in the order they apear.
IHTH (I Hope This Helps),
Roy R.
*** late note:
This should work if you have a set of rows you need to update as input source to your update rows, not if your update is user defined for the entire table
***
Just wanted to point out that as far as you specified the logic you need it can simply be done with stage variables!
after you do that respectivly populate your derived columns and there you have your update rows all nice and ready.
I know this is not that simple, then again so is your SQL.
i.e. SV1 would look like this:
if (Count("'588','570','580','582','583','560','559','549','565','557','552',
'538','555','579','566','546','574','561','684','553'", link.COUNTRY) > 0
AND Count("'MARSHAL ISL','KWAJALEIN'", link.ULTPORT )= 0 ) Or link.COUNTRY = 462 And Count("'ABAKAN','KHABAROVSK','KORSAKOV','MAGADAN',
'PETROPAVLOVSK','VLADIVOSTOK','VOSTOCHNY',
'YUZHNO SAKHAL','YUZHNO SAKHALINSK'", link.ULTPORT ) > 0 Then 'PACIFIC TOTAL' Else ""
** the above represents the following logic in your sql
[br] Update DW_JOC_ALL_IMP_DATA_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 TOTAL'
**
A litle trick when you have several, different, conditions for the same column put:
if ... then value else
after they are all done use another StageVar to concatinate or sum them up (depending on numeric or character value) and simply assign the final StageVar to the designated column.
p.s.
Bare in mind that the order in which the stage variables apear is important since they are calculated in the order they apear.
IHTH (I Hope This Helps),
Roy R.
I am in the middle of testing this...It seems to work. This tool has many diff ways to do one thing...Im still trying to figure out wich is the best.
Jim
quote:Originally posted by roy
[br]Hi,
Just wanted to point out that as far as you specified the logic you need can simply be done with stage variables!
after you do that respectivly populate your derived columns and there you have your update rows all nice and ready.
I know this is not that simple, then again so is your SQL.
i.e. SV1 would look like this:
if (Count("'588','570','580','582','583','560','559','549','565','557','552',
'538','555','579','566','546','574','561','684','553'", link.COUNTRY) > 0
AND Count("'MARSHAL ISL','KWAJALEIN'", link.ULTPORT )= 0 ) Or link.COUNTRY = 462 And Count("'ABAKAN','KHABAROVSK','KORSAKOV','MAGADAN',
'PETROPAVLOVSK','VLADIVOSTOK','VOSTOCHNY',
'YUZHNO SAKHAL','YUZHNO SAKHALINSK'", link.ULTPORT ) > 0 Then 'PACIFIC TOTAL' Else ""
** the above represents the following logic in your sql
[br] Update DW_JOC_ALL_IMP_DATA_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 TOTAL'
**
A litle trick when you have several, different, conditions for the same column put:
if ... then value else
after they are all done use another StageVar to concatinate or sum them up (depending on numeric or character value) and simply assign the final StageVar to the designated column.
p.s.
Bare in mind that the order in which the stage variables apear is important since they are calculated in the order they apear.
IHTH (I Hope This Helps),
Roy R.
Jim
quote:Originally posted by roy
[br]Hi,
Just wanted to point out that as far as you specified the logic you need can simply be done with stage variables!
after you do that respectivly populate your derived columns and there you have your update rows all nice and ready.
I know this is not that simple, then again so is your SQL.
i.e. SV1 would look like this:
if (Count("'588','570','580','582','583','560','559','549','565','557','552',
'538','555','579','566','546','574','561','684','553'", link.COUNTRY) > 0
AND Count("'MARSHAL ISL','KWAJALEIN'", link.ULTPORT )= 0 ) Or link.COUNTRY = 462 And Count("'ABAKAN','KHABAROVSK','KORSAKOV','MAGADAN',
'PETROPAVLOVSK','VLADIVOSTOK','VOSTOCHNY',
'YUZHNO SAKHAL','YUZHNO SAKHALINSK'", link.ULTPORT ) > 0 Then 'PACIFIC TOTAL' Else ""
** the above represents the following logic in your sql
[br] Update DW_JOC_ALL_IMP_DATA_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 TOTAL'
**
A litle trick when you have several, different, conditions for the same column put:
if ... then value else
after they are all done use another StageVar to concatinate or sum them up (depending on numeric or character value) and simply assign the final StageVar to the designated column.
p.s.
Bare in mind that the order in which the stage variables apear is important since they are calculated in the order they apear.
IHTH (I Hope This Helps),
Roy R.
The usage of COUNT is wrong. It will give substring matches, where as you need whole word matches. In addition, you are counting the occurrences of values within a string that looks like this:
"'VAL1','VAL2','VAL3'" etc.
You need each code value in a list, and a command that does not hit on substrings, such as LOCATE combined with an array in the form of:
VAL1
VAL2
VAL3
VAL4
Kenneth Bland
"'VAL1','VAL2','VAL3'" etc.
You need each code value in a list, and a command that does not hit on substrings, such as LOCATE combined with an array in the form of:
VAL1
VAL2
VAL3
VAL4
Kenneth Bland