Passing variable to table function in join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Passing variable to table function in join

Author

Topic

mrgr8avill
Starting Member

16 Posts

Posted - 2007-12-26 : 22:56:22

Hello, thanks in advance for reading this. I am having difficulty trying to get a statement to work.

There is a MAIN table:
ItemNo int identity(1,0),
ItemType tinyint

There is a WETPAINT table:
ItemNo int,
Color varchar(20)

There is a DRYPAINT table:
ItemNo int,
Color varchar(20)

Now, what I want to do is JOIN the MAIN table to either the WETPAINT table or the DRYPAINT table depending on the value of MAIN.ItemType

So I created a table function called getTable:

CREATE FUNCTION [dbo].[gettable]
(
@ItemType int = 1
)
RETURNS
@thistable TABLE
(
Color varchar(20)

)
AS
BEGIN
if @ItemType = 1
insert into @thistable (color) select color from WETPAINT
if @ItemType = 2
insert into @thistable (color) select color from DRYPAINT
RETURN
END

This is all fine and dandy if I iterate through the MAIN table one row at a time, but how can I JOIN the tables, like:

SELECT MAIN.ItemNo, a.Color
FROM MAIN
INNER JOIN gettable(Main.ItemNo) as a
ON a.ItemNo = MAIN.ItemNo

Obviously, there is more than one field in the DRYPAINT and WETPAINT tables, and there is a need to have both tables instead of combining them into one.

Any help in how to create a table alias by passing a value from the select statement would be greatly appreciated! Thanks again.

PS -- I am trying to create a view with this, so I can't use variables and iterate through the MAIN table one row at a time.

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-12-27 : 00:31:00

SELECT MAIN.ItemNo, IsNull(A.Color , B.Color)
FROM MAIN
LEFT OUTER JOIN WETPAINT A ON A.ItemNo = MAIN.ItemNo AND A.ItemType = 1
LEFT OUTER JOIN DAYPAINT B ON B.ItemNo = MAIN.ItemNo AND A.ItemType = 2

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-12-27 : 03:01:26

Hi!

Try this

select m.itemno,(case when itemtype=1 then d.color else
w.color end) as color from main m left outer join drypaint d on m.itemno=d.itemno
left outer join wetpaint w on m.itemno=w.itemno

kiruthika!
http://www.ictned.eu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources