Syntax error in SSRS with multiple joins

  • Hello:

    I posted this in the Reporting Services section but have yet to receive a response, so I thought I'd try here!

    I'm new to all this, so please forgive me if this is a stupid question. I'm trying to pull data from a UNIX-based database into SSRS through an ODBC connection. Here's the SQL query that I want to form my dataset:

    SELECT

    person.name,

    orders.ID,

    orders.customer,

    item.type,

    detail.item,

    detail.qty,

    customer.phone,

    item.name,

    detailassoc.date,

    detailassoc.time

    FROM (((((person INNER JOIN customer

    ON person.ID = customer.ID)

    RIGHT JOIN orders

    ON orders.customer = person.ID)

    RIGHT JOIN detailkey

    ON detailkey.ID = orders.ID)

    RIGHT JOIN detail

    ON detail.ID = detailkey.key)

    RIGHT JOIN detailassoc

    ON detailassoc.ID = detail.ID)

    LEFT JOIN item

    ON item.ID = detail.itemkey

    I have the same tables in an instance of SQL Server and this query works fine there. In SSRS, however (where I have to use ODBC because I need the data in real time and the SQL Server database is only updated nightly), I receive a syntax error that points to one of the parentheses in my joins. I tried taking out the parentheses, but then it throws a syntax error pointing to the word JOIN.

    Does anyone know what's wrong with my syntax here?

    Thanks in advance! [BigGrin]

  • A couple of questions:

      1. Do you really need all the OUTER JOIN's? Can you have an order without a customer? I normally would think that would not be possible.

      2. What is the source RDBMS? Or, is it not an RDBMS?

      3. Could you post some DDL and data like suggested in the links in my signature?

    If you have any answers posted on your other thread please put the link in here please, so others do not waste their time trying to help you on this one. If this is the first answer please put this link in your other thread so no one wastes their time over there.

    Thanks,

  • 1. Do you really need all the OUTER JOIN's? Can you have an order without a customer? I normally would think that would not be possible.

    2. What is the source RDBMS? Or, is it not an RDBMS?

    3. Could you post some DDL and data like suggested in the links in my signature?

    Hi, Jack:

    To answer your questions:

    1. I guess I really don't need the outer joins for the most part--I just don't really trust this data to be complete, so I was trying to safeguard against skipping over records where someone left something out. Regardless, I changed all the outer joins to inner joins and I received the same error.

    2. The source is a UNIX-based proprietary system. I guess you would call it an RDBMS, but its structure is pretty unique--hence the trouble it's been giving me. Part of my problem is that I'm not a DBA; the DBA who created the system is no longer here and I've been charged with trying to piece all of this stuff together.

    3. I'll do my best to try and give you some data, but I'm currently working on three other projects and am in the process of pulling my hair out. :crazy:

    I apologize if I didn't give enough info...I thought that I just had a simple syntax problem. I guess I'm just confused about why this query works fine in my SQL Server Query Analyzer but doesn't work in an SSRS dataset query.

  • I'm wondering if the system you are describing and/or the ODBC driver support the Ansi join syntax. I've seen a few legacy systems where they still require joins in the where clause. It's a much older style of writing the joins. It's inadviseable to write outer joins for SQL Server in this manner as the results can at times be "unpredictable" according to so MS documentation I read somewhere once upon a time... Basically a right join has the * on the right, left on the left, inner would just be an = sign.

    SELECT

    person.name,

    orders.ID,

    orders.customer,

    item.type,

    detail.item,

    detail.qty,

    customer.phone,

    item.name,

    detailassoc.date,

    detailassoc.time

    FROM person,

    customer,

    orders,

    detailkey,

    detail,

    detailassoc,

    item

    WHERE person.ID = customer.ID

    AND orders.customer =* person.ID

    AND detailkey.ID =* orders.ID

    AND detail.ID =* detailkey.key

    AND detailassoc.ID =* detail.ID

    AND item.ID *= detail.itemkey

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • Hi, Luke:

    Thanks for getting me on the right track! The code that ended up working isn't exactly like yours, but it's very similar:

    SELECT

    person.name,

    orders.ID,

    orders.customer,

    item.type,

    detail.item,

    detail.qty,

    customer.phone,

    item.name,

    detailassoc.date,

    detailassoc.time

    FROM person,

    customer,

    orders,

    detailkey,

    detail,

    detailassoc,

    item

    WHERE person.ID = customer.ID

    AND orders.customer = person.ID

    AND detailkey.ID = orders.ID

    AND detail.ID = detailkey.key

    AND detailassoc.ID = detail.ID

    AND item.ID = detail.itemkey

    AND detail.qty > 0

    AND detail.item = '443455'

    AND detailassoc.date = '2008-09-27'

    The problem I'm having now is that it runs fine when I specify the values of detail.item and detailassoc.date in the actual SQL query, but when I make them parameters (i.e., detail.item = ? AND detailassoc.date = ?), and try to preview the report, it just runs and runs and never displays any results. I don't get an error message saying that there's anything wrong with my parameters--it just runs forever. Do you have any idea why this query would work with assigned values but not with parameters?

  • Without knowing the exact datasource this is just a guess. Have you attempted to define the parameters with @item and @date instead of the '?' character. Check the parameters section of your dataset and make sure you are passing what you really think you are. Additionally, your datasource may not understand the format that SSRS passes these parameters in as, so you may need to change them around a bit.

    Do you have something equivalent to Query Analyzer that you can run against this datasource? I'd be interested if you can run these parameterized queries against it directly from the command line and how you need to format them that they work. It will most likely all come down to syntax and since we don't know what datasource you are using we can't relaly give you a decent answer. You stated that "The source is a UNIX-based proprietary system." Even so, it probably is stored in some type of database, be it RMS, Oracle, MySQL, MUMPS or something else. And I'd be surprised if the ODBC driver you are using was made in house, so you may be able to find some documentation on how to pass in parameters there, or if you let us know the vendor someone here may have already worked with it.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply