Kiplacon Kiplacon - 4 months ago 9
SQL Question

Asking to enter a parameter value that already exists

I have a three column table of price breaks called "FPB" that looks like this:

[Part Number] [Quantity] [Price]
AAA-AAAA     100   1.23
AAA-AAAA     200   1.15
BBB-BBBB     100   5.60
CCC-CCCC      500   3.21
....


Where each part number has multiple entries in multiple rows.

I'm trying to reorganize the table to look more like this

[Part Number] [Quantity1] [Price 1] [Quantity 2] [Price 2] [Quantity 3....
AAA-AAAA      100   1.23    200    1.15   ....
BBB-BBBB      100   5.60     ...
CCC-CCCC       500   3.21    ...
...


Where each part number has all its entries combined into one row. The first quantity column should have the lowest available quantity, the second should have the second smallest etc. I am trying to do this by first creating a 1-column table with just the unique part numbers using GROUP BY, and then creating more tables for each column that has the information I want in that column, and then joining it by Part Number. The problem comes when calculating the second smallest quantity for each type, done in the second to last section.

SELECT PNs.[Part Number], Q1T.Q1, P1T.Price, Q2T.Q2
FROM
(SELECT
[Part Number]
FROM FPB
GROUP BY [Part Number]
) AS PNs,

(SELECT
[Part Number],
MIN(Quantity) AS Q1
FROM FPB
GROUP BY [Part Number]
) AS Q1T,

(SELECT
*
FROM FPB
) AS P1T,

(SELECT
[Part Number],
MIN(IIF(Quantity>Q1T.Q1,Quantity)) AS Q2
FROM FPB
GROUP BY [Part Number]
) AS Q2T

WHERE
PNs.[Part Number] = Q1T.[Part Number]
AND P1T.[Part Number] = PNs.[Part Number]
AND P1T.Quantity = Q1T.Q1
AND Q2T.[Part Number] = PNs.[Part Number]


When I run this query, it asks me to enter a parameter value for Q1T.Q1, even though it already exists. If I remove the code section for Q2T, as well as any references to Q2, it will work without a problem, and it won't ask about a value for the other instances of Q1T.Q1. Why doesn't Q1T.Q1 have a value just for that section, and how can I fix it? As a side note, I'm using the SQL features of a program called PHPRunner, and its client doesn't support UPDATE/DELETE/INSERT/CREATE queries, UNION, and DISTINCT.

Answer

You're looking for something like this.

select
    p1.PartNumber,
    ifnull(max(p2.Quantity), 0) + 1 as LowerQuantity,
    p1.Quantity as UpperQuantity,
    p1.Price,
    count(p2.PartNumber) + 1 as PriceTier
from
    FPB p1 left outer join FPB p2
        on p2.PartNumber = p1.PartNumber and p2.Quantity < p1.Quantity

From there it's easy to pivot in order to insert into a new table:

into into NewFPB (PartNumber, Quantity1, Price1, Quantity2, Price2, ...)
select
    PartNumber,
    min(switch(PriceTier = 1, UpperQuantity)) as Quantity1,
    min(switch(PriceTier = 2, UpperQuantity)) as Quantity2, ...
    min(switch(PriceTier = 1, Price)) as Price1,
    min(switch(PriceTier = 2, Price)) as Price2, ...        
from (
    select
        p1.PartNumber,
        ifnull(max(p2.Quantity), 0) + 1 as LowerQuantity,
        p1.Quantity as UpperQuantity,
        p1.Price,
        count(p2.PartNumber) + 1 as PriceTier
    from
        FPB p1 left outer join FPB p2
            on p2.PartNumber = p1.PartNumber and p2.Quantity < p1.Quantity
) data

You might have to tweak it a little bit for Access to accept it. But the core ideas are there.