Skip to main content

SQL (sqlite3) question

I've run into a snag with an sqlite database I've been working on. Below is a simplified example of the problem.

Suppose I have the following table:

CREATE TABLE "prices" (
    "id"    INTEGER NOT NULL UNIQUE,
    "name"  TEXT NOT NULL UNIQUE,
    "list_price"    NUMERIC NOT NULL,
    "sale_price"    NUMERIC,
    "tax_rate"  NUMERIC NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
);

Is there a way to do something like the following?
SELECT
    name,
    CASE
        WHEN sale_price IS NULL
            THEN list_price
        ELSE sale_price
    END AS price,
    price * tax_rate AS tax
FROM prices;

The tax column doesn't seem to acknowledge the price column's existence, presumably because it's a column in the query rather than the source table. I could re-implement the CASE logic for the tax field, but that feels inelegant and error-prone.

Is there a better way to do this?

Shannon Prickett reshared this.

in reply to Jonathan Lamothe

Isaac Ji Kuo
Isaac Ji Kuo
• •
SQL (sqlite3) question

Sensitive content

in reply to Isaac Ji Kuo

Jonathan Lamothe
Jonathan Lamothe
•
SQL (sqlite3) question
@Isaac Ji Kuo Given how hacky the real query is, if this works, I'll take it.
@Isaac Ji Kuo
in reply to Jonathan Lamothe

Isaac Ji Kuo
Isaac Ji Kuo
• •
SQL (sqlite3) question

Sensitive content

in reply to Isaac Ji Kuo

Jonathan Lamothe
Jonathan Lamothe
•
SQL (sqlite3) question
@Isaac Ji Kuo A slightly modified version of this worked beautifully. Thanks.
If you're morbidly curious, the final resulting code was:
CREATE VIEW ext_prices AS SELECT
    sub.*,
    sub.effective_price * sub.tax_rate AS tax,
    sub.effective_price * (1 + sub.tax_rate) AS subtotal
FROM
    (SELECT
        p.*,
        t.name AS tax_name,
        t.rate AS tax_rate,
        CASE
            WHEN p.sale_price IS NULL
                THEN p.last_price
            ELSE p.sale_price
        END AS effective_price
    FROM
        prices AS p
        INNER JOIN
        taxes AS t
        ON p.tax_id = t.id
    ) AS sub
@Isaac Ji Kuo
in reply to Jonathan Lamothe

((( David "Kahomono" Frier )))
((( David "Kahomono" Frier )))
• •
seems like an order of operations issue... "price" is not getting created until after the attempt is made to calculate "tax"
in reply to ((( David "Kahomono" Frier )))

Jonathan Lamothe
Jonathan Lamothe
•

@((( David "Kahomono" Frier ))) Yeah, that was my assumption. I just wasn't sure what the best way to go about fixing it was. So far, this is the front-runner:

Isaac Ji Kuo

2024-06-13 21:30:16


Probably not as elegant as you'd like, but:

SELECT tem.*,
price*tax_rate AS tax
FROM
(SELECT name,
CASE
WHEN sale_price IS NULL
THEN list_price
ELSE sale_price
END AS price
FROM prices) AS tem;


@((( David "Kahomono" Frier )))
in reply to Jonathan Lamothe

((( David "Kahomono" Frier )))
((( David "Kahomono" Frier )))
• •
That's not as klunky as you're thinking. RelDB systems do a lot of "planning" for us, and sometimes it goes awry and we have to correct them.
in reply to Jonathan Lamothe

Ryan Frame
Ryan Frame
• •
You can use WITH to do it in two steps:
WITH pre_price AS (
    SELECT
        name,
        CASE WHEN sale_price IS NULL
            THEN list_price
            ELSE sale_price END
        AS price,
        tax_rate FROM prices
    )
    SELECT
        name,
        price,
        price * tax_rate AS tax
    FROM pre_price;
in reply to Jonathan Lamothe

Gwen (Almond Joy->Mounds arc)
Gwen (Almond Joy->Mounds arc)
• •
SQL (sqlite3) question

Sensitive content

This website uses cookies. If you continue browsing this website, you agree to the usage of cookies.

⇧