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: Tue, 12 Jun 2007 12:22:28 -0000
Message-ID: <1181650948.932296.172200@g37g2000prf.googlegroups.com>


On Jun 11, 3:46 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Jun 11, 2:28 pm, teresa <pixelm..._at_gmail.com> wrote:
>
>
>
>
>
> > 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. :-)
>
>
> You are not an idiot, by any stretch of the imagination. There was a
> fairly long discussion on this a while back in the newsgroup (whether
> varchar2 columns truncatred trailing spaces or stored what was
> entered; obviously it stores what was entered, trailing spaces and
> all, and I learned something from that discussion).

That's why I love these groups, I learn so much just lurking that I very rarely have to ask a question. I save so many discussions just because I know I'm going to need the information later. You just can't get this sort of thing from a book.

teresa Received on Tue Jun 12 2007 - 07:22:28 CDT

Original text of this message

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