On Thu, Feb 24, 2022 at 3:47 PM Tim Tilberg <ttilb...@gmail.com> wrote:
> Today I was scripting out some DB maintenance tasks on SQL Server, and was
> leveraging a query similar to:
>
> SELECT DISTINCT create_ts
> FROM #{table}
> WHERE create_ts < DATEADD(MONTH, -2, GETDATE())
>
> I initially thought "Hey, what does this look like with virtual row
> blocks, avoiding interpolation?
>
> DB[:a_table].distinct.select(:create_ts)
> .where { create_ts < dateadd(month, -2, getdate()) }
>
> I found that dateadd() was being parsed as a sql function call, but that
> getdate() was being treated as an identifier.
>
> [17] pry(main)> DB[:a_table].distinct.select(:create_ts).where { create_ts
> < dateadd(month, -2, getdate()) }.sql
> => "SELECT DISTINCT [CREATE_TS] FROM [a_table] WHERE ([CREATE_TS] <
> dateadd([MONTH], -2, *[GETDATE]*))"
>
> In this case, I was able to just use Ruby's `Date.today` rather than SQL's
> GETDATE(). I'm trying to improve my understanding of virtual row blocks,
> and was curious if there was a quick fix for this, or if it needs to drop
> into `Sequel.lit` or `Sequel.function` syntax.
>
This isn't because it is nested (nested functions work fine), it's because
you didn't provide an argument. From a virtual row perspective, modulo
having getdate as a local variable in scope, getdate and getdate() are the
same. You should use getdate.function instead of getdate().
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CADGZSSejYZXW8E1OTEXbmEc%3DYX_OuYq8BhfsaBSnyEB7qNddQA%40mail.gmail.com.
