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...

asked 02 Feb '17, 14:05

Alex's gravatar image

Alex
1.1k274756
accept rate: 25%

edited 03 Feb '17, 07:16

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?

(03 Feb '17, 02:19) Volker Barth
Replies hidden

16.0.0 Build 2344

(03 Feb '17, 07:07) Alex

Any news about this issue?

(10 Feb '17, 07:47) Alex

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?)

permanent link

answered 13 Feb '17, 16:37

Tim%20McClements's gravatar image

Tim McClements
2.0k1830
accept rate: 35%

converted 17 Feb '17, 12:47

Mark%20Culp's gravatar image

Mark Culp
24.9k10139297

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

(14 Feb '17, 14:15) Alex
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×69
×13
×10
×5
×5

question asked: 02 Feb '17, 14:05

question was seen: 2,843 times

last updated: 14 Feb '17, 14:15