Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Evaluation Help me!!

  1. #1

    Default Evaluation Help me!!

    Hi, my name is neldy

    Sorry for my bad English.

    I am trying to realize the following procedure in Spoon:

    CREATE OR REPLACE PROCEDURE hm_prc IS
    tmpVar NUMBER;
    v_id_medico medicos_dim.id_medico%type;
    v_id_fecha fechas_dim.id_fecha%type;
    v_id_servicio servicios_dim.id_servicio%type;
    v_dia varchar2 (10);
    y number (8);
    z number (8);
    f date;
    f2 date;
    cursor c_horarios_medicos is
    select *
    from horario_medico;
    BEGIN

    for h in c_horarios_medicos loop
    -----

    select id_medico
    into v_id_medico
    from medicos_dim
    where cod_medico = h.cod_medico;

    -----
    begin
    select id_servicio
    into v_id_servicio
    from servicios_dim
    where cod_servicio = h.cod_servicio;

    exception
    when no_data_found then
    v_id_servicio:=471;
    end;
    -----
    f2:= to_date('31/12/2015', 'dd/mm/yyyy');
    z := h.vig_hasta - f2;

    if z > 0 then
    y := f2 - h.vig_desde;
    else
    y:= h.vig_hasta - h.vig_desde;
    end if;

    f := h.vig_desde;
    ----
    for x in 1..y loop
    ------
    v_dia := rtrim(upper(to_char(f,'day')));
    if h.dia = v_dia then
    --

    select id_fecha
    into v_id_fecha
    from fechas_dim
    where fecha = f;

    ----
    select count(*)
    into tmpVar
    from horarios_medicos_dim
    where id_medico = v_id_medico
    and id_servicio = v_id_servicio
    and id_fecha = v_id_fecha
    and turno = h.turno;

    if tmpVar=0 then
    insert into horarios_medicos_dim (id_medico,id_servicio,id_fecha,turno,hora_inicio,hora_final,consultorio,cupos,tiempo_consulta)
    values (3,v_id_servicio,v_id_fecha,h.turno,h.hora_inicio,h.hora_final,h.consultorio2,h.total_cupo,h.tiempo_consulta);
    else
    update horarios_medicos_dim set
    hora_inicio = h.hora_inicio,
    hora_final = h.hora_final,
    consultorio = h.consultorio2,
    cupos = h.total_cupo,
    tiempo_consulta = h.tiempo_consulta
    where id_medico = v_id_medico
    and id_servicio = v_id_servicio
    and id_fecha = v_id_fecha
    and turno = h.turno;
    end if;
    ----
    end if;
    ---------
    f:=f+1;
    end loop;
    commit;
    end loop;
    commit;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    Null;
    WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
    RAISE;
    END hm_prc;
    /

    somebody can help me to realize the second loop (for x in 1..y loop, which is in bold), I tried with Evaluation Element, but display the following error:
    2007/12/10 10:00:28 - JavaScript 1 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : Couldn't compile javascript: TypeError: Cannot read property "getInteger" from undefined (<cmd>; line 1)
    2007/12/10 10:00:28 - Job 1 - Finished jobentry [JavaScript 1] (result=false)

    HELP ME PLEASE!!

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    I doubt you will get help on this forum like that, we're not in the habit of doing your work ... you put some partial Oracle PL-SQL in your mail and a javascript error, but not what you did so far.

    And you'd probably also better make the example smaller.

    Regards,
    Sven

  3. #3

    Default Re: Evaluation Help me!!

    Hi, Sven

    what i want is:

    given the following code:

    for i in 1..10 loop
    select id_service
    into v_id_service
    from service

    for x in 1..y loop
    select id_date
    into v_id_date
    from date_dim
    where date = f;
    end loop;
    end loop;

    how to implement for each input in 'i' iteration (first for), to process all 'y' iterations (second for) with "Evaluation element"?

    could it to do this with "Evaluation element" or there is another way?

    thanks very much,
    Sven

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    If you want to process stuff for every row in a result look at the "process all tables" example under the sample directory.

    But your code doesn't make sense:

    - "for i in 1..10 loop" ... loop 10 times where i is not used further in the loop?

    - "for x in 1..y loop" ... what's y never gets used anymore

    - "date = f;" ... don't find f.

    Regards,
    Sven

  5. #5

    Default

    yes your're right, but the example was fictitious. really the code is like this:

    cursor h is
    select *
    from doctor_schedule;

    for x in 1..h loop
    select id_doctor (*)
    into var_id_doctor
    from Doctor_Dim
    where cod_doctor = h.cod_doctor;

    j:= h.end_date - h.start_date;
    for y in 1..j loop
    select id_date (**)
    into var_id_date
    from Date_Dim
    where date=h.date;
    end loop;
    end loop;

    what i really to want is: how to select all id_date (**) for each id_doctor (*) ?.

    Note: '*' and '**' aren't part of code

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    1 big SQL query

  7. #7

    Default Re: Evaluation Help me!!

    I need independence of the database (without sql query and DB Call Procedurem item)... so I need to do it with other Spoon items...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.