MySQL script triggers Error #1241

Question

I have the script below, which is supposed to get a price for an array of ID's that has been provided.

But it needs to get 1 price per ID, and the tricky part is, that I want to have the ability to have scheduled price updates.

This mean that it needs to take the price that is <= UTC_TIMESTAMP smaller or equal to the current time.

SELECT 
`product_pricing`.`wo_id`,
`product_pricing`.`currency` AS price2_currency,
`product_pricing`.`price` AS price2,
`product_pricing`.`formula_id`,
`product_pricing`.`vat_calculated` AS price2_vat_calculated,
`product_pricing`.`vat_id`,
`product_pricing`.`timestamp_valid`,

`product_price_formulas`.`formula_id`,
`product_price_formulas`.`formula` price2_formula

FROM 
`product_pricing`

LEFT JOIN `product_price_formulas` 
ON `product_pricing`.`formula_id` = `product_price_formulas`.`formula_id`

WHERE 
`product_pricing`.`wo_id` IN (

    SELECT 
    `product_pricing`.`wo_id`,
    `product_pricing`.`timestamp_valid`

    FROM `product_pricing`

    WHERE 
    `product_pricing`.`wo_id` 
        IN ('015724', '028791', '015712', '015715', '015717', '039750', '028791')
    AND `product_pricing`.`timestamp_valid` <= UTC_TIMESTAMP

    ORDER BY `product_pricing`.`timestamp_valid` DESC        
 )

Is this possible?

Sample data: Current output

——————————————————————————————————————————————————————————————————
 | wo_id    | price2    | timestamp_valid 
——————————————————————————————————————————————————————————————————
 | 028791   | 8000      | 2018-03-20 19:55:41
 | 028791   | 6000      | 2018-04-01 19:55:41
 | 028791   | 4000      | 2018-04-20 19:55:41
 | 015724   | 3000      | 2018-04-18 19:55:41
 | 015724   | 1500      | 2018-03-01 19:55:41
 ....

Wanted output:

——————————————————————————————————————————————————————————————————
 | wo_id    | price2    | timestamp_valid 
——————————————————————————————————————————————————————————————————
 | 028791   | 6000      | 2018-04-01 19:55:41
 | 015724   | 1500      | 2018-03-01 19:55:41

D-Shih · Accepted Answer · 2018-04-02 14:41:36Z

1

I guess your issue is on the IN clause.

You select two field in IN clause.

EDIT

You need to self join a subquery by wo_id and Max timestamp_valid.

SELECT 
    `product_pricing`.`wo_id`,
    `product_pricing`.`currency` AS price2_currency,
    `product_pricing`.`price` AS price2,
    `product_pricing`.`formula_id`,
    `product_pricing`.`vat_calculated` AS price2_vat_calculated,
    `product_pricing`.`vat_id`,
    `product_pricing`.`timestamp_valid`,
    `product_price_formulas`.`formula_id`,
    `product_price_formulas`.`formula` price2_formula
FROM 
`product_pricing`
LEFT JOIN `product_price_formulas` ON `product_pricing`.`formula_id` = `product_price_formulas`.`formula_id`
INNER JOIN 
(
    SELECT 
        `product_pricing`.`wo_id`,
        MAX(`timestamp_valid`) AS MaxDate
    FROM `product_pricing`
    WHERE 
        `product_pricing`.`timestamp_valid` <= UTC_TIMESTAMP
    GROUP BY 
        `product_pricing`.`wo_id`
)as temp ON temp.wo_id = `product_pricing`.`wo_id` AND temp.MaxDate = `product_pricing`.`timestamp_valid`
WHERE  
    `product_pricing`.`wo_id` IN ('015724', '028791', '015712', '015715', '015717', '039750', '028791')

edited Apr 2, 2018 at 14:41

answered Apr 2, 2018 at 9:38

D-Shih

46.5k6 gold badges38 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Nikk Over a year ago

Huh, yes it worked like that however...it returns multiple prices per ID. It needs to return only 1 which is product_pricing.timestamp_valid <= UTC_TIMESTAMP

D-Shih Over a year ago

Could you provide some sample data and your expect result that will be helpful:)

Nikk Over a year ago

Added. Please take a look at the question

D-Shih Over a year ago

@Borsn Sorry late for reply,The edit wish can help you:)

Nikk Over a year ago

It's returning all values for each id. Instead of filtering it out by timestamp_valid like in the wanted output. What does MAX do in this context?

|

Gordon Linoff · Accepted Answer · 2018-04-02 10:32:14Z

0

I think you want something like this:

SELECT pp.*, ppf.formula_id, ppf.formula as price2_formula
FROM product_pricing pp LEFT JOIN
     product_price_formulas ppf
     ON pp.formula_id = ppf.formula_id    
WHERE (pp.wo_id, pp.timestamp_valid) IN
          (SELECT pp2.wo_id, MAX(pp2.timestamp_valid)
           FROM product_pricing pp2
           WHERE pp2.wo_id IN ('015724', '028791', '015712', '015715', '015717', '039750', '028791') AND
                pp2.timestamp_valid <= UTC_TIMESTAMP
          );

The ORDER BY makes no sense in the subquery, so this is my best guess as to what you want.

I left this with your structure of using IN, but I would use a correlated subquery and =:

WHERE pp.timestamp_valid = (SELECT MAX(pp2.timestamp_valid)
                            FROM product_pricing pp2
                            WHERE pp2.wo_id = pp.wo_id AND
                                  pp2.timestamp_valid <= UTC_TIMESTAMP
                           ) AND
      pp2.wo_id IN ('015724', '028791', '015712', '015715', '015717', '039750', '028791');

answered Apr 2, 2018 at 10:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Nikk Over a year ago

You only seem to be selecting the formula. How would I add all of the other rows I have in the original SELECT?

Gordon Linoff Over a year ago

@Borsn . . . You can list them in the select. I shorted that to pp.*.

Paul Campbell · Accepted Answer · 2018-04-02 11:36:53Z

0

You could JOIN product_pricing with a derived result set of the most recent valid_timestamp records.

If we ignore the product_pricing_formula table for now (since you didn't include it in your specimen data and result) that would give.

SELECT 
p.`wo_id`,
p.`price` AS price2,
p.`timestamp_valid`
FROM `product_pricing` p
JOIN (SELECT wo_id, MAX(timestamp_valid) AS max_valid_ts
      FROM `product_pricing`
      WHERE `timestamp_valid` <= UTC_TIMESTAMP
      GROUP BY wo_id) d
ON (d.wo_id = p.wo_id AND d.max_valid_ts = p.timestamp_valid)
WHERE p.`wo_id` IN (015724, 028791);

Try it on Sqlfiddle

answered Apr 2, 2018 at 11:36

Paul Campbell

1,9862 gold badges14 silver badges19 bronze badges

Collectives™ on Stack Overflow

MySQL script triggers Error #1241

3 Answers 3

7 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related