This is a multi-part message in MIME format.

------=_NextPart_000_0001_01C23160.4AA00E20
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,


Please check the red word text.


Thanks
-----Original Message-----
From: Ali Karimi [mailto:a97alika@s...]
Sent: Friday, July 19, 2002 5:07 PM
To: sql language
Subject: [sql_language] SV: RE: SV: SV: RE: SV:

Hi again!

> you specify the item by item but for my case, I have 1200 items on my
table.  How can do it one item by one
> item? 

Well, that is TRUE, but that was not really given in you earlier msg:s
(actually the requirements are not that clear).
If you do not have many different items, or let me say more correctly,
if you have STATIC amount of
items and those are very few then this solution (i.e. the trigger given
in the previous msg) is enough for the problem domain.
BUT, that is clearly not the case here!
Anyway, even if you have 1200 items (or even 5000 items) you still need
to find out what type of items you do
have. Otherwise you will not be able to find out what type of
"conversion algorithm" you need to use (e.g. if a new row
is inserted into Table_B and the item is a ItemB, then you need to find
that out and use the correct conversion alg.,
if needed, to make changes/update Table_A).
The main questions here are, how many diff. items do you have? And does
it exist a diff. calculation
for each one of them? (Recall that you have said that your table has
1200 items, but its not clear if each
one of them are diff. and need diff. calculating conversion).
No matter how many diff. calculation alg. you still need to type/store
does conversion methods in you application.
Now this problem can be solved with diff. techniques. One way to go is
to store each one of them (the conversion algorithms)
in a table and query for the right one to use. This way, if the
conversion algorithm need to be changed for a specific item, you will
only need
to update at one place only (and as I understood, we are talking about
very simple & short conversions).
Another way (not recommended, if the performance is not an issue) is to
simply hard-code it (like the trigger in the
earlier msg).One more thing that pops up on my mind, is that maybe it
would also be a good id=E9e if you mark each tupple/row
that has been changed (or if there will be any kind of items that never
need to be changed). This way, when the table growth, you
can easily exclude those tuples in your search query (and helping the
optimizer).
BUT, all these are just some thoughts. What i really would like to know,
i.e. if you still haven't solve the problem yet, is more information
about the items and the conditions related to them, e.g.

* Which columns in Table_B are compared with Table_A?
    - Do you only need to compare Item & uom1 from Table_B with Item &
uom from Table_A? (still not clear!)
according to the example tables that I have mail to you are like this:

Table A ( transaction table)
Item                uom                 qty
Item A             bag                  10
Item B             carton              20
Item C             piece               30
Item A             kg                    40
Item D            roll                   50
Item B             kg                    60 and so on
(the uom for each transaction may not be fix, all qty in table A have to
convert the qty in =91KG=92 uom unless the item does not have =91KG=92 
uom)

Table B (conversion uom table)
Item                uom1               uom2               factor
ItemA              bag                  kg                    50
ItemB              kg                    carton              30
(item C and Item D do not have conversion, they no need to convert to
=91KG=92)
           
 so=85
uom in Table A have to compared the uom1 and uom2 in TableB, let say

if uom in Table A=3Duom1 in table B then qty in Table A=3D(qty in Table 
A *
factor in Table B)
else
if uom in Table A=3Duom2 in table B then qty in Table A=3D(qty in TableA 
/
factor in Table B)
else
if uom in Table A<>uom1 in table B and uom in table A <> uom2 in table B
then qty in Table A
 end


so, the final result in Table A should be

Table A (after conversion)

Item                uom                 qty
ItemA              bag                  500(kg)
            itemB               carton              0.6667(kg)
            ItemC               piece               30(piece)
            ItemA               kg                    40(kg)
            ItemD               roll                   50(roll)
            ItemB               kg                    60(kg)


* How often do you nee to make changes to Table_A and Table_B?
    - Specially Table_A. Good to know if possible (and if its a good
id=E9e) to "mark" already changed tupples.
      Sorted Index can then be used on that column for sorting out
tupples and help the optimizer to skip tuples
      that do not match the conditions (that will speed up the query,
because there will not be any need to go through
      all of the "10 000" rows).
 Table B detail is fix cannot be changed, the purpose is for conversion
calculation.

* How many diff. calculation algorithms are there?
    - Many? Just a few?
Only the item have a =91kg=92 conversion necessary.

* Why do you need to make this changes/conversions later? How come some
changes are made later and inserted into Table_B?
    - Are you sure that this is the proper design for this?
Not understand!

I hope that you can answer this questions and also put some other
important stuff that you might think can help for solving this, into
your
next msg and post it.

ok, now i am going to sleep :o)

Regards
/ Ali



-----Ursprungligt meddelande-----
Fr=E5n: Choo SS [mailto:sschoo@a...]
Skickat: den 19 juli 2002 02:16
Till: sql language
=C4mne: [sql_language] RE: SV: SV: RE: SV:
Good morning
Thanks. I had read thru your statement but I find out that the trigger u
give me is not suitable for me because while you use 'CASE', you specify
the item by item but for my case, I have 1200 items on my table.  How
can do it one item by one item? 
Please advise.

Thanks
--- Change your mail options at http://p2p.wrox.com/manager.asp or to
unsubscribe send a blank email to