How to avoid repeating the same comparison in a query

[Altostratus]

Hi,

The following works just fine, but is there any way to write this more concisely or to make it faster, e.g. by only making the comparison once (or, even better, to achieve the same result less hackily)?

TABLE WITHOUT ID
    choice(file.size > 50, "<span class=\"more-text\">", "") +
    file.link +
    choice(file.size > 50, "</span>", "")
        AS "Column Name"

Thanks.

[holroy]

Here are some options:

Alt 1:
TABLE WITHOUT ID
  choice(moreText, "<span class='more-text'>", "") + 
  file.link + 
  choice(moreText, "</span>", ""
FLATTEN file.size > 50 as moreText

...
Alt 2:
TABLE WITHOUT ID
  choice( file.size > 50,
    "<span class='more-text'>" + file.link + "</span>", 
    file.link)

...
Alt 3:
TABLE WITHOUT ID
  "<span class='" + 
  choice(file.size > 50, "more-text", "") + "'>" + 
  file.link + "</span>"

...

Alt 4:
TABLE WITHOUT ID
  "<span class ='" + myClass + "'>" + file.link. + "</span>"
FLATTEN choice(file.size > 50, "more-text", "") as myClass

I kind of like the last one the most, as it clearly separate the logic from the visual presentation. Alternative 3 and 4, do however add the <span> element around the link in either case, but it shouldn’t do anything to add the element without a class. Alternative 2 is kind of clear and concise, and although alternative 1 does work, I kind of don’t like the double choice() in there.

So have a pick, and use one you like. Note the general trick of using FLATTEN ... as something to calculate a variable to be re-used in other parts of your query, like in the column definitions, or WHERE-clauses, or GROUP BY-clauses, and so on.

Some caveats when using FLATTEN is that it needs to be placed after any FROM clauses if you’ve got that in your query, and if you want to use stuff generated by a GROUP BY clause, it needs to be after that, as well.

[Altostratus]

Thanks a lot. I didn’t know FLATTEN could be used like that. Alternative 2 seems perfect to me, as I don’t worry that much about separating logic from presentation in such a simple query. I just needed to make it as fast as possible. Cheers.