Skip to Content
avatar image
Former Member

Problem subquery

Pessoal, falo em português do brasil e venho solicitar ajuda para resolver problema com esta query:

( Guys, I speak in Portuguese in Brazil and I request help with wrong with this query: )

CREATE VIEW V_CRMSMS_EXAMEIMPRESSO

AS

SELECT

osm.osm_serie AS OSSerie,

osm.osm_num AS OSNumero,

(DATE(rcl.rcl_dthr_web) || ' ' || TIME(rcl.rcl_dthr_web)) AS OsDataImpressao,

(DATE(osm.osm_dt_result) || ' ' || TIME(osm.osm_dt_result)) AS OsDataResultado,

COUNT(pac.pac_reg) AS OsQtdeExames,

(pac.pac_reg || osm.osm_num) as OsUsuario,

pac.PAC_W_SENHA as OsSenha,

pac.pac_reg AS PacienteRegistro,

pac.pac_nome AS PacienteNome,

pac.pac_sexo AS PacienteSexo,

FLOAT((DATEDIFF(pac.pac_nasc, timestamp)/365.25), 2) AS PacienteIdade,

pac.pac_email AS PacienteEmail,

pac.PAC_CELULAR AS PacienteCelular,

pac.pac_ind_aceita_sms AS PacienteAceita,

pac.pac_cnv AS ConvenioCod,

cnv.cnv_nome AS ConvenioNome,

str.str_str_cod AS UnidadeCod,

str1.str_nome AS UnidadeNome,

emp.emp_fone AS UnidadeFone,

emp.emp_fax AS UnidadeFax

FROM

osm

INNER JOIN pac ON (osm.osm_pac = pac.pac_reg)

INNER JOIN cnv ON (pac.pac_cnv = cnv.cnv_cod)

INNER JOIN rcl ON (osm.osm_serie = rcl.rcl_osm_serie)

AND (osm.osm_num = rcl.rcl_osm)

INNER JOIN str ON (osm.osm_str = str.str_cod)

INNER JOIN str str1 ON (str.str_str_cod = str1.str_cod)

INNER JOIN emp ON (str1.str_emp_cod = emp.emp_cod)

WHERE

((pac.pac_email IS NOT NULL) OR

(pac.PAC_CELULAR IS NOT NULL)) AND

(DATE(timestamp) = (SELECT MAX(DATE(rcl.rcl_dthr)) AS RegistroImpresso FROM rcl WHERE (rcl.rcl_osm_serie = osm.osm_serie) AND (rcl.rcl_osm = osm.osm_num)))

GROUP BY

osm.osm_serie,

osm.osm_num,

(DATE(rcl.rcl_dthr_web) || ' ' || TIME(rcl.rcl_dthr_web)),

(DATE(osm.osm_dt_result) || ' ' || TIME(osm.osm_dt_result)),

CONCAT(pac.pac_reg,osm.osm_num),

pac.PAC_W_SENHA,

pac.pac_reg,

pac.pac_nome,

pac.pac_sexo,

FLOAT((DATEDIFF(pac.pac_nasc, timestamp)/365.25), 2),

pac.pac_email,

pac.PAC_CELULAR,

pac.pac_ind_aceita_sms,

pac.pac_cnv,

cnv.cnv_nome,

str.str_str_cod,

str1.str_nome,

emp.emp_fone,

emp.emp_fax

HAVING

(COUNT(pac.pac_reg) = (SELECT COUNT(rcl.rcl_pac) AS Laudos FROM rcl WHERE (rcl.rcl_osm_serie = osm.osm_serie) AND (rcl.rcl_osm = osm.osm_num) AND (rcl.rcl_stat = 'I')))

O problema está na subquery do HAVING, gostaria de saber como faço para tirar essa subquery e se possível, resolver o problema com uma procedure ou variáveis?

( The problem lies in the Subquery HAVING, I wonder how do I kill Subqueries this and if possible, resolve the problem with a procedure or variable? )

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Oct 22, 2008 at 08:31 PM

    Hi Marcel,

    please change the table aliases in the HAVING subquery to something else than what you've used in the rest of the query. Does it work then?

    In general HAVING accepts subqueries in MaxDB (otherwise this clause would be pretty useless, would it?).

    If this does not help, please post the create DDLs for the tables/indexes so that we can reproduce the issue.

    regards,

    Lars

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Lars Breddemann

      Hi Lars,

      Compreendi todas as informações passadas por você.

      Agradeço muito todo o apoio dispensado na tentativa de encontrar uma solução para este problema, mas sei que a modelagem tem algumas deficiências, o que dificulta também para mim, por não ser o desenvolvedor ou administrador do banco.

      Passarei o link do tópico para o administrador responsável, assim como as suas dicas, e pedirei para que tente implementar alguma delas.

      Mais uma vez, muito obrigado!

      Atenciosamente,

      Marcel Cerqueira

      (+Hi Lars,

      Understand all the information passed by you.

      I very much appreciate all the support required in an attempt to find a solution to this problem, but I know that modeling is not the best, which also complicates my life, because to understand and live, even without being the developer or administrator of the database.

      Let's link the topic to the administrator as well as their tips, and ask to try to implement any of them.

      Again, thank you!

      Regards,

      Marcel Cerqueira+)

  • avatar image
    Former Member
    Oct 22, 2008 at 07:57 AM

    Hi,

    can you tell us the MaxDb Version you are using.

    Regards, Christiane

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      ( <i>SELECT MAJORVERSION, MINORVERSION, CORRECTIONLEVEL, BUILD, OPERATINGSYSTEM, KERNELVARIANT FROM SYSINFO.VERSION</i> )

      MAJORVERSION: 7

      MINORVERSION: 6

      CORRECTIONLEVEL: 0

      BUILD: 27

      OPERATINGSYSTEM: Linux 2.6.18-8.el5

      KERNELVARIANT: fast