| Subject | Re: [firebird-support] Cast as domain in view - wrong datatype |
|---|---|
| Author | Jorge Andres Brugger |
| Post date | 2017-08-24T13:42:49Z |
Thanks Dimitry.
Changing the query to:
select errores.error, errores.descripcion, errores.gtin, errores.serie, errores.fecha, coalesce(trazabilidad_errores.tipo_error,0) as TIPO_ERROR
from trazabilidad_errores inner join (
select
cast(substring(trazabilidad_informes_mov.resultado from position(':' in trazabilidad_informes_mov.resultado)+1 for (position(' -' in trazabilidad_informes_mov.resultado)-2-position(':' in trazabilidad_informes_mov.resultado)+1)) as integer) as error,
case
when position('.' in trazabilidad_informes_mov.resultado)>0 then cast(substring(trazabilidad_informes_mov.resultado from position(' - ' in trazabilidad_informes_mov.resultado)+3 for (position('.' in trazabilidad_informes_mov.resultado)-position('- ' in trazabilidad_informes_mov.resultado))) as varchar(150))
else null end as descripcion,
case
when position('GTIN: ' in trazabilidad_informes_mov.resultado)>0 then cast(substring(trazabilidad_informes_mov.resultado from position('GTIN: ' in trazabilidad_informes_mov.resultado)+6 for 14) as type of d_gtin)
else null end as GTIN,
case
when position('SERIE: ' in trazabilidad_informes_mov.resultado)>0 then cast(substring(trazabilidad_informes_mov.resultado from position('SERIE: ' in trazabilidad_informes_mov.resultado)+7 for (strlen(trazabilidad_informes_mov.resultado)-position('SERIE: ' in trazabilidad_informes_mov.resultado)+7)) as varchar(20))
else null end as serie,
max(trazabilidad_informes_mov.fecha_informe) as fecha
from trazabilidad_informes_mov
where trazabilidad_informes_mov.resultado like '%ERROR%' and substring(trazabilidad_informes_mov.resultado from position(':' in trazabilidad_informes_mov.resultado)+1 for (position(' -' in trazabilidad_informes_mov.resultado)-2-position(':' in trazabilidad_informes_mov.resultado)+1)) > 0
group by trazabilidad_informes_mov.resultado
) as errores on trazabilidad_errores.codigo_error = errores.error
GTIN output is decimal(18,0)
Is there a reason to not output decimal(14,0)?
Thanks a lot!
2017-08-24 10:22 GMT-03:00 Dimitry Sibiryakov sd@... [firebird-support] <firebird-support@yahoogroups.com>:
24.08.2017 14:49, Jorge Andres Brugger jorge.brugger@... [firebird-support] wrote:
> View GTIN field is "exported" as varchar(20), instead of decimal(14,0).
> Am I doing domething wrong or could be a bug?
Because of your "else '' end as GTIN" server has no choice. Result of whole CASE
expression must be (var)char.
--
WBR, SD.
------------------------------ ------
------------------------------ ------
++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++ ++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/ resources/documents/
++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++ ++++++
------------------------------ ------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/ firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-digest@ yahoogroups.com
firebird-support-fullfeatured@ yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@ yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/ us/yahoo/utos/terms/