Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2015
    Posts
    4
    Provided Answers: 1

    Answered: Inner Join With Select Statement

    Hello everyone.
    I'm new to this forum, so I'm pretty sure I'm doing something wrong here lol...
    Anyway, I'm having a problem regarding an SQL statement that I use very commonly on SQL Server Studio Management, but it gives me an SQL error on Informix platform:

    SQL Error (-201): A syntax error has occurred.


    Here's an example of the SQL statement:

    SELECT *
    FROM t1 AS tEx1
    INNER JOIN (SELECT order_num,
    MAX(date) AS modate
    FROM t2
    GROUP BY order_num) AS tEx2
    ON tEx1.order_num = tEx2.order_num
    AND tEx1.date = tEx2.modate

    Am I missing something for the Informix platform? Any clues?
    Last edited by Simbiose; 06-23-15 at 12:20.

  2. Best Answer
    Posted by Simbiose

    "Wow, Informix works in misterious ways lol.
    I don't know if what I did is the actual solution or just an alternate route, but all I did was to fill the data I want into the tables and then and only then join the tables.
    Have a look:

    Code:
    SELECT * FROM orders into temp t1;
    SELECT order_num, MAX(order_date) AS modate FROM orders GROUP BY order_num into temp t2;
    
    SELECT *
    FROM t1 AS tEx1
    INNER JOIN t2 AS tEx2
    ON tEx1.order_num = tEx2.order_num
    AND tEx1.order_date = tEx2.modate;
    I hope this helps others in the future, as it sure helped me "


  3. #2
    Join Date
    May 2008
    Posts
    19
    Hi Simbiose,
    no, that looks syntacically correct to me. Are there more statements involved? Which Informix Version and platform are using?
    I ran a quick test to the stores_demo database and modified your
    statement a little:

    select * from orders into temp t1;
    select * from orders into temp t2;
    SELECT *
    FROM t1 AS tEx1
    INNER JOIN (SELECT order_num,
    MAX(order_date) AS modate
    FROM t2
    GROUP BY order_num) AS tEx2
    ON tEx1.order_num = tEx2.order_num
    AND tEx1.order_date = tEx2.modate;

    That works perfectly.

    Try running the statements in question inside of dbaccess. If it gives you a syntax error, choose "modify", which will bring the cursor to the position where the error occured.

  4. #3
    Join Date
    Jun 2015
    Posts
    4
    Provided Answers: 1
    Quote Originally Posted by wowbagger_771 View Post
    Hi Simbiose,
    no, that looks syntacically correct to me. Are there more statements involved? Which Informix Version and platform are using?
    I ran a quick test to the stores_demo database and modified your
    statement a little:

    select * from orders into temp t1;
    select * from orders into temp t2;
    SELECT *
    FROM t1 AS tEx1
    INNER JOIN (SELECT order_num,
    MAX(order_date) AS modate
    FROM t2
    GROUP BY order_num) AS tEx2
    ON tEx1.order_num = tEx2.order_num
    AND tEx1.order_date = tEx2.modate;

    That works perfectly.

    Try running the statements in question inside of dbaccess. If it gives you a syntax error, choose "modify", which will bring the cursor to the position where the error occured.
    Thank you for your reply.
    Actually, what you suggested occurred to me right after I posted lol...
    I managed to build my query

  5. #4
    Join Date
    Jun 2015
    Posts
    4
    Provided Answers: 1

    Apparently Still not working ...

    Hi again.
    Sorry to bring this thread up again, but it seems the solution given above doesn't work either.

    I just keep getting the error SQL Error (-201): A syntax error has occurred.

    The temporary tables are populated, but I just can't get the inner join statement to work...

  6. #5
    Join Date
    Jun 2015
    Posts
    4
    Provided Answers: 1
    Wow, Informix works in misterious ways lol.
    I don't know if what I did is the actual solution or just an alternate route, but all I did was to fill the data I want into the tables and then and only then join the tables.
    Have a look:

    Code:
    SELECT * FROM orders into temp t1;
    SELECT order_num, MAX(order_date) AS modate FROM orders GROUP BY order_num into temp t2;
    
    SELECT *
    FROM t1 AS tEx1
    INNER JOIN t2 AS tEx2
    ON tEx1.order_num = tEx2.order_num
    AND tEx1.order_date = tEx2.modate;
    I hope this helps others in the future, as it sure helped me

Posting Permissions

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