0
It is possible to perform a Decode in a select that returns all data from a table and some from other tables?
In this select, I would like to make a Decode for one of the columns of PP (pedido_peca), but as it makes a full search I do not know if it is possible and if possible, I do not know how.
For example: Decode (cd_group, 1, Peca, 2, Accessory, null) 'Column of the requested table_peca in which I want to perform the Decode.
SELECT pp.*,
g.nu_ordem_servico,
g.dt_entrada,
(g.dt_entrada + 30) as dt_vencimento,
(select w.nm_perfil2
from workflow w
where w.id_processo = 7
and w.cd_laudo = pp.id_status) as ds_status,
conc.nu_cnpj || ' - ' || conc.sg_concessionario || ' - ' ||
conc.nm_concessionario || ' - ' || conc.nm_cidade || ' - ' ||
conc.sg_uf as sg_nm_concessionario,
(select count(1)
from ivv
where dt_exclusao is null
and nu_chassi = pp.ds_custom01
and dt_limite_garantia > pp.dt_pedido) as ic_tem_garantia
FROM pedido_peca pp
left join garantia g
on pp.nu_sg = g.nu_sg, concessionario conc
WHERE pp.dt_exclusao is null
and conc.dt_exclusao is null
and g.dt_exclusao is null
and pp.cd_concessionario = conc.id_concessionario
and pp.id_pedido = 50360
and pp.cd_concessionario = 5
Here is the code in VB.NET that will return this select above.
cfg.Abrir(DBProvider.enConnectionType.PEC)
'Obtem Pedido
sb = New System.Text.StringBuilder
sb.Append(" SELECT ")
sb.Append(" pp.*, ")
sb.Append(" g.nu_ordem_servico, ")
sb.Append(" g.dt_entrada, ")
sb.Append(" (g.dt_entrada + 30) as dt_vencimento, ")
sb.Append(" (select w.nm_perfil" & idPerfil & " from workflow w where w.id_processo=7 and w.cd_laudo=pp.id_status) as ds_status, ")
sb.Append(" conc.nu_cnpj ||' - '|| conc.sg_concessionario ||' - '|| conc.nm_concessionario ||' - '|| conc.nm_cidade ||' - '|| conc.sg_uf as sg_nm_concessionario, ")
sb.Append(" (select count(1) from ivv where dt_exclusao is null and nu_chassi = pp.ds_custom01 and dt_limite_garantia > pp.dt_pedido) as ic_tem_garantia ")
sb.Append(" FROM pedido_peca pp left join garantia g on pp.nu_sg = g.nu_sg, concessionario conc ")
sb.Append(" WHERE ")
sb.Append(" pp.dt_exclusao is null and ")
sb.Append(" conc.dt_exclusao is null and ")
sb.Append(" g.dt_exclusao is null and ")
sb.Append(" pp.cd_concessionario = conc.id_concessionario and ")
sb.Append(" pp.id_pedido = " & idPedido.ToString)
If HttpContext.Current.Session("nivelusuario") > 10 Then
sb.Append(" and pp.cd_concessionario = " & HttpContext.Current.Session("concessionario"))
End If
cfg.Fill(ds.Tables("Pedido"), sb.ToString)
Are the speakers the same type? If yes, the Decode syntax is an IF THEN ELSE then it should have a maximum of 3 parameters and you passed 6. It can explain the real condition of Decode including fields and condition for evaluation?
– Caputo
The syntax of DECODE is limited to the more flexible CASE, thought to use CASE ?
– Motta
@Caputo Which columns ? Because in Decode I am referring to only one column, in which is the pp.cd_group of type varchar2. My intention is to make this column if the value is 1 bring the result Part and case value 2 return Accessory.
– Igor
@Motta I made an attempt with CASE, but I found the same problem. When performing the CASE instead of bringing the expected result, an extra column is added with the CASE result, just like when I do the DECODE.
– Igor