Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Convert UPPERCASE to Proper Case

user560159Jun 21 2011 — edited Jun 22 2011
Hi There,
I'm jsut wondering how you convert data from all UPPER CASE to Proper case meaning. The first letter of each word is UPPER CASE only.

First name is: Jennifer
Last name is: Walsh

Thanks

Comments

BluShadow

Well, syntactically it looks equivalent.

It's likely one of the ANSI implementation bugs that exist in Oracle where it's re-writing the query badly.

Perhaps check the trace etc. to see how the query has been rewritten.

chris227

GregV wrote:

Hi,

I'm trying to use the ANSI syntax for a SQL statement involving nested table columns, but I can't seem to get it right.

12       (SELECT * FROM t WHERE id = 3) v,


12  JOIN t ON t.id = 3

13  LEFT JOIN TABLE(t.week_order_qties) oq ON oq.week_date = w.week_date

14  LEFT JOIN TABLE(t.week_sale_qties)  sq ON sq.week_date = w.week_date

Just from reading it:

Where you had a cartesian product you now have a join.

What if you change it to

12  CROSS JOIN (select * from t where t.id = 3) v


or perhaps just


CROSS JOIN ON t.id = 3


I am not quite sure if this is valid syntax, cross join with on-clause.

BluShadow

No, you can't CROSS JOIN with ON clause.  CROSS join has no ON clause.

chris227

BluShadow wrote:

No, you can't CROSS JOIN with ON clause.  CROSS join has no ON clause.

Ok, was to lazy to consult the docs, but was pretty sure that it is wrong therefore the first approach with the inline view.

After a deeper look i come up with this:

with weeks AS (select TO_DATE('06/10/2014', 'dd/mm/yyyy') + 7 * (level-1) week_date

                from dual

                connect by level <= 5

                )

select w.week_date,

        oq.id,

        oq.week_date,

        oq.qty,

        sq.week_date,

        sq.qty

FROM weeks w

left outer join (select t.id, oq.week_date, oq.qty from t, TABLE(t.week_order_qties) oq where t.id = 3) oq

ON (w.week_date = oq.week_date)

left outer join (select t.id, sq.week_date, sq.qty from t, TABLE(t.week_sale_qties ) sq where t.id = 3) sq

oN (w.week_date = sq.week_date)

order by w.week_date

WEEK_DATEIDWEEK_DATEQTYWEEK_DATEQTY
10/06/2014 - - - - -
10/13/2014 - - - - -
10/20/2014 - - - - -
10/27/2014 3 10/27/2014 10 - -
10/27/2014 3 10/27/2014 9 - -
11/03/2014 - - - 11/03/2014 11
11/03/2014 - - - 11/03/2014 12
GregV

Thanks Chris and Blu for looking at this.

Chris, it works, but this is twisted. It mixes old style and ANSI syntax, so I'd better stick to the old style only.

In fact, I'm still one of those guys who writes queries with the old style syntax. I do resort to ANSI syntax when necessary. As Blu said, this is probably a bug with the ANSI syntax, but I haven't found any note in MOS about it (searching the KB with keywords such as "TABLE" is no great help ).

Anyway, I'll open an SR and see where it goes. I'll keep in touch.

chris227

GregV wrote:

Thanks Chris and Blu for looking at this.

Chris, it works, but this is twisted. It mixes old style and ANSI syntax, so I'd better stick to the old style only.

Upps, you were right

We may just change that two lines to be pure:

with weeks AS (select TO_DATE('06/10/2014', 'dd/mm/yyyy') + 7 * (level-1) week_date

                from dual

                connect by level <= 5

                )

select w.week_date,

        oq.id,

        oq.week_date,

        oq.qty,

        sq.week_date,

        sq.qty

FROM weeks w

left outer join (select t.id, oq.week_date, oq.qty from t join TABLE(t.week_order_qties) oq on t.id = 3) oq

ON (w.week_date = oq.week_date)

left outer join (select t.id, sq.week_date, sq.qty from t join TABLE(t.week_sale_qties ) sq on t.id = 3) sq

oN (w.week_date = sq.week_date)

order by w.week_date

However, i played around with this query and there are some results i cant explain. So i would be interested in the outcome of the SR. May be it has something do to with the internal correlation of the nested tables to there parent rows which may be corrupted in this straight forward transformed ansi style notation.

GregV

Hi guys,


Just to let you know that after a lot of beating around the bushes with the Oracle support, a bug has finally been filed for this problem:

Bug 20363558 : WRONG RESULTS FOR ANSI JOIN ON NESTED TABLE

1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 20 2011
Added on Jun 21 2011
12 comments
50,365 views