Creating view with fixed value

[former_member671436]

I'm migrating a view from SQLServer to SAP HANA. That part of the view in SQL server is like follows

"1" AS "tipo_emision",
"" AS "clave_acceso", 
"" AS "guia_remision", 
"" AS "direccion_emisor", 
"0990907161001" AS "ruc", 

but when trying to put that on Hana, it gives several errors (I tried manipulating them) as

Could not execute 'SELECT oinv."FolioNum" AS "secuencial", oinv."DocDate" AS "fecha_emision", oinv."CardName" AS ...'
SAP DBTech JDBC: [260]: invalid column name: 1: line 25 col 1 (at pos 1100)
Could not execute 'SELECT oinv."FolioNum" AS "secuencial", oinv."DocDate" AS "fecha_emision", oinv."CardName" AS ...' in 16 ms 960 µs . 
SAP DBTech JDBC: [339]: invalid number:  [6930] attribute value is not a number 
Could not execute 'SELECT oinv."FolioNum" AS "secuencial", oinv."DocDate" AS "fecha_emision", oinv."CardName" AS ...'
SAP DBTech JDBC: [260]: invalid column name: 0990907161001: line 29 col 1 (at pos 1207)

[pfefferf]

It would be great if you can show your view definition you are trying to activate.

But I guess that if you have the fix value columns defined like "1" as "tipo_emision", the error is clear. "1" is enterpreted as column name, if you wanna have 1 as a character based value than you need to enclose it in single quotes, e.g. '1' as "tipo_emision".

[former_member671436]

Full select is in HANA as right now as follows

SELECT       
oinv."FolioNum" AS "secuencial", 
oinv."DocDate" AS "fecha_emision", 
oinv."CardName" AS "razon_social", 
oinv."DocEntry" AS "id_factura", 
ocrd."LicTradNum" AS "identificacion", 
CASE ocrd."U_TIPO_ID" WHEN 'R' THEN '04' WHEN 'C' THEN '05' WHEN 'P' THEN '06' WHEN 'F' THEN '07' END AS "tipo_identificacion",
ocrd."E_Mail" AS "email",
ocrd."Phone1" AS "telefono", 
ocrd."Address" AS "direccion", 
'USD' AS "moneda", 
oinv."U_SER_EST" AS "codigo", 
oinv."U_SER_PE" AS "punto_emision", 
(oinv."DocTotal" - oinv."VatSum") +
     (SELECT  SUM(inv1."PriceBefDi" * inv1."Quantity") 
       FROM   inv1 
       WHERE  (inv1."DocEntry" = oinv."DocEntry")) * (oinv."DiscPrcnt" / 100) AS "total_sin_impuestos",
oinv."DocTotal" AS "importe_total",
     (SELECT  SUM(inv1."PriceBefDi" * (inv1."DiscPrcnt" / 100) * inv1."Quantity") 
       FROM   inv1
       WHERE  (inv1."DocEntry" = oinv."DocEntry")) +
     (SELECT  SUM(inv1."PriceBefDi" * inv1."Quantity")
       FROM   inv1
       WHERE  (inv1."DocEntry" = oinv."DocEntry")) * (oinv."DiscPrcnt" / 100) AS "descuento", 
'1' AS "tipo_emision",
NULL AS "clave_acceso", 
NULL AS "guia_remision", 
NULL AS "direccion_emisor", 
"0990907161001" AS "ruc", 
"werwerwer" AS "razon_social_emisor", 
"SI" AS "obligado_contabilidad", 
"136" AS "contribuyente_especial", 
"asdas" AS "nombre_comercial", 
"qweqweqwe" AS "direccion_matriz", 
NULL AS "direccion_establecimiento", 
"0.00" AS "valor_retenido_iva", 
"0.00" AS "valor_retenido_renta", 
"0.00" AS "propina",
(SELECT   SUM(inv1."PriceBefDi" * inv1."Quantity")
  FROM    inv1
  WHERE   (inv1."DocEntry" = oinv."DocEntry")) * (oinv."DiscPrcnt" / 100) AS "descuento_adicional"
FROM           
oinv 
INNER JOIN
ocrd ON ocrd."CardCode" = oinv."CardCode"
WHERE
(oinv."FolioNum" IS NOT NULL) 
AND (oinv."FolioNum" <> 0) 
AND (oinv."U_tipo_comprob" = 18);

Now it gives

Could not execute 'SELECT oinv."FolioNum" AS "secuencial", oinv."DocDate" AS "fecha_emision", oinv."CardName" AS ...'
SAP DBTech JDBC: [260]: invalid column name: 0990907161001: line 29 col 1 (at pos 1207)

[pfefferf]

Same isse. All what is not a column, but should be a character based fix value needs to be enclosed in single quotes and not in double quotes. Always the same thing.

[former_member671436]

Query is

SELECT        
oinv."FolioNum" AS "secuencial", 
oinv."DocDate" AS "fecha_emision", 
oinv."CardName" AS "razon_social", 
oinv."DocEntry" AS "id_factura", 
ocrd."LicTradNum" AS "identificacion", 
CASE ocrd."U_TIPO_ID" WHEN 'R' THEN '04' WHEN 'C' THEN '05' WHEN 'P' THEN '06' WHEN 'F' THEN '07' END AS "tipo_identificacion",
ocrd."E_Mail" AS "email",
ocrd."Phone1" AS "telefono", 
ocrd."Address" AS "direccion", 
'USD' AS "moneda", 
oinv."U_SER_EST" AS "codigo", 
oinv."U_SER_PE" AS "punto_emision", 
(oinv."DocTotal" - oinv."VatSum") +
     (SELECT  SUM(inv1."PriceBefDi" * inv1."Quantity") 
       FROM   inv1  
       WHERE  (inv1."DocEntry" = oinv."DocEntry")) * (oinv."DiscPrcnt" / 100) AS "total_sin_impuestos",
oinv."DocTotal" AS "importe_total",
     (SELECT  SUM(inv1."PriceBefDi" * (inv1."DiscPrcnt" / 100) * inv1."Quantity") 
       FROM   inv1
       WHERE  (inv1."DocEntry" = oinv."DocEntry")) +
     (SELECT  SUM(inv1."PriceBefDi" * inv1."Quantity")
       FROM   inv1
       WHERE  (inv1."DocEntry" = oinv."DocEntry")) * (oinv."DiscPrcnt" / 100) AS "descuento", 
'1' AS "tipo_emision",
NULL AS "clave_acceso", 
NULL AS "guia_remision", 
NULL AS "direccion_emisor", 
'0990907161001' AS "ruc", 
'HERMAPROVE S.A.' AS "razon_social_emisor", 
'SI' AS "obligado_contabilidad", 
'136' AS "contribuyente_especial", 
'HERMAPROVE' AS "nombre_comercial", 
'VIA DURAN BOLICHE KM. 3.5' AS "direccion_matriz", 
NULL AS "direccion_establecimiento", 
'0.00' AS "valor_retenido_iva", 
'0.00' AS "valor_retenido_renta", 
'0.00' AS "propina",
(SELECT   SUM(inv1."PriceBefDi" * inv1."Quantity")
  FROM    inv1
  WHERE   (inv1."DocEntry" = oinv."DocEntry")) * (oinv."DiscPrcnt" / 100) AS "descuento_adicional"
FROM            
oinv 
INNER JOIN
ocrd ON ocrd."CardCode" = oinv."CardCode"
WHERE
(oinv."FolioNum" IS NOT NULL) 
AND (oinv."FolioNum" <> 0) 
AND (oinv."U_tipo_comprob" = 18);

And error is

Could not execute 'SELECT oinv."FolioNum" AS "secuencial", oinv."DocDate" AS "fecha_emision", oinv."CardName" AS ...' in 11 ms 325 µs . 
SAP DBTech JDBC: [339]: invalid number:  [6930] attribute value is not a number 

[pfefferf]

You need to check if one of your fields used in the calculations which expect a number are really numbers (aka check the type of the fields).