Statement not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Statement not working

Author

Topic

satyen
Starting Member

21 Posts

Posted - 2012-07-27 : 11:56:45

set dateformat dmy
select
a.casecode as casecode,
a.name,
a.appdate as Appointment_Date,
a.start as Petition_Date,
a.award as Order_Date,
a.taxref,
a.accyearend,
a.closed,
a.type,
a.loc,
a.nominee_1 as Lead_IP,
a.nominee_2 as Second_IP,
c.plainnote,
c.addedby,
c.adddatetime,
d.desc1,
d.ddate

from ips_case_nominee a left outer join ips_casestaff b on a.casecode = b.casecode
left outer join ips_notes c on a.casecode = c.casecode left outer join ips_diary d on d.casecode = a.casecode
and c.casecode = b.casecode
and c.systemgenerated is null

and c.entryno =
(select top 1 c.entryno
from ips_notes c
where c.casecode = a.casecode and
c.ntype = 'TAX NOTE'
order by c.entryno desc)

where LoginName like 'Tax - Manager%'
and d.desc1 like '%Tax Due%'

and d.ddate =

(select top 1 d.ddate
from ips_diary d
where d.casecode = a.casecode
order by d.ddate)

The only part which doesn't work is the following:

and d.ddate =

(select top 1 d.ddate
from ips_diary d
where d.casecode = a.casecode
order by d.ddate)

Can someone help me get this working?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-07-27 : 12:13:53

what do you get when you run


select top 1 d.ddate
from ips_diary d
where d.casecode = a.casecode
order by d.ddate

by itself and does it produce the result you want for your date filter?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

satyen
Starting Member

21 Posts

Posted - 2012-07-27 : 12:17:58

omitting the following

and d.ddate =

(select top 1 d.ddate
from ips_diary d
where d.casecode = a.casecode
order by d.ddate)

Gives 4 results. Now with the above statement I hope to obtain the top 1 result. But when I do run the the whole query including the above no results are found.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 12:20:05

you're doing left outer join with ips_diary d on d.casecode = a.casecode

so may be it didnt have any matches for casecode and hence ddate was all null values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satyen
Starting Member

21 Posts

Posted - 2012-07-27 : 12:24:29

There are definately values in the date column

satyen
Starting Member

21 Posts

Posted - 2012-07-27 : 12:25:24

What do you think?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-07-27 : 12:29:53

what is the value of a.casecode when you get the 4 results? then stuff that value in


select top 1 d.ddate
from ips_diary d
where d.casecode = [stuff value here]
order by d.ddate

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 12:35:20

can you use a different alias inside subquery and try

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-07-27 : 12:53:16

also ASC is the default sort. since you did not specify that could that be the issue

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-28 : 02:45:16

Does your subquery have a null? That would stop it working.
You also have the alias D used twice, once in the sub query and one outside. It's not necessarily wrong but confusing - see if that's right.
Also, I'd be tempted to do this as

inner join
(
select d.casecode , min( d.ddate )
from ips_diary d
) x on a.casecode=x.casecode

rather than a subquery. You will most likely find it quicker.

satyen
Starting Member

21 Posts

Posted - 2012-07-30 : 11:43:48

1. Using a different Alias does not work.
2. I have tried using ASC...no results come up
3. What do you mean if the subquery has a null?

Any suggestions

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 12:57:41

quote:
Originally posted by satyen

1. Using a different Alias does not work.
2. I have tried using ASC...no results come up
3. What do you mean if the subquery has a null?

Any suggestions

that means ddate field has null values for some of casecode values in table referred by alias a

first take a value of casecode in table a and see if you've ddate values existing in ips_diary for that

alternatively show us some matching data from both tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources