?
Solved

sql server 2008 query

Posted on 2013-02-01
2
Medium Priority
?
230 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 4

Accepted Solution

by:
MimicTech earned 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

777 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