-
MS SQL * Oracle
People, I possess the following line of command sql in Ms Sql and would
like to convert this line for the Oracle 8i, can help me? please.
SELECT Janela.idJanela, Janela.strTitulo,
FORMULARIO_Preenchimento.idPreenchimento,
FORMULARIO_Preenchimento.dtPreenchimento,
FORMULARIO_Status.idStatus,
FORMULARIO_Status.strNome,
CASE
WHEN ( FORMULARIO_Config.intPrazoExecucao = 0 ) THEN 0
ELSE
CASE
WHEN ((to_char(FORMULARIO_Preenchimento.dtAprovacao,'dd ') - to_char(sysdate,'dd')) > FORMULARIO_Config.intPrazoExecucao ) THEN 1
ELSE 0
END
END "intAtrazo",
(SELECT Usuario.strNomeUsuario
FROM Usuario
WHERE Usuario.idUsuario = FORMULARIO_Preenchimento.idUsuario) strNomeUsuario,
case when ((select (select strNome from formulario_tipo where idTipo in( select idTipo from formulario_campo where idCampo = formulario_config.idCampoKey)) as tipo from formulario_config where idJanela = formulario_preenchimento.idJanela) = 'Data') then
case when to_char((select nvl(dtData, '') from formulario_resposta where idCampo in (select idCampoKey from formulario_config where idJanela = formulario_preenchimento.idJanela) and idPreenchimento = formulario_Preenchimento.idPreenchimento), 'dd/mm/yyyy') = '01/01/1900' then
''
Else
to_char((select nvl(dtData, '') from formulario_resposta where idCampo in (select idCampoKey from formulario_config where idJanela = formulario_preenchimento.idJanela) and idPreenchimento = formulario_Preenchimento.idPreenchimento), 'dd/mm/yyyy')
End
when ((select (select strNome from formulario_tipo where idTipo in( select idTipo from formulario_campo where idCampo = formulario_config.idCampoKey)) as tipo from formulario_config where idJanela = formulario_preenchimento.idJanela) = 'Hora') then
case when to_char((select nvl(dtData, '') from formulario_resposta where idCampo in (select idCampoKey from formulario_config where idJanela = formulario_preenchimento.idJanela) and idPreenchimento = formulario_Preenchimento.idPreenchimento), 'hh:mm:ss') = '00:00:00' then
''
Else
lpad(to_char((select nvl(dtData, '') from formulario_resposta where idCampo in (select idCampoKey from formulario_config where idJanela = formulario_preenchimento.idJanela) and idPreenchimento = formulario_Preenchimento.idPreenchimento), 'dd.mm.yyyy'),8)
End
else
(select nvl(to_char(strTextoSimples),''),strTextoLongo,nvl (to_char(dtData),''),nvl(to_char(intNumerico),''), nvl(to_char(crMoeda),'') from formulario_resposta where idCampo in (select idCampoKey from formulario_config where idJanela = formulario_preenchimento.idJanela) and idPreenchimento = formulario_Preenchimento.idPreenchimento)
end "strFormPreenchido",
formulario_preenchimento.intCodigo,
nvl(formulario_preenchimento.idExecByAprov,0)
FROM Janela
,FORMULARIO_Config , FORMULARIO_Preenchimento ,FORMULARIO_Status
WHERE Janela.idJanela = FORMULARIO_Config.idJanela
and Janela.idJanela = FORMULARIO_Preenchimento.idJanela
and FORMULARIO_Preenchimento.idStatus = FORMULARIO_Status.idStatus
AND FORMULARIO_Preenchimento.idStatus IN (2,3) AND (Janela.idJanela IN
(SELECT FORMULARIO_usuExecutor.idJanela
FROM FORMULARIO_usuExecutor
)
OR Janela.idJanela IN (SELECT FORMULARIO_grpExecutor.idJanela
FROM FORMULARIO_grpExecutor
WHERE idGrupo IN (SELECT idGrupo
FROM relUsuarioGrupo
)))
ORDER BY FORMULARIO_Status.idStatus DESC, FORMULARIO_Preenchimento.dtAprovacao ASC
The Oracle returns an error ora-00913 in the line "else (select
nvl(to_char(strTextoSimples)''),strTextoLongo,nvl( to_char(dtData),''),nvl(to_char(intNumerico),''),n vl(to_char(crMoeda),'') from formulario_resposta where idCampo in (select idCampoKey from formulario_config where idJanela = formulario_preenchimento.idJanela) and idPreenchimento = formulario_Preenchimento.idPreenchimento)"
-
ORA-00913 too many values
it means that your inner select should return only one value instead of 5
...
select nvl(to_char(strTextoSimples)'')
,strTextoLongo
,nvl(to_char(dtData),'')
,nvl(to_char(intNumerico),'')
,nvl(to_char(crMoeda),'')
FROM ...end "strFormPreenchido", ...
You Have To Be Happy With What You Have To Be Happy With (KC)
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|