1 Reply Latest reply: Apr 15, 2013 8:10 PM by rp0428 RSS

    Presenting the same record over a field with a value

    1003087
      Hello everybody,

      I need to perform a query that returns me all phones and types registered on the same record, but if I have more than one phone registered it presents more of a logline. For example, if two phones have registered, a commercial and other residential, will be presented two records of the same person, and the person must submit two phones with the same record. Attention should be paid also if no phone, since the record must be presented in the same way.

      The type fields and telephone are respectively T.TEA_ST_TIPO, T.TEA_ST_TELEFONE.

      The following code
       SELECT A.AGN_ST_NOME AGN_ST_NOME
                , A.AGN_CH_TIPOPESSOAFJ AGN_CH_TIPOPESSOAFJ
                , A.AGN_ST_EMAIL AGN_ST_EMAIL
                , A.AGN_CH_TIPOINSCRICAO
                , A.CNAE_ST_CODIGO
                , A.AGN_ST_CGC
                , A.AGN_IN_NATJURID
                , A.AGN_CH_ENQUADRAMENTO
                , A.AGN_ST_CEI
                , A.AGN_ST_INSCRESTADUAL
                , A.AGN_ST_INSCRMUNIC
                , A.AGN_ST_URL
                , A.PA_ST_SIGLA
                , A.UF_ST_SIGLA
                , A.AGN_ST_CEP
                , A.MUN_IN_CODIGO
                , A.TPL_ST_SIGLA
                , A.AGN_ST_LOGRADOURO
                , A.AGN_ST_NUMERO
                , A.AGN_ST_COMPLEMENTO
                , A.AGN_ST_REFERENCIA
                , A.AGN_ST_BAIRRO
                , A.AGN_ST_CEPCXPOSTAL
                , A.AGN_ST_CXPOSTAL
                , T.TEA_ST_TIPO
                , T.TEA_ST_TELEFONE
      FROM A MGGLO.GLO_AGENTES
          , I MGGLO.GLO_AGENTES_ID
          , C MGGLO.GLO_CLIENTE
          , P MGGLO.GLO_PESSOA_FISICA
          , T MGGLO.GLO_TEL_AGENTES
        WHERE I.AGN_TAU_ST_CODIGO = 'C'
                      AND I.AGN_TAB_IN_CODIGO = A.AGN_TAB_IN_CODIGO
                      AND I.AGN_PAD_IN_CODIGO = A.AGN_PAD_IN_CODIGO
                      AND I.AGN_IN_CODIGO = A.AGN_IN_CODIGO
                      AND I.AGN_TAB_IN_CODIGO C.AGN_TAB_IN_CODIGO = (+)
                      AND I.AGN_PAD_IN_CODIGO C.AGN_PAD_IN_CODIGO = (+)
                      AND I.AGN_IN_CODIGO C.AGN_IN_CODIGO = (+)
                      AND I.AGN_TAU_ST_CODIGO C.AGN_TAU_ST_CODIGO = (+)
                      AND A.AGN_TAB_IN_CODIGO P.AGN_TAB_IN_CODIGO = (+)
                      AND A.AGN_PAD_IN_CODIGO P.AGN_PAD_IN_CODIGO = (+)
                      AND A.AGN_IN_CODIGO P.AGN_IN_CODIGO = (+)
                      P.AGN_CH_TIPO AND (+) = 'P'
                      AND A.AGN_CH_TIPOPESSOAFJ = 'J'
                      AND T.AGN_TAB_IN_CODIGO = A.AGN_TAB_IN_CODIGO
                      AND T.AGN_PAD_IN_CODIGO = A.AGN_PAD_IN_CODIGO
                      AND T.AGN_IN_CODIGO A.AGN_IN_CODIGO = [/ code]
      
      Does anyone know how to tell me realize this query?
      
      Thank you.