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.

Sample Schemas - sh.costs table rows not inserted

Apoorva S-OracleJan 29 2018 — edited Jan 31 2018

Hi All,

I am trying to install all the sample schemas provided by Oracle.

Everything gets installed fine but only the rows in sh.costs table is not getting inserted.

The error in the log file says:

Snap43.png

Edit 2: I got the above error when I ran the @mksample script while keeping teh sh connection open in SQL Developer. When I closed all connections and ran the script again, I got the following error:

Snap45.png

Please help! I need this table for usage.

Thanks in advance

-Apoorva

This post has been answered by Apoorva S-Oracle on Jan 30 2018
Jump to Answer

Comments

John Thorton
jaramill

Please READ John's reply and read that LINK.

Then provided DDL statements of your objects (tables) and DML statements of your data (INSERT statements).

Also search this forum on PIVOT questions.  You'll find tons.

Frank Kulash

Hi,

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

Explain, using specific examples, how you get those results from that data.

Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

See the forum FAQ:

Do you know how to get the results you want in 1 column, with a separate row for each month?  (Actually, that might be a better format for these results.  Make sure whoever needs this output really prefers 1 row, with a separate column for each month, before going any farther.)

Assuming you really do want 1 row, then start by getting the results in separate rows for each month, using plain old GROUP BY and AVG.

Include a column that indicates the month (just the month, not including the year).  Either

TO_CHAR (rain_date, 'MM')        or

EXTRACT (MONTH FROM rain_date)   will work.

Once you have a result set like this, you can use the SELECT ... PIVOT feature (it's a feature, not a function) to produce a separate column for each month, as shown in the link John posted in reply #1.  Since you already computed the average, the aggregate function used in the PIVOT can be MIN or MAX

If you have trouble, post your code.

mathguy

The PIVOT operator works better when the grouping is by values in one or more columns. If you need to group by expressions (such as the month extracted from the RAIN_DATE) you may be better off pivoting "manually" - using the pivoting technique that was used for a long time, before the introduction of the PIVOT operator.

In your problem description you mentioned the ten gauges, but you didn't say what role they play. You can average over all gauges and all dates in a month, OR you can average separately for each gauge, and only over all dates in a month (getting a table with ten rows, one for each gauge, and 12 columns, one for each month). In the solution below I adopt the first position: average over all gauges and all dates in a month. If you need averages by gauge AND month, then add GAUGE to the SELECT clause and add GROUP BY GAUGE at the end of the query.

Column names must be known at parse time (unless you are willing to do more complicated things); column names can and should be controlled through your reporting system anyway. I use hard-coded column names JAN, FEB, MAR...

I added just a little bit of test data in a WITH clause. Obviously, that is NOT part of the solution; the query begins after the WITH clause.

On first reading, one might think that the WHERE clause is redundant, since rows where the date is not "this year" don't contribute to the computations anyway. Actually the WHERE clause plays an important role: it rejects rows from "other years" immediately, instead of processing each such "useless" row in 12 CASE expressions.   EDIT: Actually this comment doesn't quite apply anymore. Initially I had written the CASE expressions in terms of comparing RAIN_DATE to specific month and year, and I added the WHERE clause for the reason I just explained. (The explanation is incomplete: the main benefit of the WHERE clause is that unneeded rows aren't even read from disk in the first place, if there's an index on RAIN_DATE - even regardless of them being or not being processed in CASE expressions.) But after I added the WHERE clause, I realized I can now simply use EXTRACT (MONTH FROM...) - but now the WHERE clause is in fact needed to ensure the year is the right one. I am not deleting this paragraph since I think the idea is correct, and it may be helpful in other cases.

Also, the condition should be on RAIN_DATE and not on TRUNC(RAIN_DATE) or TO_CHAR(RAIN_DATE) of any kind, so that an index on RAIN_DATE, if it exists (as it should!) can be used. Even avoiding unnecessary function calls is important for performance, but the ability to use an index is enormously more so.

with precipitation (gauge, rain_date, precipitation_amount) as (
      select 3, date '2016-12-03', 8.4228 from dual union all
      select 8, date '2016-12-04', 3.2019 from dual union all
      select 1, date '2017-01-01', 3.8002 from dual union all
      select 2, date '2017-01-01', 3.1002 from dual union all
      select 3, date '2017-01-03', 0      from dual union all
      select 3, date '2017-01-06', 1.103  from dual union all
      select 1, date '2017-02-20', 2.3901 from dual union all
      select 4, date '2017-02-23', 0.932  from dual
    )
select avg(case extract(month from rain_date) when 1 then precipitation_amount end) as jan,
       avg(case extract(month from rain_date) when 2 then precipitation_amount end) as feb,
       avg(case extract(month from rain_date) when 3 then precipitation_amount end) as mar
from   precipitation
where  rain_date >= trunc(sysdate, 'y') and rain_date < add_months(trunc(sysdate, 'y'), 12)
;

JAN      FEB      MAR
-------  -------  -------
2.00085  1.66105

FriendlyTooo

Thanks for trying to help folks.  I tried reading the links from John but for whatever reason my brain is stuck.

I'll try to make it easier:  I created a view  with just two columns:

SELECT

RAIN_MONTH, AMOUNT

FROM WEATHER.PRECIP_CURRENTYEAR_AVG

Currently it displays like this:

04/2017    6.465

03/2017    0.2935

The goal is to get it to display the month as columns so it ends up (at the end of the year with 12 columns) like this:

03/2017        04/2017    05/2017

6.465            0.2935      4.40

I tried stuff like this:

SELECT * FROM

(

SELECT

RAIN_MONTH, AMOUNT

FROM WEATHER.PRECIP_CURRENTYEAR_AVG

) PIVOT

( MONTH

FOR AVG(AMOUNT)

IN (01/2017, 02/2017, 03/2017, 04/2017)

)

ORDER BY ASC

FriendlyTooo

Thanks for trying to help folks.   I tried reading the links from John but for whatever reason my brain is stuck.

I'll try to make it easier:   I created a view  with just two columns:

SELECT

RAIN_MONTH, AMOUNT

FROM WEATHER.PRECIP_CURRENTYEAR_AVG

Currently it displays like this:

04/2017    6.465

03/2017    0.2935

The goal is to get it to display the month as columns so it ends up (at the end of the year with 12 columns) like this:

03/2017         04/2017     05/2017

6.465             0.2935       4.40

I tried stuff like this  but I have something wrong:

SELECT * FROM

(

SELECT

RAIN_MONTH, AMOUNT

FROM WEATHER.PRECIP_CURRENTYEAR_AVG

) PIVOT

( MONTH

FOR AVG(AMOUNT)

IN (01/2017, 02/2017, 03/2017, 04/2017)

)

ORDER BY ASC

Thanks again for help

Matthew

Frank Kulash

Hi,

What's wrong with the solution Mathguy posted in reply #4?  Point out where it's getting the wrong results, and explain what the correct results are in those places.  If necessary, post additional sample data.

If you want to use the SELECT ... PIVOT feature, here's one way to implement the plan from reply #3:

WITH    rain_month    AS

(

    SELECT    TO_CHAR (rain_date, 'MON')  AS rain_month

    ,         AVG (precipitation_amount)  AS avg_amount

    FROM      precipitation

    WHERE     rain_date  >= TRUNC (SYSDATE, 'YEAR')  -- current year only

    GROUP BY  TO_CHAR (rain_date, 'MON')

)

SELECT    *

FROM      rain_month

PIVOT     (    MIN (avg_amount)

          FOR  rain_month  IN ( 'JAN'  AS jan

                              , 'FEB'  AS feb

                              , 'MAR'  AS mar

                         --   ...

                              , 'DEC'  AS dec

                              )

          )

;

Output (using the sample data from reply #4):

     JAN      FEB      MAR      DEC

-------- -------- -------- --------

2.00085  1.66105

Frank Kulash

Hi, Matthew,

FriendlyTooo wrote:

Thanks for trying to help folks. I tried reading the links from John but for whatever reason my brain is stuck.

I'll try to make it easier: I created a view with just two columns:

SELECT

RAIN_MONTH, AMOUNT

FROM WEATHER.PRECIP_CURRENTYEAR_AVG

Post the CREATE VIEW statement.

How do you plan to use the view, aside from this one query?

I tried stuff like this but I have something wrong:

Would you ever tell a car mechanic just "There's something wrong with my car", without giving any details?

Try to help the people who want to help you.  Give some clue about exactly what is wrong.  Did it raise an error?  Post the complete error message. Did it get the wrong results?  Did it get the right results, but in  the wrong order?  Was it something else?

At the very least, post enough (e.g., CREATE TABLE, INSERT and CREATE VIEW statements) so that people can re-create the problem themselves.

SELECT * FROM

(

SELECT

RAIN_MONTH, AMOUNT

FROM WEATHER.PRECIP_CURRENTYEAR_AVG

) PIVOT

( MONTH

Review the syntax of SELECT ... PIVOT.  Compare your query to similar queries that work.

The first thing after 'PIVOT (" is always an aggregate function.

Do you have a column called MONTH, or is the column called RAIN_MONTH?

FOR AVG(AMOUNT)

You can't use functions immediately the FOR keyword.

IN (01/2017, 02/2017, 03/2017, 04/2017)

What are the values that identify the output columns?  Is

01/2017     (which is just another way of writing

1 / 2017    or approximately

.0004958    ) one of those values?

)

ORDER BY ASC

You need an expression (such as a column name) right before the ASC keyword.

jaramill

FriendlyTooo wrote:

Thanks for trying to help folks. I tried reading the links from John but for whatever reason my brain is stuck.

Nonsense.  The link is to post that describes HOW you should ask ANY question on these forums?  It wasn't specific to your question but for all questions.

And as you can see Frank already has to ask you again and again to POST your DDL statements of the tables/views you are using in your example.

That's the WHOLE point of the FAQ (Frequently Asked Questions) and the post is a guide on HOW TO ASK A QUESTION on these forums.  Very simple Friendly Tooo.

So do what Frank asked

Post the CREATE VIEW statement.

How do you plan to use the view, aside from this one query?"

Apologies as I didn't see that the link John posted was not the "how to ask a question".  Disregard this post.

mathguy

jaramill wrote:

FriendlyTooo wrote:

Thanks for trying to help folks. I tried reading the links from John but for whatever reason my brain is stuck.

Nonsense. The link is to post that describes HOW you should ask ANY question on these forums? It wasn't specific to your question but for all questions.

[...]

Not really. Please read Reply 1 again (to which the OP was referring). Then edit your Reply 9 as appropriate.    Cheers,   mathguy

jaramill

mathguy wrote:

jaramill wrote:

FriendlyTooo wrote:

Thanks for trying to help folks. I tried reading the links from John but for whatever reason my brain is stuck.

Nonsense. The link is to post that describes HOW you should ask ANY question on these forums? It wasn't specific to your question but for all questions.

[...]

Not really. Please read Reply 1 again (to which the OP was referring). Then edit your Reply 9 as appropriate. Cheers, mathguy

OOPs!  that font is REALLY ...tiny.  I thought it was the one for asking a question.  I'll update the my reply.

JPDominator

Without rounding

with precipitation as (select trunc(sysdate - (1 + rownum)) rain_date,

               case when SYS.dbms_random.value(0, 10) > 8

                    then SYS.dbms_random.value(0, 2)

                    else 0

                end as inches

          from dual connect by level <= 1000)

select *

  from (select to_char(rain_date,'MON') the_month,

               inches

          from precipitation

         where rain_date >= trunc(sysdate, 'YEAR') )

pivot (avg(inches) for the_month in ('JAN' as jan,

                                      'FEB' as feb,

                                      'MAR' as mar,

                                      'APR' as apr,

                                      'MAY' as may,

                                      'JUN' as jun,

                                      'JUL' as jul,

                                      'AUG' as aug,

                                      'SEP' as sep,

                                      'OCT' as oct,

                                      'NOV' as nov,

                                      'DEC' as dec));

With rounding

with precipitation as (select trunc(sysdate - (1 + rownum)) rain_date,

               case when SYS.dbms_random.value(0, 10) > 8

                    then SYS.dbms_random.value(0, 2)

                    else 0

                end as inches

          from dual connect by level <= 1000)

select *

  from (select to_char(rain_date,'MON') the_month,

               round(avg(inches), 4) avg_inches

          from precipitation

         where rain_date >= trunc(sysdate, 'YEAR')

         group by to_char(rain_date,'MON')

         )

pivot (max(avg_inches) for the_month in ('JAN' as jan,

                                          'FEB' as feb,

                                          'MAR' as mar,

                                          'APR' as apr,

                                          'MAY' as may,

                                          'JUN' as jun,

                                          'JUL' as jul,

                                          'AUG' as aug,

                                          'SEP' as sep,

                                          'OCT' as oct,

                                          'NOV' as nov,

                                          'DEC' as dec));

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

Post Details

Locked on Feb 28 2018
Added on Jan 29 2018
12 comments
535 views