Add a line

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 01 Jul 2008 3:15 pm

personal.. good afternoon I have the seuginte problem I am using the following select statement
Code: Select all
select y.nome
     , horas_prev
     , horas_trab
     , saldo
     , TOTAL
  from (select x.nome
             , rpad(pk_hora.fkg_masc_hora_hora(pk_hora.fkg_min_hora(x.hrs_prev)), 10) horas_prev
             , rpad(pk_hora.fkg_masc_hora_hora(pk_hora.fkg_min_hora(x.hrs_trab)), 10) horas_trab
             , rpad(pk_hora.fkg_masc_hora_hora(pk_hora.fkg_min_hora((hrs_trab - hrs_prev))), 10) saldo
             , rpad(decode(lead(x.nome, 1) over (order by nome), null, sum(hrs_trab) over (order by nome)), 10) TOTAL
             , decode(lead(x.nome, 1) over (order by nome), null, 'F') final
          from (select us.nome
                     , pk_soff9082.fkg_horas_prev_period(to_date('01/05/2008', 'dd/mm/yyyy'), to_date('31/05/2008', 'dd/mm/yyyy')) hrs_prev
                     , sum(hr_fim - hr_ini) hrs_trab
                  from sf_apont_diario sa
                     , usu us
                 where us.usu_id = sa.usu_id
                   and sa.usu_id in (395, 226)
                   and dia >= to_date('01/05/2008', 'dd/mm/yyyy')
                   and dia <= to_date('31/05/2008', 'dd/mm/yyyy')
                 group by us.nome) x
                 ) y
and returns this
Code: Select all
NOME                                                         HORAS_PREV HORAS_TRAB SALDO      TOTAL
------------------------------------------------------------ ---------- ---------- ---------- ----------
DESENVCNBR                                                   170:00     170:00     0:00
LUCAS SAMPAIO DE SOUZA                                       170:00     169:14     -0:46      20354
what I want actually is that the value of the total column appear in the column balance ... more below. got it??
the result would be this way
Code: Select all
NOME                                                         HORAS_PREV HORAS_TRAB SALDO
------------------------------------------------------------ ---------- ---------- ----------
DESENVCNBR                                                   170:00     170:00     0:00
LUCAS SAMPAIO DE SOUZA                                       170:00     169:14     -0:46
                                                                                   ---------
                                                                                   20354
anyone know??

Thank you...
gokden
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Poston Tue, 01 Jul 2008 4:42 pm

aoww .... it worked did a UNION ALL = D vlwww
gokden
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Poston Thu, 10 Jul 2008 10:04 am

Guys ... now has another problem .... anyone know if I have to do that anymore without the UNION ALL?
because otherwise my query will stay mtooooooooooooooooooooo great and will use the Bank's resource muito ... = D thanks ...
gokden
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Poston Thu, 10 Jul 2008 10:50 am

I wonder if it's not worth adding it in the application?
(as are few lines, maybe worth keeping in the variable sum go) ... is an idea ...
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 10 Jul 2008 11:56 am

ahem .... more é Thu I don't like uses the reports = X when ever I move him dá something wrong ... então I try to put everything I can into a select só Chi hehehehehe .... more like qui qui do this will be easier even =/Thurs é stirring in the reports = D they're rather more lines ... I'll put a sum in the lines and já was same ... muito thanks ...
gokden
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas



Return to SQL

Who is online

Users browsing this forum: No registered users and 4 guests