title: How to address a computed column value in an SQLite query?
body: <p>Having a following table:</p>
  
  <pre><code>CREATE TABLE `foo`(
    `year` INT NOT NULL,
    `month` INT NOT NULL,
    `day` INT NOT NULL,
    `hour` INT NOT NULL,
    `minute` INT NOT NULL,
    `value` INT NOT NULL,
    PRIMARY KEY(`year`, `month`, `day`, `hour`, `minute`)
  );
  </code></pre>
  
  <p>I want to write a query which will add 2 columns for every record: <code>date</code> for standard single-column data representation and <code>weekday</code> to indicate day-of-a-week number.</p>
  
  <p>I have tried</p>
  
  <pre><code>SELECT
      `year`, `month`, `day`, `hour`, `minute`, `value`,
      substr('000' || year, -4) || '-' || substr('0' || month, -2) || '-' || substr('0' || day, -2) AS `date`,
      strftime('%w', `date`) AS `weekday`
  FROM
      `foo`;
  </code></pre>
  
  <p>But this says</p>
  
  <pre><code>Error: no such column: date
  </code></pre>
  
  <p>This is an illustration. In real I have much more complex logic in calculating additional columns and need to reuse these calculated columns to calculate other columns and just copying whole code in every place I need its value look quite scary.</p>
  
  <p>Is there a way I can address a computed column value in computing another one?</p>