SQL Select, Join, Where help

Ignition
1

Hi all,

I am pulling data from multiple tables to populate a Vision Table using a named query. This query worked until I started to add to it. When it failed, I put it back to the original and it did not work.

SELECT
binder_notes.t_stamp,
usernames.username,
binder_notes.notes
FROM
binder_notes
JOIN usernames ON binder_notes.user_id = usernames.id
WHERE binder_notes.binder_id = :binder_id

When I test it, I get this error message:
GatewayException: org.postgresql.util.PSQLException: The column index is out of range: 2, number of columns: 1.
caused by Exception: org.postgresql.util.PSQLException: The column index is out of range: 2, number of columns: 1.

Ignition v8.0.16 (b2020082513)
Java: Azul Systems, Inc. 11.0.7

Now I am confused as to why it no longer works event when testing in the named query.

Also, I want to add to WHERE so that I get all the records with the selected binder_id, topic_id and page_id.

I think like this:

WHERE binder_notes.binder_id = :binder_id AND binder_notes.topic_id = :topic_id AND binder_notes.page_id = :page_id

The parameters come from a selected value on a dropdown for each table.

I would appreciate any help as basic as I am sure it is as I just don’t understand why it was working fine and then decided no to!

Thanks, Steven

2

Well, this is interesting…

Just as i gave up I remembered that comments in a query a few days ago, made it not work. So I came back, deleted the commented lines from the query and it works again.

Now I just have to figure out how to add my WHERE statement.

Steven

3

So this is the working query…

SELECT
binder_notes.t_stamp,
usernames.username,
binder_notes.notes
FROM
binder_notes
JOIN usernames ON binder_notes.user_id = usernames.id
WHERE binder_notes.binder_id = :binder_id AND binder_notes.topic_id = :topic_id AND binder_notes.page_id = :page_id

This is the second time I had a query not work with comments below the query. Could this be a bug?

Anyway, thanks for reading!! :grinning: :grinning: :grinning:

Steven

4

Maybe it will help you

5

I mean that :

JOIN usernames ON binder_notes.user_id = usernames.id
AND binder_notes.binder_id = :binder_id AND binder_notes.topic_id = :topic_id AND binder_notes.page_id = :page_id

6

@etlikmilos,

Thanks for the information and the resource. I will take a look and learn some more.

Thanks, Steven

7

No, it is a false expectation on your part. You are thinking JDBC is like a database's management studio, where query windows will also run that database's flavor of SQL script. Most JDBC drivers will run only one statement at a time, and only accept a valid non-script statement.

8

@pturmel I don’t know anything about that. I just got confused because the only difference between it working and not, was a valid comment.

I have comments in other queries and they work fine. I thought maybe I was just overloading my system so I restarted and got the same results.

I will have to be sure to clear out any comments if I have a query that does not work just to be sure.

Thanks for all the help,

Steven