March 11, 2008 at 8:35 am
I have the following code:
DECLARE @COnsultantID CHAR(20)
DECLARE @StartDate Datetime
DECLARE @EndDate Datetime
SET @ConsultantID = '0000028'
SET @StartDate ='01/01/2008'
SET @EndDate =GetDate()
SelectDISTINCT o.OrderNumber
,o.OrderCreateDate
,o.ConsultantID
,(Select Top 1 n.FirstName + ' ' + n.LastName FRom COnsultant n
WHERE n.COnsultantID = @COnsultantID) AS ConsultantName
,c.BillToAddressLine1
,c.BillToAddressLine2
,c.BillToCity
,c.BillToState
,c.BillToZip
,c.BillToPhone
,f.CareerQuestion
,f.InfoQuestion
,f.HostPartyQuestion
from Consultant c
LEFT OUTER JOIN dbo.uvw_OrderListingSummary o ON c.ConsultantID = o.consultantID
LEFT Outer Join OrderForm f ON o.OrderGroupNumber = f.ORderGroupNumber
AND o.OrderNumber = f.OrderNumber
WHERE o.Ordertypexid = 8 AND o.COnsultantID = @ConsultantID
AND o.OrderCreateDate Between @StartDate AND @EndDate
The issue is the record set return 2 rows for each OrderNumber, one that is valid and on that contains null values. How can I suppress the row with Null values?
Recordset:
OrderNumber OrderDate ConsID Consname
Row 1 79823812008-01-04 09:16:53.2930000028 Betty EvansNULLNULLNULLNULLNULLNULL000
Row 2 79823812008-01-04 09:16:53.2930000028 Betty Evans28404 97TH STZimmermanMN553987633894048000
Row 3 80488872008-01-25 10:13:58.0930000028 Betty EvansNULLNULLNULLNULLNULLNULL010
Row 4 80488872008-01-25 10:13:58.0930000028 Betty Evans28404 97TH STZimmermanMN553987633894048010
As you can see each ordernumber has two records and I need to get rid of the one with the Null values. Any ideas?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 11, 2008 at 8:43 am
Add a "Where is not null" to the where clause.
Example: "and c.address1 is not null"
Your best bet, since it looks like the nulls are coming from the table with the address data in it, is to include the primary key of that table as the not null column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2008 at 8:45 am
It means your joins are probably returning more than one row from one of the tables. That's a pretty normal occurrence when using left joins. I'd first investigate the data there to verify your joins are correct.
However, a quick fix would be to simply add 'AND ColumnX IS NOT NULL' to the WHERE clause. But that's not addressing the underlying issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2008 at 10:06 pm
It appears that you have a duplicate Consultant row with ConsultantID=28 and the second one has nulls in the data columns.
either that or that first LEFT OUTER JOIN is really a RIGHT OUTER JOIN.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 12, 2008 at 6:55 am
The table that the address is kept is has multiple records per consultantID in which some of the addresses are blank:
select consultantid, max(effectivedate),c.BillToAddressLine1
,c.BillToAddressLine2
,c.BillToCity
,c.BillToState
,c.BillToZip
,c.BillToPhone
from consultant c where consultantID = '0000028'
GROUP BY consultantid,c.BillToAddressLine1
,c.BillToAddressLine2
,c.BillToCity
,c.BillToState
,c.BillToZip
,c.BillToPhone
This returns two rows and not one that I expect and one of the rows is null:
0000028 2007-11-15 16:17:20.467NULLNULLNULLNULLNULLNULL
0000028 2007-12-04 13:58:43.00028404 97TH STZimmermanMN553987633894048
So what is going on? Shouldn't this code give me the record with the latest effective date for this consultant?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 12, 2008 at 7:12 am
Because you're grouping on everything, you'll get the max date for a distict select. You might want to look at something like:
SELECT c.consultantid
,e.EffectiveDate
,c.BillToAddressLine1
,c.BillToAddressLine2
,c.BillToCity
,c.BillToState
,c.BillToZip
,c.BillToPhone
FROM consultant c
JOIN ( SELECT c2.ConsultantId
,MAX(effectivedate) EffectiveDate
FROM consultant c2
GROUP BY consultantid
) e
ON c.ConsultantId = e.ConsultantId
AND c.EffectiveDate = e.EffectiveDate
WHERE consultantID = '0000028'
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2008 at 7:16 am
Cool. That works great. Thanks a lot too everybodies invaluable input.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 12, 2008 at 8:36 am
I guess I didn't get all the spec, they said that I needed to return the consultantID and ConsultantName even if the initial recordset came back empty:
SELECT o.orderNumber
,c.consultantid
,(Select top 1 firstname + ' ' + Lastname from Consultant
WHERE consultantid = @ConsultantID) As Consultantname
,e.EffectiveDate
,c.BillToAddressLine1
,c.BillToAddressLine2
,c.BillToCity
,c.BillToState
,c.BillToZip
,c.BillToPhone
,f.CareerQuestion
,f.InfoQuestion
,f.HostPartyQuestion
FROM consultant c
JOIN ( SELECT c2.ConsultantId
,MAX(effectivedate) EffectiveDate
FROM consultant c2
GROUP BY consultantid
) e
ON c.ConsultantId = e.ConsultantId
AND c.EffectiveDate = e.EffectiveDate
Left Outer JOIN dbo.uvw_OrderListingSummary o ON c.ConsultantID = o.consultantID
LEFT Outer Join OrderForm f ON o.OrderGroupNumber = f.ORderGroupNumber
AND o.OrderNumber = f.OrderNumber
WHERE o.Ordertypexid = 8 AND c.COnsultantID = @ConsultantID
AND o.OrderCreateDate Between @StartDate AND @EndDate
--OR o.OrderCreateDate IS NULL
I tried using OR o.OrderCreateDate IS NULL but that return rows with the OrderNumber that are NULL.
What i need is to return just a single row containing ConsultantID and ConsultantName if the initial recordset comes back empty so in actuality I should never have a empty record there should always be at least one record.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 12, 2008 at 8:56 am
Get rid of the TOP 1 sub select in the SELECT clause, that's effectively a cursor in your code.
Try using a LEFT JOIN?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2008 at 9:57 am
The issue of getting rid to the SELECT TOP 1 clause is then it returns multiple records in the subquery which is a no no.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 12, 2008 at 10:21 am
No, I mean get rid of the sub-select entirely. Why would you need it? If the name is in the consultant table then you should be able to pull it from there.
If you need records whether or not there is a consultant in the consultant table, you may need to restructure the joins to put whatever table is the root table, first.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2008 at 10:27 am
I got rid of the Select Top 1 statement and that worked and the consultant table is the first in the FROM clause so I am a little confused now.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 12, 2008 at 10:28 am
Sorry, I meant to add the From clause:
FROM consultant c
JOIN ( SELECT c2.ConsultantId
,MAX(effectivedate) EffectiveDate
FROM consultant c2
GROUP BY consultantid
) e
ON c.ConsultantId = e.ConsultantId
AND c.EffectiveDate = e.EffectiveDate
Left Outer JOIN dbo.uvw_OrderListingSummary o ON c.ConsultantID = o.consultantID
LEFT Outer Join OrderForm f ON o.OrderGroupNumber = f.ORderGroupNumber
AND o.OrderNumber = f.OrderNumber
WHERE o.Ordertypexid = 8 AND c.COnsultantID = @ConsultantID
AND o.OrderCreateDate Between @StartDate AND @EndDate
OR o.OrderCreateDate IS NULL
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 12, 2008 at 11:29 am
I have to admit I don't understand the purpose of the inline sub-query in this. It's pulling data from the same table as the main query, using the same Where clause.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 12, 2008 at 11:32 am
The reason for the inline query is because the consultant table holds multiple records for a single consultant and the inline query gives me the most recent record for the consultant.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply