Re: Problem querying a view

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. :-)
>
> teresa- Hide quoted text -
>
> - Show quoted text -

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).

David Fitzjarrell Received on Mon Jun 11 2007 - 14:46:42 CDT