Advanced Query with Search Parameters

Advanced Query with Search Parameters

  
Hi everyone,


i'm trying to filter some columns. So, to do that i need some search parameters, but it becomes harder when i'm using an Advanced Query.

There's any way to define a search parameter in an advanced query?
I've got 3 parameters that are @ClientId, @ProjectId and @IsProject...

I'm passing to @ProjectId something like: If(ProjectId <> NullIdentifier() ,"({Project}.[Pro_ProjectId]= " + ProjectId + ") ", "")

This way it was supposed with Expand Inline set to True that when @ProjectId is an empty string it were ignored in sql query.

But i'm allways getting an error like "incorrect sintax near !"...

Any Idea of what can be wrong?

THE QUERY IS:

SELECT {Client}.*, {Project}.*, {ProState}.* 
FROM (({Client} Inner JOIN {Project} ON ({Project}.[Pro_ClientId] = {Client}.[Cli_ClientId]))  Inner JOIN {ProState} ON ({Project}.[Pro_StateId] = {ProState}.[Pst_ProStateId]))
WHERE ((case when @ClientId != '' then @ClientId + ' and '  else '' end))  {Client}.[Cli_ClientId] is not null AND ((case when @ProjectId != '' then @ProjectId + ' and ' else ''  end))  {Project}.[Pro_ProjectId] is not null AND ((case when {Project}.[Pro_IsProject]=0 then 'False' else 'True' end) in @IsProject)


Thanks in advance,
Pedro Gonçalves
Pedro,

First of all...why are you using an advanced query? Do you really need?

First and a half, if you really need the advanced query, Do you really need to select all attributes from that 3 entities? Are you gonna use all these attributes? If not please create a structure with the attributes you are gonna need and make it as the output structure of that query.

Second... Search Parameter are only available for simple queries. If you want to design a dynamic where clause you should use an input parameter with the expand inline parameter = true and assign the sql text you want to the variable before you send it to the parameter query. Please don't forget to encodesql() that parameter if the query its called directly from a webscreen.

Third...use <> instead of != (you can check the sql operators http://www.w3schools.com/sql/sql_where.asp )

cheers
RNA
Hi Ricardo,

"why are you using an advanced query? Do you really need?"

I'm using advanced query because i've got a boolean field in Project Entity and i need to filter that field from 3 possible values that are: "True" or "False" or both "True and False". So, to do that i'm using "in (@Parameter)". I think that with a Query i can't do that. Am i wrong? There's any other way to do that?

Do you really need to select all attributes from that 3 entities? Are you gonna use all these attributes?

I'll use almost all attributes, so i didn't create a structure but i may do that. Thanks.

 If you want to design a dynamic where clause you should use an input parameter with the expand inline parameter = true and assign the sql text you want to the variable before you send it to the parameter query. Please don't forget to encodesql() that parameter if the query its called directly from a webscreen

I'm already using an input parameter with the expand inline set to true. But i forgot to use encodesql(...) function. I'll use it!


Cheers,
Pedro
regarding your post, and responding in what it's call "Obama way"...yes you can! :)

You should have a combobox having a special list with 3 options [All, True, False;0,1,2] and have a parameter var(text) in your simple query and your where clause condition should be @Var = '0' or (@Var = '1' And {PROJECT}.[Is_Open] = True) or (Var = '2' And {PROJECT}.[Is_Open] = False)

RNA