September 18, 2014 at 8:06 am
I have two tables (warehouses and departments) that have warehouse and department as primary key fields.
The view (1) below selects all warehouses and departments.
In 2 I want to be able to join on one common field from the view.
What I would want to do (but is not possible) is create a table variable like this:
DECLARE @Employees TABLE
(
Department varchar(100),
EmployeeNumber int
)
and then insert the rows from the two tables, however, you can't do that in a view.
Any suggestions?
1.
create view vwEmp as
select warehouse, employeenumber from warehouses
union
select department, employeenumber from departments
2.
select * from employees e
join vwEmp v on e.warehouse = v.warehouse
September 18, 2014 at 8:48 am
Robert Vogelezang (9/18/2014)
I have two tables (warehouses and departments) that have warehouse and department as primary key fields.The view (1) below selects all warehouses and departments.
In 2 I want to be able to join on one common field from the view.
What I would want to do (but is not possible) is create a table variable like this:
DECLARE @Employees TABLE(
Department varchar(100),
EmployeeNumber int
)
and then insert the rows from the two tables, however, you can't do that in a view.
Any suggestions?
1.
create view vwEmp as
select warehouse, employeenumber from warehouses
union
select department, employeenumber from departments
2.
select * from employees e
join vwEmp v on e.warehouse = v.warehouse
Of course you can't insert into a table variable from a view. That doesn't work on any level. However, if you want the values from a view inserted into a table variable you just need an insert statement.
INSERT @Employees
select * from employees e
join vwEmp v on e.warehouse = v.warehouse
Of course this begs the question of why do you need the values of a view inserted into a table variable but that is another topic entirely.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2014 at 9:02 am
Thanks for your reply. Maybe I have not explained my problem good enough.
I'll try again.
I have two tables:
warehouses
warehouse (pk)
departments
department (pk)
create view as
select warehouse from warehouses -- > how do I make warehouse CompCode ?
union
select department from departments -- > how do I make department CompCode ?
I have a stored procedure that selects from a view like this:
select * from Employees e
join vwEmp v on e.CompCode = v.CompCode
I have to be able to join on one common field in vwEmp, how?
September 18, 2014 at 9:08 am
Robert Vogelezang (9/18/2014)
Thanks for your reply. Maybe I have not explained my problem good enough.I'll try again.
I have two tables:
warehouses
warehouse (pk)
departments
department (pk)
create view as
select warehouse from warehouses -- > how do I make warehouse CompCode ?
union
select department from departments -- > how do I make department CompCode ?
I have a stored procedure that selects from a view like this:
select * from Employees e
join vwEmp v on e.CompCode = v.CompCode
I have to be able to join on one common field in vwEmp, how?
Are you wanting to make the name of the second column in your view CompCode?
create view as
select warehouse as CompCode from warehouses
union
select department from departments
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2014 at 9:10 am
Are you thinking that somehow your view has two columns or two names? When you use UNION or UNION ALL (which if probably more appropriate here) the column name will be picked up from the first query in the union. There is no point in naming columns after the first query because they will be ignored.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2014 at 9:41 am
My goodness, that simple!
Thanks!
September 18, 2014 at 10:03 am
Robert Vogelezang (9/18/2014)
My goodness, that simple!Thanks!
No problem. Glad that worked for you and thanks for letting me know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply