cancel
Showing results for 
Search instead for 
Did you mean: 

SELECT aliases not working properly inside other statements, referenced by a WHERE clause

Former Member
3,721

Hi everyone,

It's known that you can create aliases in SELECT commands using statements, like IF and CASE, including using referring them in WHERE clauses (which is not supported in SQLServer, for example). However I'm getting a really weird behavior when executing this command:

SELECT 
     IF 1 = 1 THEN 1 ELSE 0 ENDIF AS PORCARIA
    ,IF PORCARIA = 1 THEN 1 ELSE 0 ENDIF AS CACETA
WHERE
--PORCARIA = 0
CACETA = 1

If you execute it without the WHERE clause both aliases will be selected as 1, correctly. However, when trying to filter by the second parameter, Ultralite gets lost. It seems it doesn't recognize the first alias, reading it as 0 instead of its value 1.

Is this a bug or a limitation? Because we use this feature a lot, but this is the first time we use it in a second level (the WHERE clause uses an alias which is generated using a condition using another alias)

Ps: Sorry about the question name and description (the terms can be a little bit confusing), but I was not sure how I could explain it properly...

VolkerBarth
Contributor
0 Kudos

the terms can be a little bit confusing

"Named SELECT attributes" are commonly called "aliases". According to the Ultralite docs, your sample should work, as they state:

select-list
[...] Optionally, you can define an alias for each expression in the select-list. Using an alias allows you to reference the select-list expressions from elsewhere in the query, such as from within the WHERE and ORDER BY clauses.

What version/build are you using?

Former Member
0 Kudos

16.0.0 Build 2344

Former Member
0 Kudos

Any news about this issue?

Accepted Solutions (0)

Answers (1)

Answers (1)

Thank you for reporting this.

I have reproduced a problem. It appears that evaluation of the where clause is incorrect in some cases here, which leads to incorrect results.

(As a workaround, can you generate the condition directly in the where clause?)

Former Member
0 Kudos

Yes, that is what we are doing now. Thanks!