1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Use SQL query to populate a value to pass into a function...

Discussion in 'SQL PL/SQL' started by pointspreadpros, Dec 18, 2013.

  1. pointspreadpros

    pointspreadpros Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    I am not allowed to use any pl/sql variables to accomplish my task, so I was hoping I could use a simple SELECT statement to bring back a value
    and pass it into a function:

    Is it possible to do the following?:

    myFunction(1,0,Select PFI_Mistake from match_mistakes)

    Thanks in advance.
    -Jim
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not that way, but this should work:


    select myFunction(1,0,PFI_Mistake) from match_mistakes;
     
  3. pointspreadpros

    pointspreadpros Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Thanks David, but what if I have more than 1 select statement that I need to use to pass in a variable:
    such as:

    Select myFunction(1,0,Select Col1 from MATCH_PROCESS_COUNT,Select Col2 from match_mistakes) is that possible?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, that's not the way this works. You don't put SELECT statements in variable locations in a function call. This is where you'd use a join condition, as the example below illustrates:


    Code (SQL):

    SELECT myFunction(1,0,mpc.col1, mm.col2)
    FROM match_process_count mmc JOIN match_mistakes mm ON (mm.col3 = mpc.col3);
     

    You need a proper join so you avoid generating Cartesian result sets. If you can't make a proper join then you will need PL/SQL to complete this.
     
  5. pointspreadpros

    pointspreadpros Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Got it, so I need to create a key on all the tables I will be retrieving a value from, and then join them using that key.

    Fantastic, thanks so much for the help and the quick response!
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It doesn't NEED to be a key, but a pk/fk relationship would be ideal.
     
    pointspreadpros likes this.