SQL arguement in a Where statement

[roberto]

In a Grid Component using an AlphaDAO, I am having problems using an arguement in the filter for the Query SQL Select.

Example:
SELECT FACILITY.NAME, FACILITY.USERTYPES FROM FACILITY FACILITY WHERE USERTYPES LIKE '%:argType%'

argType is defined as a character populated by the variable session.sType
:argType = "1"

The above statement does not work for me. When I hardcode the "1" ( USERTYPES LIKE '%1%' ) the Grid Select works.

Using an override setting on the A5w page works successfully when I concat the session variable in place of the arguement:

CS.SQL = "SELECT FACILITY.NAME, FACILITY.USERTYPES FROM FACILITY WHERE USERTYPES LIKE '%"+alltrim(session.sType)+"%'"

Additional info,
I've also tried to trim argType without success:
SELECT FACILITY.NAME, FACILITY.USERTYPES FROM FACILITY FACILITY WHERE USERTYPES LIKE '%alltrim(:argType)%'

[NoeticCC]

Re: SQL arguement in a Where statement

Why are you hand-coding the SQL in a grid?

Or in which context are you using a filter? (See screenshots)

[roberto]

Re: SQL arguement in a Where statement

I tried using the component prompts for Table/View, but it did not work (img1.png).

Then I tried the SQL Select Statment prompts (img2.png). Again it did not work

The argument is declared with the Define Arguments builder (img3.png)

Did this answer your question? Or did I miss the mark?

[NoeticCC]

Re: SQL arguement in a Where statement

I tried using the component prompts for Table/View, but it did not work (img1.png).

Then I tried the SQL Select Statment prompts (img2.png). Again it did not work

The argument is declared with the Define Arguments builder (img3.png)

Did this answer your question? Or did I miss the mark?

I'd think it was simply

USERTYPES = :argType

[roberto]

Re: SQL arguement in a Where statement

Unfortunately a straight 'equal to' can not be used.

USERTYPES is actually a comma-delimited string of 3-digit numbers. USERTYPES could have values like "002,345,876" or "993,001,656,344,002" or etc

Therefore I tried using the LIKE statement. The LIKE works as an override in the A5W page. Unfortunately, I am not able to get it to work in the standard component prompts.

[NoeticCC]

Re: SQL arguement in a Where statement

Unfortunately a straight 'equal to' can not be used.

USERTYPES is actually a comma-delimited string of 3-digit numbers. USERTYPES could have values like "002,345,876" or "993,001,656,344,002" or etc

Therefore I tried using the LIKE statement. The LIKE works as an override in the A5W page. Unfortunately, I am not able to get it to work in the standard component prompts.

Aah... Hm, I will have a play but I would have tried something like:

".. WHERE :arg IN (USERTYPES)" but not 100% sure it works that way around.

[NoeticCC]

Re: SQL arguement in a Where statement

In mySQL this works:

SELECT ID, UserTypes
FROM in_test
WHERE UserTypes LIKE concatenate('%', :arg1, '%')

[roberto]

Re: SQL arguement in a Where statement

That works beautifully with SQL Express 2005 also.

Thank You!

[NoeticCC]

Re: SQL arguement in a Where statement

That works beautifully with SQL Express 2005 also.

Thank You!

Good to know, as the concatenate syntax was new to me before I worked with mySQL