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
Solved

sql server 2008 query

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

808 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