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  SQL Server 2000 Forums  Transact-SQL (2000)  How to create a view table with the ff. conditions

Author  Topic  mmsqlt Starting Member 1 Post Posted - 2007-10-05 : 05:31:52 Hi! I need to create and USE a view table with the conditions below. The feature in my application needs a View Table to work. 1. Table1 ITEM | LOCN | QTY Itm1 | Loc1 | 100 * My requirement is to always display an ITEM in 2 LOCATIONS (Loc1, Loc2). I got stuck here: CREATE VIEW MYVIEW1 AS select ITM, LOCN sum(QTY) as QTY from ( select ITM, LOCN, QTY from Table1 where LOCN in ('Loc1', 'Loc2') UNION select '' as ITM, 'Loc1' as LOCN, 0 as QTY UNION select '' as ITM, 'Loc2' as LOCN, 0 as QTY ) group by ITM, LOCN SELECT * FROM MYVIEW1 WHERE ITM = 'Item1' OUTPUT: Itm1 | Loc1 | 100 ---- | Loc1 | 0 ---- | Loc2 | 0 Q1. My first problem is how to force display my selected Item in all locations. If this can be done, my problem is solved. Q2. In the output above, can Loc1 be grouped to show this; Itm1 | Loc1 | 100 ---- | Loc2 | 0 Thanks in advance! SQL Server 2000