asked on

Join against a derived column

Suppose I have a simple table called transactions

Reference Price
B1234 100
S1234 101
S2222 86
B2222 85

Can I do a simple query to match the B & S records or do I need to make a synthetic table to do the join?

Reference is always 5 characters with the 4 digit numeric identifying the pairs.

SELECT
T1.Reference,
CASE
WHEN substring(T1.Reference, 1, 1) = "S" THEN "B" + substring(T1.Reference, 2, 4) -- Sell
WHEN substring(T1.Reference, 1, 1) = "B" THEN "S" + substring(T1.Reference, 2, 4) -- Buy
end matching_reference,
T1.Price- T2.Price
FROM
transactions T1,
transactions T2
WHERE
T2.Reference= matching_reference

I know I can't do the above... since it gives me a error in the joing not knowing "matching_reference"

Thanks.

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

James Talvy

ASKER

What if t1 is first character is 'S' then you need to join against 'B'

I know the following works:

SELECT 
    T1.Reference,
    T2.Reference,
    T1.Price - T2.Price
FROM
    (
    SELECT 
        Reference,
        CASE
            WHEN substring(T1.Reference, 1, 1) = "S" THEN "B" + substring(T1.Reference, 2, 4) -- Sell
            WHEN substring(T1.Reference, 1, 1) = "B" THEN "S" + substring(T1.Reference, 2, 4) -- Buy
        end matching_reference
    FROM
        transactions
    ) PAIRS,
    transactions T1,
    transactions T2
WHERE
    T1.Reference = PAIRS.Reference and
    T2.Reference = PAIRS.matching_reference

Open in new window

But I thought there was a way to do it without creating the synthetic table PAIRS.

ASKER CERTIFIED SOLUTION