This is a multi-part message in MIME format.

------=_NextPart_000_0001_01C22EFC.8E9A2C20
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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 



-----Original Message-----
From: Ali Karimi [mailto:a97alika@s...] 
Sent: Thursday, July 18, 2002 8:33 PM
To: sql language
Subject: [sql_language] SV: SV: RE: SV:

Hi Choo,

Here is the TRIGGER you wanted (ex. for result is also included; se
below).
I hope that this is what you are looking for.
If not, just send a msg and we will work on it ;o)


DROP TRIGGER LookingForChanges
GO

CREATE TRIGGER LookingForChanges
ON Table_B
AFTER INSERT, UPDATE /* do not really know if its possible
			to "update" the table. Just take
			UPDATE away if it should not trigger
			for updates. */
AS
	UPDATE Table_A
	SET qty = CASE inserted.Item
			WHEN 'ItemA'
		   		THEN	(Table_A.qty * inserted.FACTOR)
			WHEN 'ItemB'
		   		THEN	(Table_A.qty / inserted.FACTOR)
		    END
	FROM inserted
	WHERE Table_A.Item = inserted.Item AND Table_A.uom <>
inserted.uom1
GO



*** FROM BEGINNING ***

TABEL_A:
#######

Item       uom        qty
--------   --------   -----
ItemA      bag        10.0
ItemB      carton     20.0
ItemC      piece      30.0
ItemA      kg         40.0


TABLE_B:
#######

Item       uom1       uom2       factor
--------   --------   --------   -------



*** AFTER CHANGES (insertion into TABLE_B) ***

TABEL_A:
#######

Item       uom        qty
--------   --------   -----
ItemA      bag        500.0
ItemB      carton     0.66666666666666663
ItemC      piece      30.0
ItemA      kg         40.0


TABLE_B:
#######

Item       uom1       uom2       factor
--------   --------   --------   -------
ItemA      kg         bag        50.0
ItemB      kg         carton     30.0


OK... this is it!
I hope that i did understand the problem correctly and that
this is the solution you where looking for.

Regards
/ Ali Karimi




-----Ursprungligt meddelande-----
Fran: Choo SS [mailto:sschoo@a...]
Skickat: den 17 juli 2002 04:27
Till: 'sql language'
Kopia: a97alika@s...
Amne: RE: [sql_language] SV: RE: SV:


HI,

Can I know the both method? I wish to compare which is a best method for
me to use! But I have another problem, when I extract the data from DB2,
'UNION' command cannot use more than two time, is it?

-----Original Message-----
From: Ali Karimi [mailto:a97alika@s...]
Sent: Tuesday, July 16, 2002 6:33 PM
To: sql language
Subject: [sql_language] SV: RE: SV:

Which mehod do you want to use?
Do you want to do it with a Stored Procedure
or do you want to create a Trigger?

If the changes of TableB is random (that is, sometime
only one tupple/row is inserted or updated) then i would
recommend using a trigger. This way you DO NOT need to
check the hole table again and try to find the changes.
When a change is made in TableB (e.g. new tupple is inserted or
on is updated) then only that tupple can be recognized and used
for searching TableA and see if we need to make any changed there.

But if you do you changes as a Batch, i.e. sending many changes
at the same time you could also use a Stored Procedure, which probably
be faster (even though you still can use a Trigger as described above).

So, let me know which you prefere and i will show you the code, ok?

Regards
/ Ali Karimi




-----Ursprungligt meddelande-----
Fran: Jane Choo [mailto:sschoo@a...]
Skickat: den 16 juli 2002 02:54
Till: sql language
Amne: [sql_language] RE: SV:


Hi

Can you show me an example how to write a SQL statement for my case.



Thanks!



-----Original Message-----
From: Ali Karimi [mailto:a97alika@s...]
Sent: Monday, July 15, 2002 8:48 PM
To: sql language
Subject: [sql_language] SV: RE: Help with Inner Join on Two Tables

Hi,

One thing here is very clear.
You need to make updated to different tupples, depending
on some conditions.
Well, updates can not be done, for all of the tupples, at the same time
(i.e. making one only update query for taking care of all of these
condition directly).
This is a very good case where one should use a Stored Procedure for
solving this kind of problem (or a trigger depending on your design;
the trigger in that case would fire of when a change would take place
on "Table B").

For solving the actual problem, i.e. the query for making these changes
you need to (or maybe better to say can use! there are always many diff.
ways to solve a problem) use "CASE - statements" for determining wich
of the tupples the current record/tupple is refering to (e.g. ItemA or
ItemB
etc.).

I hope that you unerstand what i am trying to say and I also hope
that this is what you where looking for!

If you still don?t know how to make the query, tell me and i will show
you.

Regards
/ Ali Karimi




-----Ursprungligt meddelande-----
Fran: Jane Choo [mailto:sschoo@a...]
Skickat: den 15 juli 2002 02:28
Till: sql language
Amne: [sql_language] RE: Help with Inner Join on Two Tables


Yes, I had tried it but it just show all the data combined together. I
need a data changed to another data in the same field.

Let say

Table A (detail file)

Item     uom       qty
ItemA    bag       10
ItemB    carton     20
ItmeC    piece      30
itemA     kg        40


Table B (conversion file)

Item     uom1    uom2     factor
itemA     bag     kg      50
itemB     kg     carton   30

(itemC do not have a record in  Table B)


so now I need a Table A Qty field data in 'KG' uom unless the item does
not have a record in Table B.  after conversion, Table A data should be
as below:

Table A (detail file)
Item   uom     qty
itemA  bag     500   calculation conversion= (10bag X 50kg), uom here is
not I in 'kg'
itemB  carton  0.667 cal. Conversion=>(20carton/30carton)
itemC  piece   30    qty remained unchanged
itemA  kg      40    qty reamin unchanged because the uom in 'kg', not
necessary to convert!.

I don't know how to write the SQL statement on above method.

-----Original Message-----
rom: Joseph & Mary Taylor [mailto:joemary2@b...]
Sent: Saturday, July 13, 2002 11:06 PM
To: sql language
Subject: [sql_language] RE: Help with Inner Join on Two Tables

Have you tried a LEFT JOIN. What a left join does is it includes all the
records from you employees table and those records that have taken leave
from the LeaveReq table.

Example, in the Northwind database, you have 2 tables - one called
Customers
and one called Orders. If we have a left join on the Customers table in
the
query below, you will get a count of all the orders made by all the
customers (even if they have not placed an order). You will get 91
records,
that is how many customers you will have in the Northwind database.
Enjoy.

SELECT Customers.CustomerID, Customers.ContactName,
Count(Orders.OrderDate)
AS CountOfOrderDate
FROM Customers LEFT JOIN Orders ON Customers.CustomerID 
Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.ContactName;



-----Original Message-----
From: anne.do@c... [mailto:anne.do@c...]
Sent: 10 July 2002 22:47
To: sql language
Subject: [sql_language] Help with Inner Join on Two Tables


This is my query:
SELECT EMPLOY.*, LeaveReq.Start, LeaveReq.End
FROM EMPLOY INNER JOIN LeaveReq ON EMPLOY.EMP_NO = LeaveReq.Emp_no WHERE
(((EMPLOY.EMP_NO) In (123,444,335))) ORDER BY EMPLOY.Last_Name;

I am trying to link two tables: Employ(Emp_No, First_Name, Last_Name)
and
LeaveReq (Record_No, Emp_No, Start, End).

I want to join the Employ table on Emp_No to the LeaveReq table.  The
query I have works fine if the Employee has requested Leave and has a
corresponding record in the LeaveReq table.  If Employee #335 in the
query
below has never requested leave and thus has no record in the LeaveReq
table, his record will not show up in the join.  How can I make SQL
still
include all Employees even if they have null values for all columns in
the
LeaveReq table?


Thanks,
Anne Do
anne.do@c...