What is the purpose of sql queries in an expression and what would be the correct syntax to implement as an expression?

Here's a sracastic example from a email signature I saw:
$ select * from users where clue > 0
no rows returned


This statement says from the table users,  give me all users where clue is greater than zero  (a user who has a clue).

If there were three users whose clue column contained 1, 0, 2 it would return 2 rows.

In this case all users had zero clues in their column, so since no entry contained a value greater than zero, no rows were returned.

I hope you found this instructive as well as amusing.
Scott

Now the purpose of an sql query is to extract data fitting certain criteria from a database table, whether it is MS Access, SQL server, Oracle, etc, etc.    In the example above the criteria was clue >0.  

Select * says give me the entire row if the clue column contains greater than zero.

This is the first half of the question:

What is the purpose of sql queries in an expression

in an SSRS 2008 (see tag) expression, is the key
SSRS = Sql Server Reporing services
Expression =  tool built into this product.

Thanks for the quick response but I am looking for different information than this.

rhservan,

Are you talking about Report Builder?  

http://www.sqlmusings.com/2010/04/01/exploring-ssrs-2008-r2-report-builder-3-0-%E2%80%93-report-parts/

To put simply you would have a SQL query that extracts the data from a table (or tables) into a dataset, and then a report layout that would graphically display the results.  If this isn't the info you are looking for, can you provide us a little more direction of what you are trying to do?

No one has answered this question correctly.  I have provided all the information necessary
Tag: SSRS 2008

Here is the question again:

What is the purpose of SQL QUERIES IN AN  "EXPRESSION" and what would be the correct syntax to implement as an expression?

If you don't know what expressions are in the SSRS designer you wont know this answer.

Ok first of all, I am well versed in what an expressions are, but it's very difficult to get your requirements from what you asked originally.

An example of how to use expressions in a sql query.

Let's say we have a parameter that allows the user to enter in the number of records returned... Call it TopValue for sake of this example.

You would then do something like the following in your SQL query.

=IIF(Parameters!TopValue >0, "Select top" + Cstr(Parameters!TopValue.Value + " name from users"

Thx sgdought! I was just trying to come up with a way around a report design issue I'm having and that will help me a lot...  

@Auric1983 - Thanks for your efforts - Just trying to communicate the best I can through text.  To me this is a bit difficult. But  lets give it another try.

Below is a sample of an sql queriy in an expression in SSRS 2008 which I am working on. I did not create this but it is in a report.  I have never seen a select statement used this way.  Is a select statement in my SSRS 2008 Expression box possible?  

="select distinct DRL_ID from l_carp.p_goods_receipts, l_carp.p_gr_cer_nums, l_carp.p_b2mml_logs, DIMS.SOX, DIMS.BUSHELS
where DRL_ID = crg_ref_DRL_ID
and crg_cat_num = SOX.rod_batch and brsn_ref_crg_id = crg_id and brsn_cer_num = SOX.SOXer
and SOX.container_id = BUSHELS.container_id
and BUSHELS.bun_cer = '" + Parameters!container.Value.ToString + "'
and bml_processed_flag in ('Z','G','M')"

Correction
The line:

and bushels.bun_cer =@containerValue (This converted after I pasted in the post)

Actual
bushels.bun_cer= ' " + Parameters!container.value.tostring + " '

Actually No I made that change in the version in my post.

Looks to me like the original report author didn't understand how to use Parameters in a query in SSRS, and instead used an expression to build the SQL statement.

Check the datasets in that report to see if there is one calling that expression textbox.

The above expression is run in a dataset.

There is another expression in a text box which provides a count of DRL_ID after "test text"
="test text (" & IIF(count(Fields!DRL_ID.value) is nothing, 0,count(Fields!DRL_ID)) &")"

What about on the "DATASETS" tab of the report? there must be a dataset pointing to that expression somewhere.

1. This runs as an expression in DataSet2
="select distinct DRL_ID from l_carp.p_goods_receipts, l_carp.p_gr_cer_nums, l_carp.p_b2mml_logs, DIMS.SOX, DIMS.BUSHELS
where DRL_ID = crg_ref_DRL_ID
and crg_cat_num = SOX.rod_batch and brsn_ref_crg_id = crg_id and brsn_cer_num = SOX.SOXer
and SOX.container_id = BUSHELS.container_id
and BUSHELS.bun_cer = '" + Parameters!container.Value.ToString + "'
and bml_processed_flag in ('Z','G','M')"

2. This runs as an expression in TextBox 1.
="test text (" & IIF(count(Fields!DRL_ID.value) is nothing, 0,count(Fields!DRL_ID)) &")"

The expression in Text box 1 actually points to the dataset2,  sorry I left that out of the expression on #2

When would it be advantageous to run a query, like the one above,  as an expression?

If you were dynamically creating the SQL query and you wanted to be able to specify fields in a where clause for example.

Thank you for your contributions, the information you provided assisted with resolving my problems.