January 10, 2012 at 5:00 pm
Hi, can someone help me figure out the best syntax for this query? Much appreciated -
SELECT sa.accountno AS accountno, s.saledate AS SaleDate, s.saleprice AS saleprice,
s.improvedflag AS improvedflag, s.grantor AS grantor, s.grantee AS grantee,
s.receptionno AS recpno, d.deeddescription AS deed,
CASE
when s.saleprice = '0' AND s.improvedflag = '0' then s.improvedflag = 'N/A'
when s.saleprice > '0' AND s.improvedflag = '0' then s.improvedflag = 'Vacant'
when s.saleprice > '0' AND s.improvedflag = '1' then s.improvedflag = 'Improved'
else NULL
end
from tblsale s, tblsaleacct sa, tlkpdeedtype d
where sa.receptionno = s.receptionno
and s.deedcode = d.deedcode
and s.verend = '99999999999'
and sa.verend = '99999999999'
order by sa.accountno, s.saledate;
January 10, 2012 at 8:50 pm
First, please write your code using the ANSI-92 style joins, not the ANSI-89 style joins. Putting your join criteria in the FROM clause and your filter criteria in the WHERE clause makes reading your code easier.
Here is what I think you are attempting to accomplish. If not, you need to give us more detail as to what you are trying to accomplish:
SELECT
sa.accountno AS accountno,
s.saledate AS SaleDate,
s.saleprice AS saleprice,
s.improvedflag AS improvedflag,
s.grantor AS grantor,
s.grantee AS grantee,
s.receptionno AS recpno,
d.deeddescription AS deed,
CASE
when s.saleprice = '0' AND s.improvedflag = '0' then 'N/A'
when s.saleprice > '0' AND s.improvedflag = '0' then 'Vacant'
when s.saleprice > '0' AND s.improvedflag = '1' then 'Improved'
else NULL
end ImprovedFlagDescription
from
tblsale s
inner join tblsaleacct sa
on (sa.receptionno = s.receptionno)
inner join tlkpdeedtype d
on (sa.receptionno = s.receptionno)
where
s.verend = '99999999999'
and sa.verend = '99999999999'
order by
sa.accountno,
s.saledate;
January 11, 2012 at 10:38 am
Yes, this is perfect, thank you. The second inner join was on deedcode, but with that change it works exactly the way I need it to.
Thanks so much!
January 11, 2012 at 12:14 pm
I have another join question for you SQL pros. The query below produces the accurate result set, but I'm guessing the syntax isn't up to current standards. Should the first where clause (where p.personcode = o.personcode) be an inner join? Or a nested query? And if so, how would you write it? Thanks so much for your help -
tbladdress contains addresscode, addresses
tblperson contains personcode, names
tblaccount contains account #, other stuff needed
tblacctowneraddress contains personcode, addresscode and account #
select distinct a.accountno, a.parcelno, p.privateflag, p.name1, p.name2, a.businessname,
ad.address1, ad.address2, ad.city, ad.statecode, ad.zipcode, ad.province, ad.country, ad.postalcode
from tbladdress ad
inner join tblperson p
on (p.personcode = ad.personcode)
inner join tblacctowneraddress o
on (o.addresscode = ad.addresscode)
inner join tblacct a
on (a.accountno = o.accountno)
where p.personcode = o.personcode
and a.acctstatuscode = 'A'
and a.accountno not like 'P%'
and p.privateflag != '1'
and o.primaryownerflag = '1'
and a.verend = '99999999999'
and p.verend = '99999999999'
and o.verend = '99999999999'
and ad.verend = '99999999999'
order by a.accountno;
January 11, 2012 at 1:47 pm
Because you're only using inner joins, just let the sql engine determine at which step in the process it will handle your where clause.
(It will take indexes and statistics into account for that to determine its sqlplan )
select distinct
a.accountno
, a.parcelno
, p.privateflag
, p.name1
, p.name2
, a.businessname
, ad.address1
, ad.address2
, ad.city
, ad.statecode
, ad.zipcode
, ad.province
, ad.country
, ad.postalcode
from tbladdress ad
inner join tblperson p
on p.personcode = ad.personcode
inner join tblacctowneraddress o
on o.addresscode = ad.addresscode
and o.personcode = p.personcode
inner join tblacct a
on a.accountno = o.accountno
where a.acctstatuscode = 'A'
and a.accountno not like 'P%'
and p.privateflag != '1'
and o.primaryownerflag = '1'
and a.verend = '99999999999'
and p.verend = '99999999999'
and o.verend = '99999999999'
and ad.verend = '99999999999'
order by a.accountno ;
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
January 11, 2012 at 2:22 pm
Thank you so much, Johan. I really appreciate your help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply