Moor: Could not parse this expression for CASE WHEN Query in .moor File

[Bramiproject]

I have a problem when doing a Query using the CASE WHEN condition, a problem that appears with the message "Could not parse this expression. Note: This is a reserved keyword, you can escape it in double ticks".

Example of the query:

CASE WHEN ( SELECT A.tittle FROM todo A ) IS NOT NULL THEN CASE WHEN ( ........ ) END

Please help for this problem ...

[simolus3]

On which position in the query does that error occur? I couldn't reproduce this from the snippet you posted (the following parses just fine):

SELECT
CASE 
  WHEN 
    ( SELECT A.tittle FROM todo A ) IS NOT NULL
    THEN 1
  ELSE 0 
END

When parsing queries, moor is a bit more strict than sqlite. Sqlite allows some keywords to be used as column names in queries if that doesn't make the query ambigous. Moor doesn't, so if you want to use a column name that's a reserved keyword, you need to wrap it in double ticks: SELECT a."start" FROM a;.

If you can post the full query, I can help you find the error and how to fix it. But the error message from the generator should point towards the right position.

[Bramiproject]

Hi @simolus3,
Thanks for your advice for this case, I don't know what the problem is in my query, but when it runs on sqlite everything works fine, maybe you can provide a solution, please help me to solve this problem..

SELECT stock.*, CAST((round(CASE WHEN (SELECT td_qtypcs FROM transaction_detail A WHERE A.td_pcode=stock.p_code) IS NOT NULL THEN CASE WHEN (stock.ps_qty - (SELECT SUM(A.td_qtypcs) FROM transaction_detail A WHERE A.td_pcode=stock.p_code)) < stock.p_convunitdua THEN 0 WHEN (stock.ps_qty - (SELECT SUM(A.td_qtypcs) FROM transaction_detail A WHERE A.td_pcode=stock.p_code)) = stock.p_convunitdua THEN 1 ELSE (stock.ps_qty - (SELECT SUM(A.td_qtypcs) FROM transaction_detail A WHERE A.td_pcode=stock.p_code)) END ELSE stock.ps_qty /stock.p_convunitdua END)) AS INTEGER) AS ps_ct, CAST(CASE WHEN (SELECT td_qtypcs FROM transaction_detail A WHERE A.td_pcode=stock.p_code) IS NOT NULL THEN (((stock.ps_qty - (SELECT SUM(A.td_qtypcs) FROM transaction_detail A WHERE A.td_pcode=stock.p_code)) - (stock.p_convunitdua * round((stock.ps_qty - (SELECT SUM(A.td_qtypcs) FROM transaction_detail A WHERE A.td_pcode=stock.p_code))/stock.p_convunitdua)))) ELSE stock.ps_qty - (stock.p_convunitdua * (stock.ps_qty /stock.p_convunitdua)) END AS INTEGER) AS ps_pcs, transaction_outlet.* FROM (SELECT A.*, B.ps_qty FROM product_master A LEFT JOIN product_stock B ON A.p_code = B.ps_code WHERE B.ps_qty > 0) AS stock LEFT JOIN (SELECT B.*, A.td_id, A.td_orderno, A.td_pcode, SUM(A.td_qtyb) AS td_qtyb, SUM(A.td_qtyt) AS td_qtyt, SUM(A.td_qtyk) AS td_qtyk, SUM(A.td_qtypcs) AS td_qtypcs, SUM(A.td_amount) AS td_amount, A.td_sellpriceone, A.td_sellpricetwo, A.td_sellpriceThree, F.*, C.* FROM transaction_detail A LEFT JOIN transaction_header B LEFT JOIN outlet_transaction_detail C JOIN outlet_transaction_header F ON A.td_orderno = B.t_orderno AND C.otd_custnumber=F.oth_custnumber AND F.oth_custnumber=B.t_custnumber WHERE B.t_custnumber = :custNumber AND C.otd_flagsending = "N" GROUP BY B.t_custnumber, A.td_pcode) transaction_outlet ON stock.p_code = transaction_outlet.td_pcode

[simolus3]

Oh wow, that's quite a query. I've fixed the parsing bug in sqlparser. Until the next update, you could wrap the nested CASE expressions in parentheses as a workaround. E.g. IS NOT NULL THEN (CASE WHEN ... )

[Bramiproject]

Wow your very fast in responding to problem, thank you very much for your help, happy to use your library.
I will wait for the update.

Thanks @simolus3

[Bramiproject]

One more question @simolus3,

I have problem in this sub query for this Table Stock is undefined maybe in clause
WHERE A.td_pcode = stock.p_code

SELECT (SELECT td_qtypcs FROM transaction_detail A WHERE A.td_pcode = stock.p_code) AS transactions FROM (SELECT A.*, B.ps_qty FROM product_master A LEFT JOIN product_stock B ON A.p_code = B.ps_code WHERE B.ps_qty > 0) AS stock

Any solution for this case @simolus3 Please help me ...

[simolus3]

Can you post the table definitions? That would make it easier for me to reproduce this, thanks.