cancel
Showing results for 
Search instead for 
Did you mean: 

Problem SQL query in HANA

0 Kudos

Hi team,

i'm trying to pass some SQL query to HANA SAP B1 but have some troubles on it.

SET SCHEMA "SBOURBANOR"; SELECT t0.ItemCode, t0.itemname, (select case when t0.itemcode like 'AN1%' then 'ASAI NORTE 1' when t0.itemcode like 'AN2%' then 'ASAI NORTE 2' when t0.itemcode like 'TS5%' then 'TODOS SANTOS 5' when t0.itemcode like 'TS6%' then 'TODOS SANTOS 6' END) as 'Ubicación' , t1.price AS 'Precio de lista contado', substring (t0.itemcode, 5,3) as UV, substring (t0.itemcode, 9,3) as MZA, substring (t0.itemcode, 13,3) as '# Lote/Casa', substring (t0.itemname, 1,4) as Tipo, t0.U_superficie as 'Superficie Mts2' FROM OITM T0 INNER JOIN ITM1 T1 ON T0.[ItemCode] = T1.[ItemCode] INNER JOIN OPLN T2 ON T1.PriceList = T2.ListNum WHERE t0.Iscommited <= 0 and t0.onhand>0 and t0.onorder<=0 and T2.[ListName] = 'CONTADO' AND T1.Price <>0

and get the error: Could not execute 'SELECT t0.ItemCode, t0.itemname, (select case when t0.itemcode like 'AN1%' then 'ASAI NORTE 1' when ...' SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near ")": line 5 col 52 (at pos 252)

can you help finding the problem please? i'm new in HANA.

Thanks

Accepted Solutions (0)

Answers (2)

Answers (2)

MD1
Active Contributor
0 Kudos

try this

SELECT t0."ItemCode", t0."ItemName",
(SELECT
CASE
WHEN t0."ItemCode" LIKE 'AN1%' THEN 'ASAI NORTE 1'
WHEN t0."ItemCode" LIKE 'AN2%' THEN 'ASAI NORTE 2'
WHEN t0."ItemCode" LIKE 'TS5%' THEN 'TODOS SANTOS 5'
WHEN t0."ItemCode" LIKE 'TS6%' THEN 'TODOS SANTOS 6'
END
FROM DUMMY) AS "Ubicación", t1."Price" AS "Precio de lista contado", substring(t0."ItemCode", 5, 3) AS "UV",
substring(t0."ItemCode", 9, 3) AS "MZA", substring(t0."ItemCode", 13, 3) AS "# Lote/Casa",
substring(t0."ItemName", 1, 4) AS "Tipo", t0."U_superficie" AS "Superficie Mts2"
FROM OITM T0
INNER JOIN ITM1 T1 ON T0."ItemCode" = T1."ItemCode"
INNER JOIN OPLN T2 ON T1."PriceList" = T2."ListNum"
WHERE t0."IsCommited" <= 0 AND t0."OnHand" > 0 AND t0."OnOrder" <= 0 AND T2."ListName" = 'CONTADO' AND
T1."Price" <> 0

KonradZaleski
Active Contributor
0 Kudos

"select" statement is not relevant for 'Ubicación' syntax. Try syntax without "select":

case when t0.itemcode like 'AN1%' then 'ASAI NORTE 1' when t0.itemcode like 'AN2%' then 'ASAI NORTE 2' when t0.itemcode like 'TS5%' then 'TODOS SANTOS 5' when t0.itemcode like 'TS6%' then 'TODOS SANTOS 6' END as 'Ubicación'