Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

need help with CASE statement and table joins Expand / Collapse
Author
Message
Posted Tuesday, January 10, 2012 5:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 2:21 PM
Points: 7, Visits: 10
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;
Post #1233640
Posted Tuesday, January 10, 2012 8:50 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 23,089, Visits: 31,635
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;




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1233696
Posted Wednesday, January 11, 2012 10:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 2:21 PM
Points: 7, Visits: 10
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!
Post #1234190
Posted Wednesday, January 11, 2012 12:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 2:21 PM
Points: 7, Visits: 10
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;
Post #1234280
Posted Wednesday, January 11, 2012 1:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #1234349
Posted Wednesday, January 11, 2012 2:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 2:21 PM
Points: 7, Visits: 10
Thank you so much, Johan. I really appreciate your help.
Post #1234387
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse