Solved

sql server 2008 query

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

785 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