SQL command | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL command

Hello, I need to write a SQL statement which does the following: Depending on the type of transaction either go to the Supplier table or the customer table. ID COL WS are used as keyfields to the corresponding customer or supplier table. TypeTrans = 1 : suppliers
2 : customers. fe
table transactions :
field
TypeTrans ID COL WS
1 1 1 1
2 900 1 1
Supplier table ID COL WS Code
1 1 1 ABC
Customer table ID COL WS Code
900 1 1 Cust1 The result of the query should be : TypeTrans ID COL WS CODE
1 1 1 1 ABC
2 900 1 1 Cust1 How can I write such a query? Thanks
you can write this in a stored proc if @TypeTrans = 1
begin
–Supplier Table
end
else
if @TypeTrans = 2
begin
–Customer Table
end or else Select * from transaction,suppliers where transaction.TypeTrans = 1 and transaction.Id =supplier.Id
UNION
Select * from transaction,customers where transaction.TypeTrans = 2 and transaction.Id =customers.Id
—————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

Execute below code in QA :
declare @supplier table(ID int,COL int,WS int,Code varchar(10))
insert @supplier select 1, 1, 1, ‘ABC’
declare @customer table(ID int,COL int,WS int,Code varchar(10))
insert @customer select 900 ,1 ,1 ,’Cust1′ declare @transactions table(TypeTrans int,ID int,COL int,WS int)
insert @transactions
SELECT 1, 1, 1, 1 UNION ALL
SELECT 2, 900, 1, 1 select *,case typetrans
WHEN 1 then (SELECT code from @supplier s WHERE s.id=t.id)
ELSE (SELECT code from @customer c WHERE c.id=t.id) END
from @transactions t

I forgot to mention : how can we do that from Visual Basic? Ralph
To achieve it from visual basic, step1>create stored procedure with select statement and if trantype is parameterised then do what Dinesh has suggested. step2>create all your ADO connections and command in VB to fire the above SP
To complete this Step you may need to look for VB forums for getting appropriate suggestions. Step3>Retrive the resultset and display it on Screen
]]>