Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: select count(*) in IIf doesn't work

    Why doesn't:

    INSERT INTO StatusList ( DatabaseName, TableName, ImportStatus )
    VALUES ( "db1", "table1", IIf((SELECT COUNT(*) FROM table1) < 1,"Failed","Ok"));

    Work?

    I have several databases that imports several tabels on a weekly basis and I'm constructing a macro to import all the tables at once.
    I have one database with links to all tables that the data will be imported to.

    I want the question above to update the table "StatusList" with the databasename, tablename and if the import was successfull. If there are zero rows in the table the import failed.

    I get a:

    "Reserved error (-3025); there is no message for this error"

    When i try to run the question, why?

    I can always do it in VB nut I'm sure it's doable in SQL.

  2. #2
    Join Date
    Jul 2003
    Posts
    123
    Anyone?

  3. #3
    Join Date
    Aug 2003
    Posts
    23
    Maybe it is possible, but I can't manage to use SELECT COUNT(*) in a Values list.

    Not an elegant solution, but I think it will work.

    INSERT INTO StatusList ( DatabaseName, TableName, ImportStatus )
    SELECT "db1", "table1", IIf((SELECT COUNT(*) FROM table1)<1,"Failed","Ok")
    FROM sometable
    GROUP BY "db1", "table1";

    someTable could be any table, a dummy table or StatusList or whatsoever

    Group By to avoid more than one record. Otherwisw you get as many record as ther is in the table.

    hth

    /Svein Erik

Posting Permissions

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