Use a value in MySQL query in multiple lines without a variable-CodePudding

I have this mysql query that uses the same value I'm passing, 3 times over.

select p.*,
       ROUND(sum(unitsPerBlock * blocks) / datediff(date(now()), date(?)), 2) as avg
from batches b
inner join products p on b.productID = p.id
where (
          select sum(b1.availableQty)
          from batches b1
          where b1.productID = p.id
            and b1.addedDate between date(?) and date(now())
      ) = 0
and b.addedDate between date(?) and date(now())
group by b.productID
order by avg desc

Is there any way to make this query without me having to type the below

preparedStatement.setString(1, date);
preparedStatement.setString(2, date);
preparedStatement.setString(3, date);

And just use this

preparedStatement.setString(1, date);

CodePudding user response：

select p.*,
       ROUND(sum(unitsPerBlock * blocks) / datediff(CURRENT_DATE, input.parameter), 2) as avg
from batches b
inner join products p on b.productID = p.id

CROSS JOIN (SELECT date(?) AS parameter) AS input

where (
          select sum(b1.availableQty)
          from batches b1
          where b1.productID = p.id
            and b1.addedDate between input.parameter and CURRENT_DATE)
      ) = 0
and b.addedDate between input.parameter and CURRENT_DATE
group by b.productID
order by avg desc