Query Problem

I am using the query shown below but I am not getting the result I expect. I
want to get "Elected" only when the count of LastName field is 75% or more
of the count of when the "CG" field is equal to "Y". What did I mess up
here?
============================ =iif(Count(Fields!LastName.Value) < Count(( Fields!CG.Value)="Y")*0.75, "Not
Elected", "Elected")
============================================Hi Wayne
Count(( Fields!CG.Value)="Y"), i think you can't do this,the
architecture of the RS is such a way that it calculates all the
aggregations and operations first and then will come to the
conditional Expressions next,
here it is messed up with Expressions first .where you are unable to
get the result
As an alternate you can do this
1) Create an extra column Extra1 by going to dataset-->Fields
it must be a calculated field (not dataset field), give like this
IIF( Fields!CG.Value="Y",1,0)
2)Add another column extra2 with sum(Extra1) .
2)Go to the layout ,add extra column to the right of the column
wherever you want,give the expression like this
=iif(Count(Fields!LastName.Value) <First(Extra2), "Not
> Elected", "Elected")
i am not that much confident that it will work, but readjusting all
this expressions should work
Regards,
Raj Deep.A
Wayne Wengert wrote:
> I am using the query shown below but I am not getting the result I expect. I
> want to get "Elected" only when the count of LastName field is 75% or more
> of the count of when the "CG" field is equal to "Y". What did I mess up
> here?
> ============================> =iif(Count(Fields!LastName.Value) < Count(( Fields!CG.Value)="Y")*0.75, "Not
> Elected", "Elected")
> ============================================|||Thanks for the suggestions. I'll give that a try.
Wayne
"RajDeep" <rajalapati@.gmail.com> wrote in message
news:1160558814.067206.178500@.h48g2000cwc.googlegroups.com...
> Hi Wayne
> Count(( Fields!CG.Value)="Y"), i think you can't do this,the
> architecture of the RS is such a way that it calculates all the
> aggregations and operations first and then will come to the
> conditional Expressions next,
> here it is messed up with Expressions first .where you are unable to
> get the result
> As an alternate you can do this
> 1) Create an extra column Extra1 by going to dataset-->Fields
> it must be a calculated field (not dataset field), give like this
> IIF( Fields!CG.Value="Y",1,0)
> 2)Add another column extra2 with sum(Extra1) .
> 2)Go to the layout ,add extra column to the right of the column
> wherever you want,give the expression like this
> =iif(Count(Fields!LastName.Value) <First(Extra2), "Not
>> Elected", "Elected")
> i am not that much confident that it will work, but readjusting all
> this expressions should work
> Regards,
> Raj Deep.A
>
>
> Wayne Wengert wrote:
>> I am using the query shown below but I am not getting the result I
>> expect. I
>> want to get "Elected" only when the count of LastName field is 75% or
>> more
>> of the count of when the "CG" field is equal to "Y". What did I mess up
>> here?
>> ============================>> =iif(Count(Fields!LastName.Value) < Count(( Fields!CG.Value)="Y")*0.75,
>> "Not
>> Elected", "Elected")
>> ============================================>sql

Query Optimization

Question regarding performance.

If I have a query such as:

Select UserId, Firstname,lastname from members where country='can'

If I were to call this from an .net executable as straight SQL to the Database vs. encapsulating the command in a stored procedure and calling the procedure.

What would be the performance differences? Would their be any issues (outside of security) that would make me choose to place the call in a Procedure?

Thanks

It probably doesn't matter in this case. Executing the SELECT statement using a parameterized command object will provide same benefit as calling SP with slighly more overhead. RPC execution for SP calls provide better performance than sending the entire SQL text. The answer is that it depends on your needs. Changing SQL statements embedded in applications is often harder than modifyin a SP. You can also take a look at the whitepaper below for more information:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

query not returning data!

Hi! I have a sql query in stored procedure:
SELECT Salutation + ' ' + FirstName + ' ' + LastName AS fullname

Ok, this returns a value if salutation is not null, but if the salutation is null it doesn't return any value, I was thinking if the saluation is null then I would atleast get the firstname and last name. Any help appreciated on this.

You can us ISNULL or COALESCE to take care the NULL value in your Salutation column before you combine it with firstname and lastname. For example:

SELECT ISNULL(Salutation,'') + ' ' + FirstName + ' ' + LastName AS fullname

Or

SELECT COALESCE(Salutation,'') + ' ' + FirstName + ' ' + LastName AS fullname

|||

To be precise, the query is returning a value. The value is NULL, which in SQL terms is unknown.

So, when you add (concatenate) a known value to an unknown one, what should the result be?

Unknown!

It's analogous to saying, I'm going to add $5 to the money in my pocket. How much money is in my pocket?

You don't know, because you don't know how much money is already in my pocket.

(You don't even know if there is $5 in my pocket - there might be a hole in it. )

You have to be very careful to account for NULL values in the database.

Nothing is less than, greater than, or equal to NULL. Even NULL!

|||

Thank you! It works great now. What does COALESCE mean?

Cheers!

|||

Thank you for the explanation! Make sense.