Re: Problem querying a view

On Jun 11, 1:53 pm, teresa <pixelm..._at_gmail.com> wrote:
> On Jun 11, 2:44 pm, teresa <pixelm..._at_gmail.com> wrote:
>
>
>
>
>
> > Hello all,
>
> > I have inherited a database application and I'm writing views based on
> > the queries the application uses. One of the queries is a join on
> > three tables to get a count of a particular user id. Here are the
> > tables (examples):
>
> > CREATE TABLE TA
> > (ID NUMBER, TYPE VARCHAR2(1 BYTE), USER_ID VARCHAR2(50 BYTE))
>
> > CREATE TABLE TC
> > (ID NUMBER, INST_ID NUMBER)
>
> > CREATE TABLE TI
> > (ABBR VARCHAR2(20 BYTE), INST_NAME VARCHAR2(100 BYTE), INST_ID NUMBER)
>
> > Here is the view:
>
> > CREATE OR REPLACE FORCE VIEW TA_C_I AS
> > SELECT
> > a.id AS a_id
> > ,a.type AS type
> > ,a.user_id AS user_id
> > ,c.id AS c_id
> > ,c.inst_id AS c_inst_id
> > ,i.abbr AS abbr
> > ,i.inst_name AS inst_name
> > ,i.inst_id AS i_inst_id
> > FROM
> > TA a
> > ,TC c
> > ,TI i
> > WHERE a.id = c.id
> > AND c.inst_id = i.inst_id
>
> > When I query:
> > SQL> select count(*), user_id
> > 2 from ta_c_i
> > 3 group by user_id;
>
> > COUNT(*) USER_ID
> > ---------- --------------------------------------------------
> > 100 8E1E9E08-802E-D019-AF4C32C5CF0D1BB2
> > ...
> > 121 A0725615-802E-D019-A6B3168A70F3C2F2
>
> > 47 rows selected.
>
> > Then when I query:
> > SQL> select count(*) from ta_c_i
> > 2 where user_id = 'A0725615-802E-D019-A6B3168A70F3C2F2';
>
> > COUNT(*)
> > ----------
> > 0
>
> > I must not have a good idea of how this works (probable). I really
> > did expect the second query to return 121, since that was the count
> > from the last row of the previous query, where I got the user_id
> > from. (the view's query works in the production system, which isn't
> > different from what I'm working with now)
>
> > Why didn't the second query return 121?
>
> > Thanks so much,
> > teresa
>
> I just tried these:
>
> SQL> select count(*) from ta_c_i
> 2 where user_id like 'A%';
>
> COUNT(*)
> ----------
> 2212
>
> SQL> select count(*) from ta_c_i
> 2 where user_id like 'A0725615%';
>
> COUNT(*)
> ----------
> 121
>
> SQL> select count(*) from ta_c_i
> 2 where user_id like 'A0725615-802E-D019-A6B3168A70F3C2F2%';
>
> COUNT(*)
> ----------
> 121
>
> SQL> select count(distinct user_id) from ta_c_i
> 2 where user_id like 'A0725615-802E-D019-A6B3168A70F3C2F2%';
>
> COUNT(DISTINCTUSER_ID)
> ----------------------
> 1
>
> SQL> select count(distinct user_id) from ta_c_i
> 2 where user_id = 'A0725615-802E-D019-A6B3168A70F3C2F2';
>
> COUNT(DISTINCTUSER_ID)
> ----------------------
> 0
>
> I don't get it.
>
> Thanks,
> teresa- Hide quoted text -
>
> - Show quoted text -

You likely have trailing spaces on the USER_ID values and, as such, nothing is equal to your 'truncated' string. Try this and see what is returned:

select '||user_id||' user_id
from
(select distinct user_id from ta);

You may be surprised at what you find.

David Fitzjarrell Received on Mon Jun 11 2007 - 14:03:29 CDT