Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2002
    Location
    Iceland
    Posts
    9

    Red face Unanswered: MyODBC - Union workaround anybody ??

    Hello
    Im trying to merge ( by using UNION ) two select statements into one result ( and since MySQL 3.23.32 does not support UNION and I cannot upgrade my MySQL server I need a different solution ).

    Im using MyODBC ( tried both v.2.50.39 and v.3.51.05 ) to connect to the MySQL DB throu Access 2002 ( linked tables )

    Ive tried using the following SQL Statement :

    SELECT IFNULL(tblJobList2.JobNumber,tblnonFDPSJobList.Job Number) AS JobNumber
    FROM tblDummyTable AS temp
    LEFT JOIN tblJobList2 ON ( temp.DummyField = 0 )
    LEFT JOIN tblnonFDPSJobList ON ( temp.DummyField = 1 )
    WHERE temp.DummyField < 2 AND IFNULL(tblJobList2.JobNumber,tblnonFDPSJobList.Job Number) IS NOT NULL
    ORDER BY JobNumber

    But I get the error message "Syntax error (missing operator) in query expression '( temp.DummyField = 0 )
    LEFT JOIN tblnonFDPSJobList ON ( temp.DummyField = 1 )'

    ( see the following web page to explain how this bypasses the UNION problem http://codewalkers.com/tutorials.php?show=33&page=1 )


    This SQL statement works just fine when run from the MySQL Client and does what it is suppost to do.

    Anybody have an idea how to do a UNION with MYODBC and Access or some workaround ? plz ?!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm guessing that access wants you to parenthesize the multiple joins according to its syntax rules

    you have:

    FROM tblDummyTable AS temp
    LEFT JOIN tblJobList2 ON ( temp.DummyField = 0 )
    LEFT JOIN tblnonFDPSJobList ON ( temp.DummyField = 1 )

    you can remove those parentheses, as they are superfluous and can cause confusion in understanding later

    now parenthesize the microsoft access way:

    FROM tblDummyTable AS temp
    LEFT JOIN ( tblJobList2
    LEFT JOIN tblnonFDPSJobList
    ON temp.DummyField = 1 )
    ON temp.DummyField = 0

    by the way, that "union via a dummy table" is a pretty nifty trick

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Dec 2002
    Location
    Iceland
    Posts
    9
    Nope that isnt it ....
    I tried every posible way but no success
    I think this is a MyODBC problem ?

  4. #4
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Red face MyODBC problem ?

    Originally posted by arnar
    Nope that isnt it ....
    I tried every posible way but no success
    I think this is a MyODBC problem ?
    Nope. Tried it with an ODBC connection (MyODBC 3.51) and it works fine. So we're left with Access...

  5. #5
    Join Date
    Dec 2002
    Location
    Iceland
    Posts
    9
    exactly how did you try it... ive been trying different variants and getting no luck... what access are you using ? ( and why doesnt IFNULL work with access )

    (one pretty frustrated)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    IFNULL doesn't work with access because it's not an access function

    sorry, i missed that the first time around

    change

    IFNULL(tblJobList2.JobNumber,tblnonFDPSJobList.Job Number)

    to

    IIF(ISNULL(tblJobList2.JobNumber),tblnonFDPSJobLis t.JobNumber,tblJobList2.JobNumber)


    rudy

  7. #7
    Join Date
    Dec 2002
    Location
    Iceland
    Posts
    9

    Re: MyODBC problem ?

    Originally posted by bstjean
    Nope. Tried it with an ODBC connection (MyODBC 3.51) and it works fine. So we're left with Access...
    Exactly how did you try it ? with what tables and such... and do you still have the sql statement ?

  8. #8
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: MyODBC problem ?

    Originally posted by arnar
    Exactly how did you try it ? with what tables and such... and do you still have the sql statement ?
    Ahem! Well...

    VisualAge Smalltalk 5.52 with a plain ODBC connection (MyODBC 3.51) to a 3.23.51-max-debug MySQL server on Windows 2000 SP1.

    Tables were dummy tables I created for the purpose of testing that case.

    As mentionned before, problem is that you use Access to query and IFNULL is not an Access valid keyword. But there used to be a way to execute "pass-through" queries from Access, which meant that the SQL parser didn't validate your SQL statement and assumed you knew that the statement would be a valid one on the target server. If it's possible, that's what you need.

    The only other way you can solve your problem is by using Access functions instead of MySQL functions...

    Hope this helps!

  9. #9
    Join Date
    Dec 2002
    Location
    Iceland
    Posts
    9
    Of course !
    pass-thought query is the way to go here !

    everything working great now thanks

    just for your "pleasure" Im including the final sql statement

    SELECT
    IFNULL(tblJobList2.JobNumber,tblnonFDPSJobList.Job Number) AS JobNumber,
    IFNULL(tblJobList2.Assignee,tblnonFDPSJobList.Assi gnee) AS Assignee,
    IFNULL(tblJobList2.Short_Description,tblnonFDPSJob List.Short_Description) AS Short_Description,
    IFNULL(tblJobList2.Task_Status,tblnonFDPSJobList.T ask_Status) AS Task_Status,
    IFNULL(tblJobList2.Type_of_Record,tblnonFDPSJobLis t.Type_of_Record) AS Type_of_Record,
    IFNULL(tblJobList2.Est_Completion,tblnonFDPSJobLis t.Est_Completion) AS Est_Completion

    FROM ( tblDummyTable AS temp
    LEFT JOIN tblJobList2 ON temp.DummyField = 1 )
    LEFT JOIN tblnonFDPSJobList ON temp.DummyField = 0
    WHERE
    temp.DummyField < 2 AND
    IFNULL(tblJobList2.JobNumber,tblnonFDPSJobList.Job Number) IS NOT NULL AND
    IFNULL(tblJobList2.Assignee,tblnonFDPSJobList.Assi gnee) = 'Arnar' AND
    IFNULL(tblJobList2.Task_Status,tblnonFDPSJobList.T ask_Status) != 'Completed'

    ORDER BY Assignee

Posting Permissions

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