May 7, 2009 at 4:36 am
Hi All,
Can anyone help explain the following behaviour:
The following query works fine but isn't ordered:
SELECTLA.Date,
LA.ContactId,
A1.Forename + ' ' + A1.Surname,
LIA.OwnerAgent
FROMLeadAllocation LA
INNER JOIN Agreement A ON A.AgreementId = LA.AgreementId
INNER JOIN LifeInfo LI ON LI.ContactId = LA.ContactId
INNER JOIN Agent A1 ON A1.DomainUserId = LA.AgentId
AND LA.Date > A1.StartDate
AND (A1.EndDate IS NULL
OR LA.Date < A1.EndDate)
LEFT OUTER JOIN LifeInfoAgentAudit LIA ON LIA.ContactId = LA.ContactId
AND LIA.Date = (SELECT MAX(LIA1.Date)
FROM LifeInfoAgentAudit LIA1
WHERE LIA1.ContactId = LA.ContactId
AND LIA1.OwnerAgent != A1.Forename +' '+ A1.Surname
AND LIA1.Date < LA.Date)
WHEREA.AgreementType = 'Life'
ANDLA.Date > CONVERT(DATETIME, CONVERT(VARCHAR(10), DATEADD(dd, -1, GETDATE()), 103), 103)
If I add an ORDER BY 1 clause to order by the date column, the OwnerAgent values all become NULL!
If I change the outer join to an inner join, the query works but I lose the NULL entries.
If I change the select statement to add ISNULL around LIA.OwnerAgent, it works fine, but I don't know why.
I'm using SQLServer 2000 SP4.
Any help appreciated,
Thanks.
Bryan.
May 7, 2009 at 5:51 am
It will be easier to show you what is happening, if you can provide the CREATE TABLE statements for each of the tables you reference.
The data for the tables isn't required, though any index definitions may be handy.
Cheers
Paul
May 7, 2009 at 7:08 am
Hi, the table definitions are:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LeadAllocation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LeadAllocation]
GO
CREATE TABLE [dbo].[LeadAllocation] (
[Date] [datetime] NULL ,
[ClientId] [int] NULL ,
[ContactId] [int] NULL ,
[AgreementId] [int] NULL ,
[Sent] [tinyint] NULL ,
[AgentId] [varchar] (20) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LifeInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LifeInfo]
GO
CREATE TABLE [dbo].[LifeInfo] (
[ContactId] [int] NOT NULL ,
[LifeYN] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[Provider] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[SingleOrJoint] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[MonthlyPayment] [money] NULL ,
[DOB2ndApp] [datetime] NULL ,
[Smoker12] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[LastReviewDate] [datetime] NULL ,
[CoverAmount] [money] NULL ,
[CoverEnoughYN] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[CoverType] [int] NULL ,
[CoverPeriod] [int] NULL ,
[CoverLevel] [int] NULL ,
[CoverPremium] [int] NULL ,
[QuoteYN] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[DateModified] [datetime] NULL ,
[OwnerAgent] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[Notes] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
[Eligibility] [char] (1) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LifeInfoAgentAudit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LifeInfoAgentAudit]
GO
CREATE TABLE [dbo].[LifeInfoAgentAudit] (
[ContactId] [int] NOT NULL ,
[Date] [datetime] NOT NULL ,
[OwnerAgent] [varchar] (20) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Agreement]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Agreement]
GO
CREATE TABLE [dbo].[Agreement] (
[AgreementId] [int] IDENTITY (36, 1) NOT NULL ,
[ClientId] [int] NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[AgreementType] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[DailyAllocation] [int] NULL ,
[TotalAllocation] [int] NULL ,
[AllocationType] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[Priority] [int] NULL ,
[LTVMin] [decimal](3, 2) NULL ,
[LTVMax] [decimal](3, 2) NULL ,
[CreditsMin] [int] NULL ,
[CreditsMax] [int] NULL ,
[CreditValueMin] [money] NULL ,
[CreditValueMax] [money] NULL ,
[AgeMin] [int] NULL ,
[AgeMax] [int] NULL ,
[EmploymentStatus] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[IVAYN] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[BankruptcyAllowed] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[EmailYN] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[PaymentType] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[InvoiceAmount] [money] NULL ,
[DeliveryType] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[DeliveryEmail] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[Authorised] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[AuthInitials] [char] (4) COLLATE Latin1_General_CI_AS NULL ,
[Active] [char] (1) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Agent]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Agent]
GO
CREATE TABLE [dbo].[Agent] (
[AgentId] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[Forename] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[Surname] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[DomainUserId] [char] (15) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
Basically the script is looking to find the previous editor of a record who is not the same as the person who has created the allocation record in LeadAllocation.
Unfortunately, I've lost the tab formatting so the script looks much uglier than it shoud, sorry.
May 7, 2009 at 2:45 pm
Hi
"ORDER BY 1" means to order by "LA.Date" in your statement. You use a LEFT JOIN to your "LifeInfoAgentAudit" table where the "OwnerAgent" is stored. If your first dates don't have a related "LifeInfoAgentAudit" you will not get a value for them. That specifies an OUTER JOIN 😉
Greets
Flo
May 7, 2009 at 2:56 pm
So this proves you actually have NULL s in the designated date column !
Since you didn't order the first time, these rows will have been some where random in the result set.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 7, 2009 at 3:04 pm
Somewhat unrelated, I would also suggest moving away from ORDER BY 1 and move to using ORDER BY column_name.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply