The subquery (inner query) you added isn't correlated to the outer (main) query. Since all of the tables in the inner query are already referenced by the outer query, do you need the subquery at all?

-- use meaningful table aliases

-- use proper joins

SELECT

p.Product_Name as Item ,

p.Description as Description ,

c.Currency as Currency,

p.stock_quantity as Quantity,

e.unit_price as Unit_Cost,

p.stock_quantity * e.unit_price as Total_Cost,

p.stock_quantity *

(case when c.id in (3,1) then e.unit_price Else e.unit_price * c.Rate End)

FROM Product p

INNER JOIN Section s

ON p.section_ID = s.section_ID

INNER JOIN Department d

ON s.Department_ID = d.Department_ID

INNER JOIN Entry_New e

ON p.Product_ID = e.Product_ID

INNER JOIN Currency c

ON c.id = e.currency

/*

(

select

case when ci.id in (3,1) then ei.unit_price Else ei.unit_price * ci.Rate End As unit_price_rate

from Currency ci,

Entry_New ei,

Product pin

where ci.id = ei.currency

and pin.Product_ID = ei.Product_ID

) xx

*/

WHERE p.stock_quantity > 0