Re: Datablock query problem (where)

Hi Scott,  

I don't believe you can specify multiple tables as a datasource, except via a database view. Other alternatives are a stored procedure or From clause query.  

However, since you are only selecting values from a single table there is a single data source. To include other tables in the Where clause, you can use a sub-query, eg.
calls.call deleted <> 1 and exists
(select 1 from action details, hd roles
where action details.call call id = calls.call id and hd roles.hd role id = action details.hd role hd role ID and hd roles.usr user id = :Global.hd user)

Regards,
Neville.

    Scott wrote in message <39F6290F.389BF329_at_student.ecu.edu.au>...     I'm using Oracle Developer 6, with an Oracle 8i database.     My datablock is listing records from a single table, that works fine.

    But when I try to restrict this information via the 'where' clause, it causes the problems.

    It is in the where clause I reference foreign keys of the block in other tables.

    Here is the sql statement that causes the error (generated by Developer - with my own where and order by) It's a bit fierce, so I don't expect anyone to follow it from scratch!

    It's the bit in lowercase that is causing the problems     SELECT ROWID,calls.CALL ID,calls.DATE LOGGED,calls.USR USER ID, calls.PROBTYPE PROB TYPE ID,calls.STATTYPE STATUS TYPE ID,calls.SLA SLA I D,
calls.SLAPRIOR PRIORITY SEQ,calls.ASSET ASSET ID,calls.CALL DELETED,calls .PROBLEM DESC
    FROM CALLS, action details, hd roles     where calls.call deleted <> 1 and (action details.call call id = calls.call id and hd roles.hd role id = action details.hd role hd role ID and

    hd roles.usr user id = :Global.hd user))     order by calls.sla sla id desc, slaprior priority seq desc, date logged

    Thanks.

    G'day

    I've been desperately trying to figure out how to add a where clause to

    a database block that references another table.     Even if I add the other table to the data source names, and qualify each

    field referenced with the table name, at runtime I receive this error:

    ORA-00918 column ambiguously defined     If I run the same query in pl/sql, it works after I take out ROWID     (which is inserted by tools automatically) in the select statement.

    This only happens when I try to reference a column from another table. I

    only have the one block in my form, and I've tried adding the other     table as another db block, but to no avail.

    Can anyone help?

--