Results 1 to 14 of 14
  1. #1
    Join Date
    May 2004
    Posts
    105

    Unanswered: IF EXISTS statement in my perl program

    I am having trouble finishing my query.

    This is what I have:

    IF EXISTS(Select ApplicationID from Application Where Application = '&_')
    Insert Into PCApp(ApplicationID, SystemNetName)
    Values( , $HoH->{Host}{SystemNetName})

    I am not sure what to put in the blank within the Values parenthesis. I need to obtain the ApplicationID that is checked in the IF EXISTS section. But I cannot put a select statement into the Values() section.

    Any suggestions would be appreciated.

    Thanks,
    Laura

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use a SELECT statement instead of the VALUES clause.

    -PatP

  3. #3
    Join Date
    May 2004
    Posts
    105
    And using a select statement will still insert the values into the table?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In an INSERT statement, you can use the VALUES clause for a list of constants, or a SELECT clause for a list of expressions. The SELECT can include multiple rows and/or contain UNION operators to create multiple row inserts using just one INSERT statement. The SELECT buys you the ability to use expressions (including function calls), generate multiple rows, etc.

    -PatP

  5. #5
    Join Date
    May 2004
    Posts
    105
    Ok I think I got it. Off the subject, can the IF...ELSE Contain an embedded IF...ELSE? Is it ok to have two inserts with the if section?

    For example,

    IF NOT EXISTS (Select ApplicationID from Application Where (Application = '$_' ))
    INSERT INTO Application(Application)
    Values('$_')

    INSERT INTO PCApp(ApplicationID, SystemNetName)
    SELECT Application.ApplicationID, Host.SystemNetName
    FROM Application CROSS JOIN Host
    WHERE (Application.Application = '$_' AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')

    ELSE
    INSERT INTO PCApp(ApplicationID, SystemNetName)
    SELECT Application.ApplicationID, Host.SystemNetName
    FROM Application CROSS JOIN Host
    WHERE (Application.Application = '$_' AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')";


    Thanks for your help.
    -Laura
    Last edited by lauramccord; 08-23-04 at 13:06.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think what you meant was:
    Code:
    IF NOT EXISTS (Select ApplicationID
       FROM Application Where (Application = '$_' ))
       BEGIN
          INSERT INTO Application(Application)
             Values('$_')
    
          INSERT INTO PCApp(ApplicationID, SystemNetName)
             SELECT Application.ApplicationID, Host.SystemNetName
                FROM Application CROSS JOIN Host
                WHERE (Application.Application = '$_'
                   AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')
       END
    ELSE 
       INSERT INTO PCApp(ApplicationID, SystemNetName)
          SELECT Application.ApplicationID, Host.SystemNetName
             FROM Application CROSS JOIN Host
             WHERE (Application.Application = '$_'
                AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')";
    Note the addition of the BEGIN...END (in red) to your code.

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    CROSS JOIN?

    And what's with the double quote on the end?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, so the cross join isn't the way that I'd approach it, but it would work... Kind of like the way you had to construct joins using the pre-SQL-89 syntax. Ugly, but adequate to the job!

    The double quote hanging off of the end is actually because Laura is taking this SQL out of the middle of her Perl code. It isn't really part of the SQL syntax at all.

    -PatP

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Really...OK

    Laura...start writting stored procedures and execute them instead.....

    Never did learn Pearl....though we did use it for an Oracle project once....

    I gotta find a Rexx interpreter for Windoze.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    May 2004
    Posts
    105
    Originally, I used a left join but when I tested my query in sql server's enterprise manager it automatically changed it to cross join.

    The begin and end worked. Thanks for the help.

    -Laura

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    I gotta find a Rexx interpreter for Windoze.....
    I've never tried it, but I've heard that Reginald isn't too bad.

    -PatP

  12. #12
    Join Date
    May 2004
    Posts
    105
    Yeah, I am pretty new at SQL Server. Just started learning it last month because I am going to be the database administrator. So, I have not begun using stored procedures yet, but I will.

    Thanks,
    Laura

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hold the phone...Enterprise Manager?

    Do you mean Query Analyzer?

    And Pat.....RxSocks.....Not that I'll find a practical application for it...(Well maybe I could replace DTS), but I bet I can get it to talk to SQL Server....


    Very cool

    Thanks
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    May 2004
    Posts
    105
    Enterprise Manager -> Opened the table view to see the records that were stored -> on the top toolbar there is an sql button that I tested my statements. It probably isn't the best way, but I wanted to have quick access to my table design and query results.

    I am aware of Query Analyzer but I have not used it much.

    -Laura

Posting Permissions

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