Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query help - Incorporating a new piece of business logic into an existing query

Posted on 2012-08-22
12
Medium Priority
?
525 Views
Last Modified: 2012-08-22
I have the following stored proc being called by an application which pulls 11,000 products.  
SELECT 
	p.PRODUCT_ID, 
	PUB_DATE_WN = ISNULL(upd.PUB_DATE, ''), 				
	STD_ABBV_CODE = ISNULL(upd.STD_ABBV_CODE, ''),
	TITLE = ISNULL(p.LONG_NAME, ''),
	PUB_YEAR_INV = ISNULL(f.USR_PUB_YEAR, ''),
	PUBLISHER_WN = ISNULL(upd.PUBLISHER, ''),
	TEXT = ISNULL(t.TEXT, ''), 
	MEETING_LOCATION = ISNULL(mpl.LOCATION, ''),
	PRODUCT_URL = ISNULL(pwc.PRODUCT_URL, '') 
FROM dbo.WEB_PRODUCT_VW p
	LEFT OUTER JOIN dbo.PRODUCT_TEXT t ON t.PRODUCT_ID = p.PRODUCT_ID and t.TEXT_TYPE_CODE = 'WEB_LONG'
	LEFT OUTER JOIN dbo.USR_PRODUCT_FACTS f ON f.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.INV_PRODUCT i ON i.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.USR_PRODUCT_DETAIL upd ON upd.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.MTG_PRODUCT_LOCATION mpl ON mpl.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.PRODUCT_WEB_CONTROL pwc ON pwc.PRODUCT_ID = p.PRODUCT_ID
WHERE (p.SUBSYSTEM = 'INV' OR p.SUBSYSTEM = 'ECD' OR p.SUBSYSTEM = 'MTG' OR p.SUBSYSTEM = 'MBR') 
	   AND ((p.PRODUCT_CODE = p.PARENT_PRODUCT) OR (p.PRODUCT_ID = '30' OR p.PRODUCT_ID = '82' OR p.PRODUCT_ID = '264')) 
	   AND p.AVAILABLE_TO_ORDERS_FLAG = 'Y'
	   AND p.END_DATE >= CURRENT_TIMESTAMP

Open in new window


It is actually pulling a lot more in the select fields but I eliminated a lot of them for readability.

So a partial set of results could look like:
PRODUCT_ID    PUB_DATE_WN    STD_ABBV     TITLE                                     ... other fields
1                      1997-01-01          Z100              Z100-97 standard  
2                      1998-01-01          B100              B100-98 standard
3                       2012-01-01         NULL              A book
4                       2012-05-18         NULL              A Journal article
5                      2012-01-01          B100              B100-12 standard <-- only pull this
6                      2010-01-01          Z100              Z100-10 standard <-- only pull this

So what I need to do hopefully is adjust the existing query to continue pulling all of the products including things like books and journal articles except for when STD_ABBV has the same value (i.e. Z100 or B100 in this example) only pull the one with the latest pub date.
0
Comment
Question by:PurpleSlade
  • 5
  • 5
  • 2
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38321338
a kind of DISTINCT / GROUP BY , but not possible with just that.
see this article that helps to solve this kind of t-sql problems easly with sql 2005+ :
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 38321384
I read this yesterday when you posted this in my other question.  I am not sure why you are reposting it again after I took the time to reclarify my question.  I would appreciate getting some actual advice on the query itself.
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38321385
SELECT PRODUCT_ID,
	   PUB_DATE_WN,
	   STD_ABBV,
	   TITLE FROM (
SELECT YourTable.PRODUCT_ID,
	   YourTable.PUB_DATE_WN,
	   YourTable.STD_ABBV,
	   YourTable.TITLE,
	   ROW_NUMBER() OVER (PARTITION BY STD_ABBV ORDER BY PUB_DATE_WN DESC) AS RowNo
	   FROM YourTable) T 
	   WHERE T.RowNo = 1

Open in new window


Your query could be written like this:
SELECT * FROM (SELECT 
	p.PRODUCT_ID PRODUCT_ID, 
	ISNULL(upd.PUB_DATE, '') PUB_DATE_WN, 				
	ISNULL(upd.STD_ABBV_CODE, '') STD_ABBV_CODE,
	ISNULL(p.LONG_NAME, '') TITLE,
	ISNULL(f.USR_PUB_YEAR, '') PUB_YEAR_INV ,
	ISNULL(upd.PUBLISHER, '') PUBLISHER_WN ,
	ISNULL(t.TEXT, '') [TEXT], 
	ISNULL(mpl.LOCATION, '') MEETING_LOCATION ,
	ISNULL(pwc.PRODUCT_URL, '') PRODUCT_URL ,
	ROW_NUMBER() OVER (PARTITION BY upd.STD_ABBV_CODE ORDER BY upd.PUB_DATE DESC) RowNo
FROM dbo.WEB_PRODUCT_VW p
	LEFT OUTER JOIN dbo.PRODUCT_TEXT t ON t.PRODUCT_ID = p.PRODUCT_ID and t.TEXT_TYPE_CODE = 'WEB_LONG'
	LEFT OUTER JOIN dbo.USR_PRODUCT_FACTS f ON f.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.INV_PRODUCT i ON i.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.USR_PRODUCT_DETAIL upd ON upd.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.MTG_PRODUCT_LOCATION mpl ON mpl.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.PRODUCT_WEB_CONTROL pwc ON pwc.PRODUCT_ID = p.PRODUCT_ID
WHERE (p.SUBSYSTEM = 'INV' OR p.SUBSYSTEM = 'ECD' OR p.SUBSYSTEM = 'MTG' OR p.SUBSYSTEM = 'MBR') 
	   AND ((p.PRODUCT_CODE = p.PARENT_PRODUCT) OR (p.PRODUCT_ID = '30' OR p.PRODUCT_ID = '82' OR p.PRODUCT_ID = '264')) 
	   AND p.AVAILABLE_TO_ORDERS_FLAG = 'Y'
	   AND p.END_DATE >= CURRENT_TIMESTAMP) T
WHERE T.RowNo = 1

Open in new window

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

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38321417
sorry, I had not noticed that you "reposted" your question...
I see that pourfard has taken your query and added what I describe indeed in the article: the ROW_NUMBER() with a PARTITION BY the STD_ABBV_CODE, ordering DESC on the date, and in the outer query, filtering to get only row number 1 (per STD_ABBC_CODE), which should do the job, even for those rows that don't have a date value ..
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 38321544
The query executes, but it eliminates all of the other products.  Instead of getting around 11,000 products, I now only get the 158 most current standards.  As was mentioned in the question I need to keep all of the rest of the products.
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38321558
May be you need to include Null STD_ABBV_CODE, in this case try:
SELECT * FROM (SELECT 
	p.PRODUCT_ID PRODUCT_ID, 
	ISNULL(upd.PUB_DATE, '') PUB_DATE_WN, 				
	ISNULL(upd.STD_ABBV_CODE, '') STD_ABBV_CODE,
	ISNULL(p.LONG_NAME, '') TITLE,
	ISNULL(f.USR_PUB_YEAR, '') PUB_YEAR_INV ,
	ISNULL(upd.PUBLISHER, '') PUBLISHER_WN ,
	ISNULL(t.TEXT, '') [TEXT], 
	ISNULL(mpl.LOCATION, '') MEETING_LOCATION ,
	ISNULL(pwc.PRODUCT_URL, '') PRODUCT_URL ,
	ROW_NUMBER() OVER (PARTITION BY upd.STD_ABBV_CODE ORDER BY upd.PUB_DATE DESC) RowNo
FROM dbo.WEB_PRODUCT_VW p
	LEFT OUTER JOIN dbo.PRODUCT_TEXT t ON t.PRODUCT_ID = p.PRODUCT_ID and t.TEXT_TYPE_CODE = 'WEB_LONG'
	LEFT OUTER JOIN dbo.USR_PRODUCT_FACTS f ON f.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.INV_PRODUCT i ON i.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.USR_PRODUCT_DETAIL upd ON upd.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.MTG_PRODUCT_LOCATION mpl ON mpl.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.PRODUCT_WEB_CONTROL pwc ON pwc.PRODUCT_ID = p.PRODUCT_ID
WHERE (p.SUBSYSTEM = 'INV' OR p.SUBSYSTEM = 'ECD' OR p.SUBSYSTEM = 'MTG' OR p.SUBSYSTEM = 'MBR') 
	   AND ((p.PRODUCT_CODE = p.PARENT_PRODUCT) OR (p.PRODUCT_ID = '30' OR p.PRODUCT_ID = '82' OR p.PRODUCT_ID = '264')) 
	   AND p.AVAILABLE_TO_ORDERS_FLAG = 'Y'
	   AND p.END_DATE >= CURRENT_TIMESTAMP) T
WHERE T.RowNo = 1 OR upd.STD_ABBV_CODE IS NULL 

Open in new window

0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 38321575
Hi - when I try that I get the following message: the multi-part identifier "upd.STD_ABBV_CODE" could not be bound.
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38321583
change upd.STD_ABBV_CODE to T.STD_ABBV_CODE in the last line:
SELECT * FROM (SELECT 
	p.PRODUCT_ID PRODUCT_ID, 
	ISNULL(upd.PUB_DATE, '') PUB_DATE_WN, 				
	ISNULL(upd.STD_ABBV_CODE, '') STD_ABBV_CODE,
	ISNULL(p.LONG_NAME, '') TITLE,
	ISNULL(f.USR_PUB_YEAR, '') PUB_YEAR_INV ,
	ISNULL(upd.PUBLISHER, '') PUBLISHER_WN ,
	ISNULL(t.TEXT, '') [TEXT], 
	ISNULL(mpl.LOCATION, '') MEETING_LOCATION ,
	ISNULL(pwc.PRODUCT_URL, '') PRODUCT_URL ,
	ROW_NUMBER() OVER (PARTITION BY upd.STD_ABBV_CODE ORDER BY upd.PUB_DATE DESC) RowNo
FROM dbo.WEB_PRODUCT_VW p
	LEFT OUTER JOIN dbo.PRODUCT_TEXT t ON t.PRODUCT_ID = p.PRODUCT_ID and t.TEXT_TYPE_CODE = 'WEB_LONG'
	LEFT OUTER JOIN dbo.USR_PRODUCT_FACTS f ON f.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.INV_PRODUCT i ON i.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.USR_PRODUCT_DETAIL upd ON upd.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.MTG_PRODUCT_LOCATION mpl ON mpl.PRODUCT_ID = p.PRODUCT_ID
	LEFT OUTER JOIN dbo.PRODUCT_WEB_CONTROL pwc ON pwc.PRODUCT_ID = p.PRODUCT_ID
WHERE (p.SUBSYSTEM = 'INV' OR p.SUBSYSTEM = 'ECD' OR p.SUBSYSTEM = 'MTG' OR p.SUBSYSTEM = 'MBR') 
	   AND ((p.PRODUCT_CODE = p.PARENT_PRODUCT) OR (p.PRODUCT_ID = '30' OR p.PRODUCT_ID = '82' OR p.PRODUCT_ID = '264')) 
	   AND p.AVAILABLE_TO_ORDERS_FLAG = 'Y'
	   AND p.END_DATE >= CURRENT_TIMESTAMP) T
WHERE T.RowNo = 1 OR T.STD_ABBV_CODE IS NULL 

Open in new window

0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 38321595
Yes, unfortunately this still eliminates the majority of the products.  It returns only one row with a null std_abbv_code.
0
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 2000 total points
ID: 38321630
ok replace the last line with:

WHERE T.RowNo = 1 OR  T.STD_ABBV_CODE = ''
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38321817
if it didn't work comment the last line:
 --WHERE T.RowNo = 1 OR  T.STD_ABBV_CODE = ''

Then execute the query, you should see all the records, check the records with RowNo greater than 1. They are the ones which should not appear in the final result.
0
 
LVL 2

Author Closing Comment

by:PurpleSlade
ID: 38322327
That worked great, thanks very much for your help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

810 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