0

I'm sorry for my bad english, I'll try explain my question as clearest as I can.

I'm developing a web app that retrieve remote data from an IBM iSeries AS/400, the backend is made up of PHP+ODBC.

I've successfully gathered data using sample queries (SELECT * FROM tableA WHERE Condition), but now I'm stuck in a more complex query.

I've tried many ways to retrieve the data I need, but the only way I've found is to use one query to gather an array, where I store the keys I need, and then cast as much queries as the keys in the array.

That involve in a terrible result, as it takes over a minute because it have to launch too many queries.

The IBM iSeries Access ODBC driver is the only way I've found to perform the connection.

Below you can find an example of the query I'm trying to cast via the ODBC:

select * from 
db.TableA
where 
TableA.Column1='9999-12-31' and 
TableA.Column2='0001' and 
TableA.Column3='0' and 
TableA.bcacod IN (
select TableB.Column1 from db.TableB inner join db.TableC on TableB.Column1=TableC.Column1 where
TableB.Column2='1' and 
(TableB.Column3=0 or TableB.Column3>=" . date("Ymd") . ") 
and TableC.Column2='COM' 
and TableB.Column4='20') 
order by TableA.Column3

I get always the same error, no matter what I try to improve in my query:

Warning: odbc_prepare(): SQL error: [IBM][Programma di controllo ODBC di System i Access][DB2 per i5/OS]SQL0206 - Colonna o variabile globale TableA.Column1 non trovata., SQL state S0022

I've checked many times, names of all columns are correct.

I've done other simplest queries with those fields and they work correctly.

Another things I've tried is to execute the query directly into the DB, and it works fine.

My question is:

Is the ODBC driver capable of perform subqueries?

If it's not possibile I'll start searching other ways to do what I need.

Thanks a lot to everyone.

Alessio

Shanrya
  • 5
  • 1
  • 1
    Error seems clear: *Column1* cannot be found in *TableA*. Please show [table schema](https://stackoverflow.com/questions/2973186/how-to-view-db2-table-structure): `DESCRIBE TABLE db.TableA`. – Parfait Aug 26 '19 at 17:32
  • You say "names of all columns are correct" but Db2 thinks otherwise; I'd be more inclined to trust the database on that. Since you don't post the actual query or the table definition, it's not possible for anyone but you to find the error. – mustaccio Aug 26 '19 at 18:09
  • @mustaccio, DB2 for i (i5/OS) is little bit different. If schema name is used to qualify table name in from clause, schema name has to be used in all places where table name is used. Otherwise, DB2 will give error. Workaround is to use alias as suggested. Also, it will work if current schema is set and table referenced is from current schema. – Satya Aug 26 '19 at 18:37
  • Thanks @Satya, your solution works fine. Placing the name of the db before the table name allow me to cast successfully the query from the ODBC. One thing I don't understand is why if I cast the same query directly to the db it works without problems. Anyway, really thanks a lot to everyone! – Shanrya Aug 27 '19 at 06:14

1 Answers1

0

Either add schema name in all places or add alias to table name in from clause.

Example:

select * from 
db.TableA TableA
where 
TableA.Column1='9999-12-31' 
..
...
order by TableA.Column3
Wieland
  • 1,663
  • 14
  • 23
Satya
  • 583
  • 1
  • 4
  • 6