Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Check for existence of records

    I have a simple question. I have a PL/SQL script that I am trying to run based on the existance of a row in another table. This other table will either have a row for a given location or not. If it does exist, the approved_flag will either be Y or N. I need a query that will run the script if to row either doesn't exist, or if it does, is approved. My code here does not get executed if the row does not exist and it should. Ideas???

    Code:
    SELECT COUNT (*)
    INTO   v_count
    FROM   pac.DELETEME_CONTROL
     WHERE dealer = '&1' 
       AND process_flag  = 'N'  -- has not been processed yet 
       AND ok_to_process = 'Y'; -- has manager approval
    
    IF v_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE ('Location '||'&1'||' has not been approved for deletion. Now exiting');
    ELSE
        DBMS_OUTPUT.PUT_LINE ('Location '||'&1'||' has been approved for deletion. Starting deleting DELETEME DFUs');   
        run the rest of the code...
    END IF;

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you can't use ampersam variable in PL/SQL.
    Use standard variable declaration (like you do with v_count) and pass in through normal process.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    Thanks for the advice, but I still am unsure how the query should be written.

  4. #4
    Join Date
    Sep 2005
    Posts
    220
    Thanks for the advice. I meant I am using SQLPlus. The query as it stands will give me a count if a row is in the table but I need it to return a 1 if the row does not exist.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something like this:
    Code:
    /* Formatted on 17/05/2007 21:33 (Formatter Plus v4.8.7) */
    CREATE OR REPLACE PROCEDURE prc_approve (
       par_dealer   IN   deleteme_control.dealer%TYPE
    )
    IS
       v_count   NUMBER;
    BEGIN
       SELECT COUNT (*)
         INTO v_count
         FROM pac.deleteme_control
        WHERE dealer = par_dealer
          AND process_flag = 'N'                     -- has not been processed yet
          AND ok_to_process = 'Y';                         -- has manager approval
    
       IF v_count = 0
       THEN
          DBMS_OUTPUT.put_line
                              (   'Location '
                               || '&1'
                               || ' has not been approved for deletion. Now exiting'
                              );
       ELSE
          DBMS_OUTPUT.put_line
              (   'Location '
               || '&1'
               || ' has been approved for deletion. Starting deleting DELETEME DFUs'
              );
       --RUN the rest OF the code...
       END IF;
    END;

  6. #6
    Join Date
    Sep 2005
    Posts
    220
    I just ran it for a location number that I know is not in the table and I got "Location R080 has not been approved for deletion. Now exiting". It should move on and run the rest of the code.

  7. #7
    Join Date
    Sep 2005
    Posts
    220
    Let me clarify. These are the conditions when I want to run the other code:

    1) the location exists in the table and is approved
    2) the location does not exist in the table
    Last edited by ssmith001; 05-17-07 at 18:56.

Posting Permissions

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