Solved

sql server 2008 query

Posted on 2013-02-01
2
229 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 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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