• 0
Sign in to follow this  
Mariene Didio

Ajuda com Select

Question

Preciso de uma ajuda fiz este sql mas preciso que o que estou selecionando como nome, cpf , etc  retornem em colunas, alguém pode me ajudar?

 

SELECT                                                  '0001' ||''||
                                                  rpad(coalesce(substr(z01_nome,1,35),''),35,' ') ||''||
                                                             rpad(coalesce(z01_cgccpf,''),11,'0') ||''||
   rpad(substr(trim(coalesce(z01_ender,''))||','|| coalesce(z01_numero::char(4),''),1,35),35,' ') ||''||
                                                                 rpad(coalesce(z01_cep,''),8,' ') ||''||
                                                rpad(substr(coalesce(z01_bairro,''),1,20),20,' ') ||''||
                                                              rpad(coalesce(z01_telef,''),10,' ') ||''||
                                                                                          'Ramal' ||''||
                                               rpad(coalesce(to_char(rh01_nasc,'DDMMYYYY'),''),8) ||''||
                                                                                        rh01_sexo ||''||
                                                 coalesce(CASE rh01_estciv
                                                                     WHEN 1 THEN '6'
                                                                     WHEN 2 THEN '1'
                                                                     WHEN 3 THEN '4'
                                                                     WHEN 4 THEN '3'
                                                                     WHEN 5 THEN '2'
                                                              END,'') ||''||
                                                             
                                                             
                                                           CASE
                                                                WHEN rh01_estciv = 2 THEN 1
                                                              ELSE 4
                                                                         END ||''||
                                                           rpad(coalesce(rh01_natura,''),25,' ') ||''||
                                                  rpad(substr(coalesce(z01_pai,''),1,35),35,' ') ||''||             
                                                  rpad(substr(coalesce(z01_mae,''),1,35),35,' ') ||''||             
                                                             rpad(coalesce(z01_ident,''),10,'0') ||''||             
                                                                                         'SSPRS' ||''||  
                                              rpad(coalesce(to_char(z01_identdtexp,'DDMMYYYY'),''),10) ||''||
                                             
                                                                                             ' ' ||''||
                                      lpad(translate(trim(coalesce(provento,'')),'.',''),15,'0') ||''||
                                          to_char(coalesce(rh01_admiss,'01-01-1999'),'DDMMYYYY') ||''||
                                                     rpad(substr(coalesce(conj,''),1,35),35,' ') ||''||
                                                                                             ' ' ||''||
                                                 rpad(coalesce(substr(z01_nome,1,19),''),19,' ') ||''||
                                                                                          '0110' ||''||
                                                                                     rh01_regist ||''||
                                                 CASE rh30_regime
                                                    WHEN 1 THEN 'Estatutario'
                                                    WHEN 2 THEN 'CLT'
                                                    WHEN 3 THEN 'ExtraQuadro'
                                                                                             end ||''||
                                                                                   nomeinstabrev
                                           as coluna
                                 FROM rhpessoal
                                   inner join cgm on rh01_numcgm = z01_numcgm
                                   inner join rhpessoalmov on rh02_anousu = 2017
                                                          and rh02_mesusu = 10
                                                          and rh02_regist = rh01_regist
                                   left join rhpesrescisao on rh05_seqpes = rh02_seqpes
                                   inner join rhlota on r70_codigo = rh02_lota
                                                    and r70_instit = rh02_instit
                                    inner join rhfuncao on rh01_funcao = rh37_funcao
                                                       and rh37_instit = rh02_instit
                                    inner join rhinstrucao on rh01_instru = rh21_instru
                                   inner join rhestcivil on rh01_estciv = rh08_estciv
                                   left join rhpesdoc on rh16_regist = rh01_regist
                                   left join rhpespadrao on rh02_seqpes = rh03_seqpes
                                   inner join rhregime on rh30_codreg = rh02_codreg
                                    and rh30_instit = rh02_instit
                                    inner join db_config on codigo = rh02_instit
                                    left join rhpesbanco on rh44_seqpes = rh02_seqpes
                                    left join (select rh31_regist, max(case when rh31_gparen = 'P' then rh31_nome else '' end) as pai, max(case when rh31_gparen = 'M' then rh31_nome else '' end) as mae, max(case when rh31_gparen = 'C' then rh31_nome else '' end) as conj from rhdepend where rh31_gparen in ('P','M','C') group by rh31_regist) as dep on dep.rh31_regist = rh01_regist
                                   left join (select r14_regist, to_char(sum(case when r14_pd = 1 then r14_valor else 0 end ),'99999999.99') AS provento
                                                                   FROM gerfsal
                                                                   INNER JOIN rhrubricas ON rh27_rubric = r14_rubric
                                                                   AND rh27_instit = 1
                                                                   WHERE r14_anousu = 2017
                                                                     AND r14_mesusu = 10
                                                                     AND r14_pd != 3
                                                                   GROUP BY r14_regist) AS xxx ON xxx.r14_regist = rhpessoalmov.rh02_regist
                                   WHERE rh05_seqpes IS NULL
                                   ORDER BY z01_nome

Share this post


Link to post
Share on other sites

0 answers to this question

Recommended Posts

There have been no answers to this question yet

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this