I've never seen the 'id' = opportunity.id syntax before. Of course, I
don't use sql server. Do you get the same if you do select
opportunty.id AS id?
On 8/26/05, Protoculture <[EMAIL PROTECTED]> wrote:
> I am trying to get a total count of all our records. The query itself is a
> join ( as you can see below )... however when I make a change between the
> following I get a different result set... I want to be able to return the
> correct number of records but do it the correct way and not return a list of
> ids ( then do a #queryname.recordCount# ), rather I just want a proper count
> returned as I am trying to do in query2 below.
>
> The two queries are identical except for the top portion, directly below.
>
> <query 1 snippet> // this returns a listing 3500 records
>
> SELECT DISTINCT 'id' = opportunity.id
>
> </query 1 snippet>
>
> <query 2 snippet> // this returns a count of 135,000+ records
>
> SELECT DISTINCT count(opportunity.id) as total_records
>
> </query 2 snippet>
>
> /* also tried
>
> count(*),
> count(id = opportunity.id),
> count('id' = opportunity.id)
>
> */
>
> FULL SQL CODE...
>
> <query 1>
> SELECT DISTINCT
>
> 'id' = opportunity.id
>
> FROM opportunity,
> advertiser,
> location
>
> WHERE opportunity.deleted
> = 'N'
> AND
> opportunity.authorised
> = 'Y'
> AND
> opportunity.active
> = 'Y'
> AND
> opportunity.activate
> <= getdate()
> AND
> opportunity.deactivate
> >= getdate()
> AND
> opportunity.advertiser_id
> = advertiser.id
> AND
> advertiser.deleted
> = 'N'
> AND
> advertiser.suspended
> = 'N'
> AND
>
> (
> (0 = 0 OR levels LIKE '%' + ',' +
> CONVERT(VARCHAR,0) + ',' + '%') OR
> (0 = 0 OR levels LIKE '%' + ',' +
> CONVERT(VARCHAR,0) + ',' + '%')
> ) AND
>
> (0 = 0 OR sectors LIKE '%' + ',' +
> CONVERT(VARCHAR,0) + ',' + '%') AND
> (0 = 0 OR work_types LIKE '%' + ',' +
> CONVERT(VARCHAR,0) + ',' + '%')
>
> AND
>
> (
> 0 = 0 OR
> (0 LIKE '%' + ',' + CONVERT(VARCHAR,0) + ',' +
> '%' AND
> 0 LIKE '%' + ',' +
> CONVERT(VARCHAR,0) + ',' + '%')
> )
>
> </query 1>
>
> <query 2>
>
> SELECT DISTINCT
>
> count(opportunity.id) as total_records
>
> FROM opportunity,
> advertiser,
> location
>
> WHERE opportunity.deleted
> = 'N'
> AND
> opportunity.authorised
> = 'Y'
> AND
> opportunity.active
> = 'Y'
> AND
> opportunity.activate
> <= getdate()
> AND
> opportunity.deactivate
> >= getdate()
> AND
> opportunity.advertiser_id
> = advertiser.id
> AND
> advertiser.deleted
> = 'N'
> AND
> advertiser.suspended
> = 'N'
> AND
>
> (
> (0 = 0 OR levels LIKE '%' + ',' +
> CONVERT(VARCHAR,0) + ',' + '%') OR
> (0 = 0 OR levels LIKE '%' + ',' +
> CONVERT(VARCHAR,0) + ',' + '%')
> ) AND
>
> (0 = 0 OR sectors LIKE '%' + ',' +
> CONVERT(VARCHAR,0) + ',' + '%') AND
> (0 = 0 OR work_types LIKE '%' + ',' +
> CONVERT(VARCHAR,0) + ',' + '%')
>
> AND
>
> (
> 0 = 0 OR
> (0 LIKE '%' + ',' + CONVERT(VARCHAR,0) + ',' +
> '%' AND
> 0 LIKE '%' + ',' +
> CONVERT(VARCHAR,0) + ',' + '%')
> )
>
> </query2>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216483
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
