March 4, 2008 at 2:14 pm
I need to be able to a row that contains the name of the consultant even if the main SELECT statement returns a empty recordset.
code:
DECLARE @ConsultantID VARCHAR(20)
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @COnsultantID = '0030539'
SET @StartDate = '12-01-2007'
SET @EndDate = '12-15-2007'
SELECTDISTINCT A.TSOrderType
,A.TSUserType
,A.InternalUserName
,A.OrderTypeXID
,D.OrderCategory
,D.OrderType
--,A.OrderStatusXID
--,C.OrderStatus
,A.OrderGroupType
,CONVERT(VARCHAR(10),A.ORderCreateDate,101) AS OrderCreateDate
,'G'+ CONVERT(VARCHAR(20),A.ORderGroupNumber) AS OrderGroupNumber
,A.OrderNumber
,A.PartOneTotal
,A.PartTwoTotal
,A.PartThreeTotal
,A.Hostcredit
,A.SampleAllowanceAmount
,'Discounts' =
Case D.OrderCategory
when 'Client' THEN A.TSODiscountAmount
ELSE A.ConsultantDiscountAmount
END
,A.ConsultantDiscountAmount
,B.[ShippedDate]
,'ShipDate' =
CASE A.OrderStatusXID
WHEN 32 THEN Convert(nvarchar(10),Max(B.ShippedDate),101)
WHEN 16 THEN 'Partial'
WHEN 8 THEN 'Unshipped'
WHEN 128 THEN 'Unshipped'
END
,A.ShipToFirstName + ' ' +A.ShipToLastName AS ShipToName
,A.ShippingTotal
,A.OrderTotal
,A.TaxTotal
,A.ConsultantID
,(SELECT TOP 1 FirstName + ' ' + LastName FROM [Consultant] c
WHERE a.ConsultantID = c.ConsultantID) AS ConsultantName
FROM uvw_OrderListingSummary A
LEFT OUTER JOIN dbo.OrderFormLineItem AS B ON
A.OrderGroupNumber = B.OrderGroupNumber
ANDA.OrderNumber = B.OrderNumber
--LEFT OUTER JOIN SharedDimension.dbo.DimOrderStatus AS c With (noLock) ON
--A.OrderStatusXID = c.XID
LEFT OUTER JOIN SharedDimension.dbo.DimOrderType AS D With (NoLock) On
A.OrderTypeXId = D.XID
WHEREA.ConsultantID = @ConsultantID AND
A.OrderCreateDate Between Convert(varchar(10),@StartDate,101) AND Convert(varchar(10),@EndDate,101)
GROUP BY
A.TSOrderType
,A.TSUserType
,A.InternalUserName
,A.OrderGroupType
,A.ORderCreateDate
,A.ORderGroupNumber
,A.OrderNumber
,A.PartOneTotal
,A.PartTwoTotal
,A.PartThreeTotal
,A.Hostcredit
,A.SampleAllowanceAmount
,A.ConsultantDiscountAmount
,A.TSODiscountAmount
,A.ShippingTotal
,A.TaxTotal
,A.OrderTotal
,b.ShippedDate
,A.ShipToFirstName
,A.ShipToLastName
,A.OrderStatusXID
,A.ShipToEmail
,A.OrderTypeXID
,D.OrderCategory
,D.OrderType
--,C.OrderStatus
,A.ConsultantID
,A.ConsultantName
ORDER BY OrderCreateDate Desc
,OrderGroupNumber
,OrderNumber
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 4, 2008 at 2:21 pm
Sorry, but this doesn't quite make sense:
I need to be able to a row that contains the name of the consultant even if the main SELECT statement returns a empty recordset.
Could you please restate the problem?
Thanks
😎
March 4, 2008 at 2:28 pm
The proc is running a Reporting Service report and in the header information of the report we have the consultant name which is based on a consultantid parameter coming in, what is currently happening is if the recordset is returned empty we are loosing the consultant name in the header. We need to keep the consultant name in the header regardless if there is an empt recordset being returned or not.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 4, 2008 at 2:32 pm
I am posting what is happening in RS.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 5, 2008 at 6:40 am
Have you tried putting the 'Consultant' table in the main 'from' and then left joining the tables after it, that way you ALWAYS get the Consultant record (assuming it exists) and therefore, the Consultants Name, in the dataset.
If it was easy, everybody would be doing it!;)
March 5, 2008 at 6:45 am
Agreed - if you want the consultant name - you need to have it in the main "from", and it needs to somehow be the base table. Not quite sure what your relationships will look like, though
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 5, 2008 at 6:51 am
I think I am doing that. This is the FROM clause:
FROM uvw_OrderListingSummary A
LEFT OUTER JOIN Consultant h ON
a.consultantid=h.[ConsultantID]
LEFT OUTER JOIN dbo.OrderFormLineItem AS B ON
A.OrderGroupNumber = B.OrderGroupNumber
ANDA.OrderNumber = B.OrderNumber
LEFT OUTER JOIN SharedDimension.dbo.DimOrderType AS D With (NoLock) On
A.OrderTypeXId = D.XID
WHEREA.ConsultantID = @ConsultantID AND
A.ORderCreateDate Between Convert(varchar(10),@StartDate,101) AND Convert(varchar(10),@EndDate,101)
I am thinking there needs to be a way where I will never return an empty recordset. There should always be one record containing the consultantId and name.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 5, 2008 at 6:59 am
alorenzini (3/5/2008)
FROM uvw_OrderListingSummary ALEFT OUTER JOIN Consultant h ON
a.consultantid=h.[ConsultantID]
LEFT OUTER JOIN dbo.OrderFormLineItem AS B ON
A.OrderGroupNumber = B.OrderGroupNumber
ANDA.OrderNumber = B.OrderNumber
LEFT OUTER JOIN SharedDimension.dbo.DimOrderType AS D With (NoLock) On
A.OrderTypeXId = D.XID
WHEREA.ConsultantID = @ConsultantID AND
A.ORderCreateDate Between Convert(varchar(10),@StartDate,101) AND Convert(varchar(10),@EndDate,101)
Consultant must the table that always returns records...right now you have it LEFT JOINED, so if no records in the 'uvw_OrderListingSummary', then you won't get any Consultant records either. Put Consultant first and then LEFT join uvw_OrderListingSummary and see what that gives you.
If it was easy, everybody would be doing it!;)
March 5, 2008 at 7:04 am
Still gives me a empty record set.
FROM Consultant H
LEFT OUTER JOIN [uvw_OrderListingSummary] A ON
h.consultantid=a.[ConsultantID]
LEFT OUTER JOIN dbo.OrderFormLineItem AS B ON
A.OrderGroupNumber = B.OrderGroupNumber
ANDA.OrderNumber = B.OrderNumber
LEFT OUTER JOIN SharedDimension.dbo.DimOrderType AS D With (NoLock) On
A.OrderTypeXId = D.XID
WHEREA.ConsultantID = @ConsultantID AND
A.ORderCreateDate Between Convert(varchar(10),@StartDate,101) AND Convert(varchar(10),@EndDate,101)
GROUP BY
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 5, 2008 at 7:07 am
...check the 'where' clause
If it was easy, everybody would be doing it!;)
March 5, 2008 at 7:16 am
That was the issue. I added
OR OrdercreateDate IS NULL
And it works now. Thanks for all the help.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply