Help with Advanced Query

Help with Advanced Query

  
Hi there.

I need some help using an advanced query.

First of all, i'm using an Advanced Query because i'm using the SELECT DISTINCT syntax. Furthermore, i need to use search parameters as input to the advanced query. How can i do that?

Thanks

Hi,

You can include them on the simple query conditions with something like:

table.field = @parameter or parameter = 0
or
table.field = @parameter or parameter = ""

depends if the field is integer or text data type.

and then test the simple query with input parameters, get the SQL code and use it on the adv query.

ps: after changing the simple query conditions, try remove the search parameter and see if works.

hope it helps.

best regards,
Luis Soares

or you can fiddle with the "expand inline" on parameter, so you build up your query like you want it.
Hi Mates,

Thanks a lot for your help but i figured out a way to do it without an advanced query.

BTW, why not add the DISTINCT syntax to Simple Queries in a future release? Isn't possible?

Cheers
Pedro Domingues

Hi Pedro,

Yes it would be great to have it, you can help the cause by "liking" it in wisdom of the crowds:
http://www.outsystems.com/wisdomofthecrowds/IdeaComment_List.aspx?IdeaId=260

Cheers,
Hermínio Mira
Done =)

Thanks
Hi Luis,

I tried using table.field = @parameter or parameter = "" for a text search field but was unable to do so... The query I used was:

SELECT distinct {User}.* from {User} left join {User_Role} on {User}.[Id]={User_Role}.[User_Id]
where  ({User}.[Username] = @PrmUserName or {User}.[Username]= '')
Coould you please help me out.


Luis Paulo Soares wrote:

Hi, 

You can include them on the simple query conditions with something like:

table.field = @parameter or parameter = 0
or
table.field = @parameter or parameter = ""

depends if the field is integer or text data type.

and then test the simple query with input parameters, get the SQL code and use it on the adv query.

ps: after changing the simple query conditions, try remove the search parameter and see if works.

hope it helps.

best regards,
Luis Soares

 
 
The WHERE clause is wrong.  It should be:

WHERE ({User}.[Username] = @PrmUserName or @PrmUserName = '')

(Note that the '' at the end is two single quotes representing an empty string)
Hi
I used the following query as suggested: 
 SELECT distinct {User}.* from {User} left join {User_Role} on {User}.[Id]={User_Role}.[User_Id] 
where ({User}.[Username]=@PrmUserName or @PrmUserName ='')
But the query doesn't give any output when search parameter is null/blank.


Hi Rajendra,

Are you using SQL server or Oracle?
Because in Oracle the '' is Null, so the platform uses ' ' (a space) to replace the empty string.
So either do:
SELECT distinct {User}.* from {User} left join {User_Role} on {User}.[Id]={User_Role}.[User_Id] 
where ({User}.[Username]=@PrmUserName or @PrmUserName =' ')
Or it do it implementation independent by passing a parameter that is always empty:
SELECT distinct {User}.* from {User} left join {User_Role} on {User}.[Id]={User_Role}.[User_Id] 
where ({User}.[Username]=@PrmUserName or @PrmUserName = @EmptyString)

Regards,
João Rosado