How do I reference current scope?-CodePudding

I frequently get column name clashes, like the following artificial example:

with
  weekdays('name') as (
    values
      ('Monday')
    , ('Tuesday')
    , ('Wednesday')
    , ('Thursday')
    , ('Friday')
    , ('Saturday')
    , ('Sunday')
  )
select
  substr("name",1,3) as "name"
from weekdays

where 1==1
and "name" == 'Monday'
-- how do I reference current scope's name, where "name" == 'Mon'?

In bash it would be:

and ./"name" == 'Monday'

to reference current scope's name instead of first name from the $PATH.

Is there a way to reference current scope in SQLite?

CodePudding user response：

Standard SQL does not allow aliases of derived columns to be used in the WHERE clause, because SELECT is processed after WHERE.

SQLite allows it (probably by replacing the alias in the WHERE clause with its respective expression), but when there is a name conflict with a column of the participating tables/ctes the aliased column is shadowed by the existing column.

This means that in your case you can't refer directly to the aliased column.
Instead repeat the expression:

and substr("name",1,3) = 'Mon'

Or, use a subquery:

select *
from (
  select
    substr("name",1,3) as "name"
  from weekdays
)
where 1=1
and "name" = 'Mon'

Or, better, use a different non-conflicting alias.

See the demo.