Results 1 to 3 of 3

Thread: Query Help

  1. #1
    Join Date
    Aug 2003
    Posts
    25

    Unanswered: Query Help

    Hello there,

    I will really appreciate if someone can help me on this...

    I have a procedure (pls see attachment). This procedure reads data from logbox transaction table. If transaction_code = 'I', data will be posted on actual_in. If transaction_code = 'O' , data will be posted on actual_out.
    If transaction_code = 'I' and actual_in is not null, data will be posted on balik_overtime_in. If transaction_code = 'O' and actual_out is not null , data will be posted on balik_overtime_out.

    My problem is how can i post data on this part of procedure, where
    the actual_out is next day.



    Example:

    transaction_date transaction_time transaction_code id_number
    1. 01-Sep-03 17:01 I 871083
    2. 02-Sep-03 06:00 O 871083

    The result should look like this:

    attendance_date actual_time_in actual_time_out id_number
    1. 01-Sep-03 17:01 06:00 871083


    On the example above, logbox_transactions have two rows. it should be written in attendance_transactions_w as one row only.


    PLEASE HELP ME.........


    tia,
    ynoel
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    might need some tweaking, but show me what errors you get when you try to compile this:

    PHP Code:
    create or replace PROCEDURE upload_data IS

      CURSOR  logbox_cur  IS
             SELECT id_number

                     
    transaction_date,
                    
    to_char(transaction_time,'HH24:MI'actual_in_out,
                    
    transaction_code
             FROM   logbox_transactions
             WHERE  transaction_date BETWEEN  
    :cg$ctrl.from   AND  :cg$ctrl.to
             ORDER BY id_number
    transaction_date;

      
    logbox_rec      logbox_cur%ROWTYPE;

      
    var_actual_in   VARCHAR2(150);  
      
    var_actual_out  VARCHAR2(150); 
      
    var_balik_in    VARCHAR2(150);
      
    var_balik_out   VARCHAR2(150);


      
    var_with_logbox_validation  VARCHAR2(1)  :=  'N';
      
    var_working_time_code       VARCHAR2(10) :=  '0000000000';
      
    var_id_num                  VARCHAR2(30);
      
    var_date                      DATE;
      
    var_test                      DATE;
      
     
    CURSOR find_match IS
             SELECT actual_time_in
             FROM attendance_transactions_w
            WHERE  var_id_num 
    id_number 
            
    AND    var_date attendance_date
            
    and    pay_date is null 
            
    and    actual_time_in is not null;

    BEGIN

       
    FOR logbox_rec IN logbox_cur LOOP

          
    IF  logbox_rec.transaction_code  'I' THEN
                 var_id_num 
    := logbox_rec.id_number;
                 
    var_date   := logbox_rec.transaction_date;

                 
    var_actual_in    :=  to_char(logbox_rec.transaction_date,'DD-MON-YYYY')||' '||logbox_rec.actual_in_out
        
            
    OPEN find_match;
                
    FETCH find_match into var_test;
                if 
    find_match%found then
                
                 
              
    -- ACTUAL IN AND BALIK OVERTIME IN POSTING --


                  
    UPDATE attendance_transactions_w
                  SET    balik_overtime_in           
    to_date(var_actual_in,'DD-MON-YYYY HH24:MI')
                  
    WHERE  logbox_rec.id_number           id_number 
                  
    AND    logbox_rec.transaction_date attendance_date
                  
    and      pay_date is null 
                  
    and      actual_time_in is not null;
                    
                else
                     
                     
    INSERT INTO attendance_transactions_w 
                         
    (id_numberattendance_dateactual_time_in
                          
    with_logbox_validationworking_time_code)
                     
    VALUES
                         
    (logbox_rec.id_number
                          
    logbox_rec.transaction_date
                         
    to_date(var_actual_in,'DD-MON-YYYY HH24:MI'),
                         
    var_with_logbox_validation,
                         
    var_working_time_code);
                
    end if;
            
    CLOSE find_match;

         ELSE

            IF  
    logbox_rec.transaction_code  'O' THEN
                 var_id_num 
    := logbox_rec.id_number;
                 
    var_date   := logbox_rec.transaction_date;

                  
    var_actual_out   :=  to_char(logbox_rec.transaction_date,'DD-MON-YYYY')||' '||logbox_rec.actual_in_out;

              -- 
    ACTUAL OUT AND BALIK OVERTIME OUT POSTING --
                
    OPEN find_match;
                
    FETCH find_match into var_test;
                
                if 
    find_match%found then

                  UPDATE attendance_transactions_w
                  SET    balik_overtime_out          
    to_date(var_actual_out,'DD-MON-YYYY HH24:MI')              
                  
    WHERE  logbox_rec.id_number           id_number 
                  
    AND    logbox_rec.transaction_date attendance_date
                  
    and      pay_date is null 
                  
    and      actual_time_in is not null;

                else
                  
                     
    UPDATE  attendance_transactions_w
                     SET     actual_time_out             
    to_date(var_actual_out,'DD-MON-YYYY HH24:MI')
                       
    WHERE   logbox_rec.id_number           id_number 
                     
    AND     logbox_rec.transaction_date attendance_date
                     
    and      pay_date is null 
                     
    and      actual_time_in is not null;
                
    end if;
            
    CLOSE find_match;
                
            
    END IF;
          
    END IF;
       
    END LOOP;
       
    CLOSE   logbox_cur;
       
    COMMIT;
    END;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2003
    Posts
    25
    Hi The_Duck,

    I got this error....

    SQLWKS> DECLARE
    2>
    3> CURSOR logbox_cur IS
    4> SELECT id_number,
    5> transaction_date,
    6> to_char(transaction_time,'HH24:MI') actual_in_out,
    7> transaction_code
    8> FROM logbox_transactions
    9> WHERE transaction_date BETWEEN '14-SEP-03' AND '18-SEP-03'
    10> ORDER BY id_number, transaction_date;
    11>
    12> logbox_rec logbox_cur%ROWTYPE;
    13>
    14> var_actual_in VARCHAR2(150);
    15> var_actual_out VARCHAR2(150);
    16> var_balik_in VARCHAR2(150);
    17> var_balik_out VARCHAR2(150);
    18>
    19>
    20> var_with_logbox_validation VARCHAR2(1) := 'N';
    21> var_working_time_code VARCHAR2(10) := '0000000000';
    22> var_id_num VARCHAR2(30);
    23> var_date DATE;
    24> var_test DATE;
    25>
    26> CURSOR find_match IS
    27> SELECT actual_time_in
    28> FROM attendance_transactions_w
    29> WHERE var_id_num = id_number
    30> AND var_date = attendance_date
    31> and pay_date is null
    32> and actual_time_in is not null;
    33>
    34> BEGIN
    35>
    36> FOR logbox_rec IN logbox_cur LOOP
    37>
    38> IF logbox_rec.transaction_code = 'I' THEN
    39> var_id_num := logbox_rec.id_number;
    40> var_date := logbox_rec.transaction_date;
    41>
    42> var_actual_in := to_char(logbox_rec.transaction_date,'DD-MON-YYYY')||' '||logbox_rec.actual_in_out;
    43>
    44> OPEN find_match;
    45> FETCH find_match into var_test;
    46> if find_match%found then
    47>
    48>
    49> -- ACTUAL IN AND BALIK OVERTIME IN POSTING --
    50>
    51>
    52> UPDATE attendance_transactions_w
    53> SET balik_overtime_in = to_date(var_actual_in,'DD-MON-YYYY HH24:MI')
    54> WHERE logbox_rec.id_number = id_number
    55> AND logbox_rec.transaction_date = attendance_date
    56> and pay_date is null
    57> and actual_time_in is not null;
    58>
    59> else
    60>
    61> INSERT INTO attendance_transactions_w
    62> (id_number, attendance_date, actual_time_in,
    63> with_logbox_validation, working_time_code)
    64> VALUES
    65> (logbox_rec.id_number,
    66> logbox_rec.transaction_date,
    67> to_date(var_actual_in,'DD-MON-YYYY HH24:MI'),
    68> var_with_logbox_validation,
    69> var_working_time_code);
    70> end if;
    71> CLOSE find_match;
    72>
    73> ELSE
    74>
    75> IF logbox_rec.transaction_code = 'O' THEN
    76> var_id_num := logbox_rec.id_number;
    77> var_date := logbox_rec.transaction_date;
    78>
    79> var_actual_out := to_char(logbox_rec.transaction_date,'DD-MON-YYYY')||' '||logbox_rec.actual_in_out;
    80>
    81> -- ACTUAL OUT AND BALIK OVERTIME OUT POSTING --
    82> OPEN find_match;
    83> FETCH find_match into var_test;
    84>
    85> if find_match%found then
    86>
    87> UPDATE attendance_transactions_w
    88> SET balik_overtime_out = to_date(var_actual_out,'DD-MON-YYYY HH24:MI')
    89> WHERE logbox_rec.id_number = id_number
    90> AND logbox_rec.transaction_date = attendance_date
    91> and pay_date is null
    92> and actual_time_in is not null;
    93>
    94> else
    95>
    96> UPDATE attendance_transactions_w
    97> SET actual_time_out = to_date(var_actual_out,'DD-MON-YYYY HH24:MI')
    98> WHERE logbox_rec.id_number = id_number
    99> AND logbox_rec.transaction_date = attendance_date
    100> and pay_date is null
    101> and actual_time_in is not null;
    102> end if;
    103> CLOSE find_match;
    104>
    105> END IF;
    106> END IF;
    107> END LOOP;
    108> CLOSE logbox_cur;
    109> COMMIT;
    110> END;
    111> /
    ORA-01001: invalid cursor
    ORA-06512: at line 108
    SQLWKS>


    Can you please explain me the meaning of this.......

    ORA-01001 invalid cursor

    Cause: Either a host language program call specified an invalid cursor or the values of the AREASIZE and MAXOPENCURSORS options in the precompiler command were too small. All cursors must be opened using the OOPEN call before being referenced in any of the following calls: SQL, DESCRIBE, NAME, DEFINE, BIND, EXEC, FETCH, and CLOSE. The Logon Data Area (LDA)must be defined by using OLON or OLOGON. If the LDA is not defined, this message is issued for the following calls: OPEN, COM, CON, ROL,and LOGOFF.

    Action: Check the erroneous call statement. Specify a correct LDA area or open the cursor as required. If there is no problem with the cursor, it may benecessary to increase the AREASIZE and MAXOPENCURSORS options before precompiling.

    Do I need to change some init parameters here??? What must be the basis???

    SQLWKS> show parameter area_size
    NAME TYPE VALUE
    ----------------------------------- ------- ------------------------------
    bitmap_merge_area_size integer 1048576
    create_bitmap_area_size integer 8388608
    hash_area_size integer 0
    sort_area_size integer 65536
    SQLWKS> show parameter cursor
    NAME TYPE VALUE
    ----------------------------------- ------- ------------------------------
    close_cached_open_cursors boolean FALSE
    cursor_space_for_time boolean FALSE
    open_cursors integer 400
    row_cache_cursors integer 10
    session_cached_cursors integer 0

    Please help me......


    thanks,
    ynoel
    Last edited by ynoel; 09-18-03 at 18:20.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •