Solved

sql server 2008 query

Posted on 2013-02-01
2
228 Views
Last Modified: 2013-02-02
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.

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')

Open in new window


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')

Open in new window


TestTable1 looks like this:

TestTable1
TestTable2 looks like this:

 TestTable2
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

Desired query result set
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.
0
Comment
Question by:maqskywalker
2 Comments
 
LVL 4

Accepted Solution

by:
MimicTech earned 500 total points
ID: 38845895
try this join.

SELECT [Region]
   ,[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]

Open in new window

0
 
LVL 10

Expert Comment

by:Monica P
ID: 38846485
Hi MimicTech  query works appropriate :)

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]
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question