Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT * FROM (
- SELECT
- vwd.*,
- dmst.dmdast_status,
- CASE
- WHEN (vwd.aluno_cod IS NULL AND (dmst.dmdast_status = 1 OR dmst.dmdast_status = 3)) THEN 1
- ELSE 0
- END AS na_demanda,
- CASE
- WHEN (vwd.aluno_cod IS NULL AND (dmst.dmdast_status = 8 OR dmst.dmdast_status = 4)) THEN 1
- ELSE 0
- END AS cancelado
- FROM (
- SELECT
- pes.pes_cod,
- pes.pes_nome,
- pes.pes_nomemae,
- pes.pes_dtnasc,
- pes.pes_certnc_numero,
- a.aluno_pescod,
- a.aluno_cod,
- a.aluno_ra,
- (
- SELECT MAX(d.dmda_cod) FROM edu_demanda d
- WHERE 1=1
- AND d.dmda_pescod = pes.pes_cod
- ) AS dmda_cod,
- (
- SELECT MAX(m.matric_cod) FROM edu_matricula m
- INNER JOIN edu_classe c
- ON c.classe_cod = m.matric_classecod
- WHERE 1=1
- AND m.matric_alunocod = a.aluno_cod
- ) AS matric_cod,
- (
- SELECT resp.edresp_cod
- FROM edu_responsavel resp
- WHERE 1=1
- AND resp.edresp_pescod = pes.pes_cod
- ) AS edresp_cod,
- (
- SELECT fn.fun_cod
- FROM rh_funcionario fn
- WHERE 1=1
- AND fn.fun_pescod = pes.pes_cod
- ) AS fun_cod,
- CASE
- WHEN ( primeiro_nome(TRIM(pes.pes_nome),1) = upper( primeiro_nome(TRIM('MARIA CECILIA FERNANDES GALLIZARE'),1) ) ) THEN 1
- ELSE 0
- END AS nome_coincide,
- CASE
- WHEN ( pes.pes_dtnasc = '2018-05-14' ) THEN 1
- ELSE 0
- END AS nasc_coincide,
- CASE
- WHEN ( remove_acento(TRIM(pes.pes_nomemae)) = upper( remove_acento(TRIM('ROGÉRIA FERNANDES GALIZARE') ) ) ) THEN 1
- ELSE 0
- END AS nome_mae_coincide
- FROM bas_pessoa pes
- LEFT JOIN edu_aluno a
- ON a.aluno_pescod = pes.pes_cod
- WHERE 1=1
- AND (
- (
- primeiro_nome(TRIM(pes.pes_nome),1) = primeiro_nome(TRIM('MARIA CECILIA FERNANDES GALLIZARE'),1)
- AND pes.pes_dtnasc = '2018-05-14'
- )
- OR
- (
- primeiro_nome(TRIM(pes.pes_nome),1) = primeiro_nome(TRIM('MARIA CECILIA FERNANDES GALLIZARE'),1)
- AND remove_acento(TRIM(pes.pes_nomemae)) = remove_acento(TRIM('ROGÉRIA FERNANDES GALIZARE'))
- )
- )
- ) AS vwd
- --dados da demanda
- LEFT JOIN edu_demanda d
- ON vwd.dmda_cod = d.dmda_cod
- LEFT JOIN edu_demanda_status dmst
- ON dmst.dmdast_dmdacod = d.dmda_cod
- AND dmst.dmdast_ativo = 1
- ) AS vw
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement