MSSQL Query results vs dreamweaver recordset

"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.

What's different about it? It might just appear different because of a
formatting problem that confuses the browser or something along those lines.
What do you expect and what do you get?

> 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
>>
>>
>
>

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
>
>