Re: Problem querying a view

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 Received on Mon Jun 11 2007 - 13:53:06 CDT