CODEWITHSUNDEEP
sqlms-access-2013
Kiplacon
Kiplacon

Reputation: 35

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.

Upvotes: 2

Views: 276

Answers (3)

shawnt00
shawnt00

Reputation: 17915

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.

Upvotes: 1

Dance-Henry
Dance-Henry

Reputation: 953

As per your question, by the time you define the derived table Q2T, Q1T is still an invalid object. You need to try to work around this issue.

EDIT: Suppose you only got 2-level columns to handle, the code is listed below, i tested it. It works well.

select q5.*,q3.quantity, q3.price

from

(select *
from FPB as Q1
where 0 = (select count(distinct(quantity)) from FPB as Q2 where Q2.quantity < Q1.quantity AND Q2.[part number] = Q1.[part number])) as Q5
,
(
select distinct(temp.[part number]),Q2.quantity, Q2.price from FPB as temp
left join
(select *
from FPB as Q4
where 1 = (select count(distinct(quantity)) from #test as Q2 where Q2.quantity < Q4.quantity AND Q2.[PART NUMBER] = Q4.[PART NUMBER])) as Q2
on temp.[PART NUMBER] = Q2.[PART NUMBER]
) as Q3

where Q5.[PART NUMBER] = Q3.[PART NUMBER]

Upvotes: 0

M.Hassan
M.Hassan

Reputation: 11032

The query you call is incorrect.

Q1T is inner select statement , and in Q2T (other inner select statement) , you can't use any field from Q1T

The SQL Server raise error: Incorrect syntax near ')'.

To overcome this limitation , you should use Common Table Expressions CTE for PNs, Q1T, P1T, Q2T

CTE is like dynamic view. It's a new feature since sql 2008 and It's a very powerful.

review: https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Try to Draw a relational data model for these four CTE to be sure that a relation exist between them based on your where conditions. I think the logic in this query may raise runtime error during execution:

e.g. The multi-part identifier "Q1T.Q1" could not be bound.

Edit:

For Ms-Access you can create four queries for: PNs, Q1T, P1T, Q2T every one is in a separate query, and the fifth query join these queries and add where conditions. In this case you will not get any syntax error. and will get Data model with relation free :) .

Upvotes: 1

Related Questions