Firemonkey TSQLQuery doesn’t retrieve sqlite subquery in select field

  

Does anyone know why in C++ Builder Firemonkey application TSQLQuery can’t retrieve SubQuery field within a select. Please see my Code below as I cant retrieve SPrice1 and SPrice2 as it says it doesn’t exist.

Note: I use C++ Builder 10.3 Update1

Note: Exactly the same SQL perfectly working fine in c# applications and other Sqlite management tools.

myQuery->Close();

myQuery->SQL->Text = “SELECT P.Description1,P.Code,P.Notes,L.InvoiceDate,”;
myQuery->SQL->Add(“(SELECT Price FROM Prices WHERE ProductID=P.ProductID AND PriceID=:pPriceID1) AS SPrice1 “);
myQuery->SQL->Add(“(SELECT Price FROM Prices WHERE ProductID=P.ProductID AND PriceID=:pPriceID2) AS SPrice2 “);
myQuery->SQL->Add(” FROM Products P LEFT OUTER JOIN LastPrices L ON L.ProductID=P.ProductID AND L.CustomerID=:pCustomerID WHERE P.ProductID > 0 AND P.Description LIKE ‘%TEST%'”);

myQuery->ParamByName(“pPriceID1”)->AsInteger = PriceID1 ;
myQuery->ParamByName(“pPriceID2”)->AsInteger = PriceID2 ;
myQuery->ParamByName(“pCustomerID”)->AsInteger = CustomerID;

myQuery->Open();

I even tried to have them in LEFT JOINED and it worked only for 1 query only retrived SPrice1 but not SPrice2

myQuery->Close();

myQuery->SQL->Text = “SELECT P.Description1,P.Code,P.Notes,L.InvoiceDate,P1.Price AS SPrice1,P2.Price AS SPrice2 “;
myQuery->SQL->Add(” FROM Products P LEFT OUTER JOIN LastPrices L ON L.ProductID=P.ProductID AND L.CustomerID=:pCustomerID “);
myQuery->SQL->Add(” LEFT JOIN Prices P1 ON P1.ProductID=P.ProductID AND P2.PriceID=:pPriceID1 “)
myQuery->SQL->Add(” LEFT JOIN Prices P2 ON P2.ProductID=P.ProductID AND P2.PriceID=:pPriceID2 “)
myQuery->SQL->Add(“WHERE P.ProductID > 0 AND P.Description LIKE ‘%TEST%'”);

myQuery->ParamByName(“pPriceID1”)->AsInteger = PriceID1 ;
myQuery->ParamByName(“pPriceID2”)->AsInteger = PriceID2 ;
myQuery->ParamByName(“pCustomerID”)->AsInteger = CustomerID;

myQuery->Open():

Comments are closed.