maqskywalker
asked on
sql server 2008 query
Hi experts,
I'm using sql server 2008 and sql server 2008 management studio.
I have two tables. For easy reference here is the scripts to create both of my tables.
TestTable1 looks like this:
TestTable2 looks like this:
So this is what I want.
If you look at the MyFlag column of TestTable1 there are only values of Y or N in this column.
If the value of this column equals Y then I want to replace Y with the word "Obsolete"
If the value of this column equals N then I want to replace N with either the word '"Open" or "Closed".
To determine whether the N should be replaced with Open or Closed I need to look at TestTable2 for the same OrderNo, and I need to look the OrderStatus column in this table. If the OrderStatus column contains a value of O then that means this order is "Open", it it contains value of "C" then that means it's "Closed", so then in the MyFlag column of TestTable1 for that OrderNo I would replace N with the word "Open" or "Closed"
For example if we look at OrdeNo = 1 in TestTable1 the value of the MyFlag column is N. So then I would look at TestTable2 for that OrderNo.
In TestTable2 I can seed the for OrderNo 1 the OrderStatus is "C" and then i would want the word Closed put in TestTable1 in the MyFlag column for that OrderNo.
So this is what I would want my result output of my query to look like. Basically it looks just like TestTable 1 except with the words Open or Closed in the MyFlag column
When the value of the MyFlag column of TestTable1 = Y I handle that like shown in this query:
Query 1
SELECT [Region]
,[Year]
,[OrderNo]
,CASE [MyFlag]
WHEN 'Y' THEN 'Obsolete'
WHEN 'N' THEN 'N'
END AS MyFlag
FROM [Test].[dbo].[TestTable1]
The part I'm having trouble with is handling when the MyFlag column of TestTable1 = N
I was thinking of nesting the CASE statement from Query 2 below into Query 1 right after where I say WHEN 'N' but I'm not sure that's right.
Query 2
SELECT [Region]
,[Year]
,[OrderNo]
,CASE [OrderStatus]
WHEN 'C' THEN 'Closed'
WHEN 'O' THEN 'Open'
ELSE 'Unknown'
END
FROM [Test].[dbo].[TestTable2]
Can anyone help with this query?
Thanks.
I'm using sql server 2008 and sql server 2008 management studio.
I have two tables. For easy reference here is the scripts to create both of my tables.
CREATE TABLE [dbo].[TestTable1](
[Region] [int] NOT NULL,
[Year] [int] NOT NULL,
[OrderNo] [int] NULL,
[MyFlag] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable1] ([Region], [Year], [OrderNo], [MyFlag]) VALUES (1, 2013, 1, N'N')
INSERT [dbo].[TestTable1] ([Region], [Year], [OrderNo], [MyFlag]) VALUES (1, 2013, 2, N'N')
INSERT [dbo].[TestTable1] ([Region], [Year], [OrderNo], [MyFlag]) VALUES (1, 2013, 3, N'N')
INSERT [dbo].[TestTable1] ([Region], [Year], [OrderNo], [MyFlag]) VALUES (1, 2013, 4, N'Y')
INSERT [dbo].[TestTable1] ([Region], [Year], [OrderNo], [MyFlag]) VALUES (1, 2013, 5, N'Y')
INSERT [dbo].[TestTable1] ([Region], [Year], [OrderNo], [MyFlag]) VALUES (1, 2013, 6, N'N')
INSERT [dbo].[TestTable1] ([Region], [Year], [OrderNo], [MyFlag]) VALUES (1, 2013, 7, N'N')
INSERT [dbo].[TestTable1] ([Region], [Year], [OrderNo], [MyFlag]) VALUES (1, 2013, 8, N'Y')
INSERT [dbo].[TestTable1] ([Region], [Year], [OrderNo], [MyFlag]) VALUES (1, 2013, 9, N'N')
INSERT [dbo].[TestTable1] ([Region], [Year], [OrderNo], [MyFlag]) VALUES (1, 2013, 10, N'Y')CREATE TABLE [dbo].[TestTable2](
[Region] [int] NOT NULL,
[Year] [int] NOT NULL,
[OrderNo] [int] NULL,
[OrderStatus] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable2] ([Region], [Year], [OrderNo], [OrderStatus]) VALUES (1, 2013, 1, N'C')
INSERT [dbo].[TestTable2] ([Region], [Year], [OrderNo], [OrderStatus]) VALUES (1, 2013, 2, N'C')
INSERT [dbo].[TestTable2] ([Region], [Year], [OrderNo], [OrderStatus]) VALUES (1, 2013, 3, N'C')
INSERT [dbo].[TestTable2] ([Region], [Year], [OrderNo], [OrderStatus]) VALUES (1, 2013, 4, N'M')
INSERT [dbo].[TestTable2] ([Region], [Year], [OrderNo], [OrderStatus]) VALUES (1, 2013, 5, N'M')
INSERT [dbo].[TestTable2] ([Region], [Year], [OrderNo], [OrderStatus]) VALUES (1, 2013, 6, N'O')
INSERT [dbo].[TestTable2] ([Region], [Year], [OrderNo], [OrderStatus]) VALUES (1, 2013, 7, N'O')
INSERT [dbo].[TestTable2] ([Region], [Year], [OrderNo], [OrderStatus]) VALUES (1, 2013, 8, N'M')
INSERT [dbo].[TestTable2] ([Region], [Year], [OrderNo], [OrderStatus]) VALUES (1, 2013, 9, N'C')
INSERT [dbo].[TestTable2] ([Region], [Year], [OrderNo], [OrderStatus]) VALUES (1, 2013, 10, N'M')TestTable1 looks like this:
TestTable2 looks like this:
So this is what I want.
If you look at the MyFlag column of TestTable1 there are only values of Y or N in this column.
If the value of this column equals Y then I want to replace Y with the word "Obsolete"
If the value of this column equals N then I want to replace N with either the word '"Open" or "Closed".
To determine whether the N should be replaced with Open or Closed I need to look at TestTable2 for the same OrderNo, and I need to look the OrderStatus column in this table. If the OrderStatus column contains a value of O then that means this order is "Open", it it contains value of "C" then that means it's "Closed", so then in the MyFlag column of TestTable1 for that OrderNo I would replace N with the word "Open" or "Closed"
For example if we look at OrdeNo = 1 in TestTable1 the value of the MyFlag column is N. So then I would look at TestTable2 for that OrderNo.
In TestTable2 I can seed the for OrderNo 1 the OrderStatus is "C" and then i would want the word Closed put in TestTable1 in the MyFlag column for that OrderNo.
So this is what I would want my result output of my query to look like. Basically it looks just like TestTable 1 except with the words Open or Closed in the MyFlag column
When the value of the MyFlag column of TestTable1 = Y I handle that like shown in this query:
Query 1
SELECT [Region]
,[Year]
,[OrderNo]
,CASE [MyFlag]
WHEN 'Y' THEN 'Obsolete'
WHEN 'N' THEN 'N'
END AS MyFlag
FROM [Test].[dbo].[TestTable1]
The part I'm having trouble with is handling when the MyFlag column of TestTable1 = N
I was thinking of nesting the CASE statement from Query 2 below into Query 1 right after where I say WHEN 'N' but I'm not sure that's right.
Query 2
SELECT [Region]
,[Year]
,[OrderNo]
,CASE [OrderStatus]
WHEN 'C' THEN 'Closed'
WHEN 'O' THEN 'Open'
ELSE 'Unknown'
END
FROM [Test].[dbo].[TestTable2]
Can anyone help with this query?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To avoid this error
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Region'.
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'Year'.
use the t1 or t2 instance name before the fieldname
SELECT t1.[Region]
,t1.[Year]
,t1.[OrderNo]
,CASE [MyFlag]
WHEN 'Y' THEN 'Obsolete'
WHEN 'N' THEN
CASE [OrderStatus]
WHEN 'C' THEN 'Closed'
WHEN 'O' THEN 'Open'
ELSE 'Unknown'
END
END AS MyFlag
FROM [Test].[dbo].[TestTable1] t1
JOIN [Test].[dbo].[TestTable2] t2
ON t2.[OrderNo] = t1.[OrderNo]