Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Problem querying a view

Re: Problem querying a view

From: teresa <pixelmeow_at_gmail.com>
Date: Mon, 11 Jun 2007 19:28:26 -0000
Message-ID: <1181590106.182249.23210@m36g2000hse.googlegroups.com>


On Jun 11, 3:03 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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.
>

I am an idiot. All I needed was trim(). (I'm still thinking about your example...)

Thank you for the hint. :-)

teresa Received on Mon Jun 11 2007 - 14:28:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US