Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007

    Unanswered: Question regarding logical operators, and view creation.

    I am trying to write a program that retrieves a table’s records based on a list of criteria in a text file. Additionally, I would like to generate a view such that the data retrieved can later be accessed without the text file.

    For example, lets say I have the following list in a text file:


    And a 2-field table called ‘Employees’ that looks like:

    1, Kevin
    2, John
    3, Jim
    4, Karen
    5, Steve
    6, Susan

    Currently, while my program reads EMPLOYEE_ID from the text file, it generates a select statement that retrieves from the Employees table the name at that particular EMPLOYEE_ID, and appends the result to a temporary table . The reason I am creating a select statement for each EMPLOYEE_ID and not creating a single select statement with a logical IN (i.e. select name from employees where employee_id in (1, 3, 5)), is because the IN operator is limited to a maximum number of arguments (1000, I believe). So, using IN wouldn’t work for any file that contained more than the maximum number of criteria allowed.

    So, given that I can’t use IN, my major concern is how to go about generating a view from all the individual SQL statements. Should I instead create a single select statement and append an AND conditional for each record in my text file (i.e select name from employees where employee_id = 1 AND employee_id = 3 AND employee_id = 5)? Are select statements limited to a maximum number of logical operators?

    Please excuse me if my approach seems fundamentally ass-backwards. I haven’t been doing this for very long. Any advice would be greatly appreciated.
    Last edited by Narwe; 07-13-07 at 13:13.

  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    I guess you could try defining your input file as an external table and then join the two. In that case you won't need to generate a view each time as the query would never change.
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Yeah, I thought about doing something like that. I've been trying to avoid having to retain the text files, but if a cleaner solution can't be arrived at I might not have a choice.

  4. #4
    Join Date
    May 2004
    Dominican Republic
    If you dont want to retain the files, just create a new table (could be a global temporary table) once you set up your external table to access the file, so you can store the data there temporarily.

Posting Permissions

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