> Can be better written as
>
> UNIT.HUNITTYPE BETWEEN 2 AND 51
Paul,
This may not be good advice. CLVTECH states the numbers that
he/she wants
including in the WHERE clause. These numbers are not a
consistent from - to
list. Your BETWEEN would include every number from 2 to 51
which may not be
correct.
Instead, use IN. ie:
AND UNIT.HUNITTYPE IN ( '2','3','4','5', etc. etc)
This allows you to explicitly state the numbers you want to
be included, but
is far easier and quicker to code.
Pat.
"Paul Whitham AdobeCommunityExpert"
<design@valleybiz.net> wrote in message
news:fainpv$fcb$1@forums.macromedia.com...
>I can't answer your specific issue but I would recommend
that you use the
>query created in SQL as the source of the page recordset
rather than
>creating a new SQL statement on the page. It should be
faster thay way.
>
> Also the section of your SQL where you go
>
> UNIT.HUNITTYPE = '2' OR
>> UNIT.HUNITTYPE = '3' OR
>> UNIT.HUNITTYPE = '4' OR
>> UNIT.HUNITTYPE = '5' OR
>> UNIT.HUNITTYPE = '42' OR
>> UNIT.HUNITTYPE = '44' OR
>> UNIT.HUNITTYPE = '45' OR
>> UNIT.HUNITTYPE = '46' OR
>> UNIT.HUNITTYPE = '47' OR
>> UNIT.HUNITTYPE = '48' OR
>> UNIT.HUNITTYPE = '49' OR
>> UNIT.HUNITTYPE = '50' OR
>> UNIT.HUNITTYPE = '51')
>
> Can be better written as
>
> UNIT.HUNITTYPE BETWEEN 2 AND 51
>
> --
> Paul Whitham
> Certified Dreamweaver MX2004 Professional
> Adobe Community Expert - Dreamweaver
>
> Valleybiz Internet Design
> www.valleybiz.net
>
> "CLVTECH" <webforumsuser@macromedia.com> wrote in
message
> news:faie7b$5qk$1@forums.macromedia.com...
>>I run my query in MSSQL 2005. I am returning values
in 10 columns. When I
>>run
>> the same query in Dreamweaver, I get 9 out of 10
rows correct. The 10th
>> is
>> different.
>>
>> Below is my query. I checked 11 times before asking
this question. The
>> query's
>> match in my .cfm doc and my sql query written in
2005. I am relatively
>> new to
>> dreamweaver, les than a year exp. Could someone
explain my results?
>>
>> SELECT DISTINCT
>> DATEDIFF(DAY, UNIT.dtAvailable, GETDATE()) AS
>> DAYS_DIFF,
>> MAX(TENANT.SRENT) AS old_rent, UNIT.dtAvailable,
UNIT.SRENT AS NEW_Rent,
>> PROPERTY.SADDR1, UNIT.PUCODE, UNIT.sStatus,
>> UNIT.HUNITTYPE, UNIT.SCODE, PROPERTY.dtInactive,
UNITTYPE.SDESC,
>> UNIT.SFIELD1
>> FROM TENANT INNER JOIN
>> UNIT ON TENANT.HUNIT = UNIT.HMY INNER JOIN
>> PROPERTY ON TENANT.HPROPERTY = PROPERTY.HMY AND
>> UNIT.HPROPERTY = PROPERTY.HMY INNER JOIN
>> UNITTYPE ON UNIT.HUNITTYPE = UNITTYPE.HMY
>> WHERE (UNIT.dtAvailable IS NOT NULL) AND
(UNIT.HUNITTYPE = '2' OR
>> UNIT.HUNITTYPE = '3' OR
>> UNIT.HUNITTYPE = '4' OR
>> UNIT.HUNITTYPE = '5' OR
>> UNIT.HUNITTYPE = '6' OR
>> UNIT.HUNITTYPE = '7' OR
>> UNIT.HUNITTYPE = '8' OR
>> UNIT.HUNITTYPE = '25' OR
>> UNIT.HUNITTYPE = '26' OR
>> UNIT.HUNITTYPE = '27' OR
>> UNIT.HUNITTYPE = '28' OR
>> UNIT.HUNITTYPE = '29' OR
>> UNIT.HUNITTYPE = '30' OR
>> UNIT.HUNITTYPE = '31' OR
>> UNIT.HUNITTYPE = '32' OR
>> UNIT.HUNITTYPE = '33' OR
>> UNIT.HUNITTYPE = '34' OR
>> UNIT.HUNITTYPE = '35' OR
>> UNIT.HUNITTYPE = '36' OR
>> UNIT.HUNITTYPE = '37' OR
>> UNIT.HUNITTYPE = '39' OR
>> UNIT.HUNITTYPE = '41' OR
>> UNIT.HUNITTYPE = '42' OR
>> UNIT.HUNITTYPE = '44' OR
>> UNIT.HUNITTYPE = '45' OR
>> UNIT.HUNITTYPE = '46' OR
>> UNIT.HUNITTYPE = '47' OR
>> UNIT.HUNITTYPE = '48' OR
>> UNIT.HUNITTYPE = '49' OR
>> UNIT.HUNITTYPE = '50' OR
>> UNIT.HUNITTYPE = '51') AND (UNIT.sStatus IS NOT
>> NULL)
>> AND (UNIT.SCODE <> 'OUTPARK') AND (UNIT.SCODE
<> 'OUTPARK1') AND
>> (UNIT.SCODE <> 'OUTPARK2') AND (UNIT.SCODE
<>
>> 'OUTPARK3') AND (UNIT.SCODE <> 'OUTPARK4') AND
(UNIT.SCODE <> 'OUTPARK5')
>> AND
>> (PROPERTY.dtInactive IS NULL)
>> GROUP BY TENANT.HPROPERTY, UNIT.SCODE,
UNIT.dtAvailable, UNIT.sStatus,
>> UNIT.SRENT, PROPERTY.SADDR1, UNIT.SDEPOSIT1,
UNIT.PUCODE,
>> UNIT.HUNITTYPE, UNIT.SCODE, PROPERTY.dtInactive,
>> UNITTYPE.SDESC, UNIT.SFIELD1
>> ORDER BY PROPERTY.SADDR1
>>
>>
>
>
3
Replies
AdChoices