Re: Developing Query
From: joel garry <joel-garry_at_home.com>
Date: Fri, 21 May 2010 09:11:58 -0700 (PDT)
Message-ID: <04092d3f-5e34-422d-8331-af9bdc703d4f_at_a27g2000prj.googlegroups.com>
On May 21, 8:02 am, Shakespeare <what..._at_xs4all.nl> wrote:
> Op 21-5-2010 16:13, Mark D Powell schreef:
>
>
>
> > On May 19, 11:50 am, Don B<Braenovich...._at_DOL.GOV> wrote:
> >> select INQMSIS.mine_tbl.sic_desc, COUNT
> >> (INQPROD.ACC_INJ_TBL.AI_DOC_NUM), COUNT
> >> (INQPROD.ACC_INJ_TBL.AI_DOC_NUM) * 200000 from INQPROD.ACC_INJ_TBL,
> >> INQMSIS.mine_tbl where ((INQMSIS.mine_tbl.c_m_ind = 'M') and
> >> (INQPROD.ACC_INJ_TBL.INJ_DEGR_CD IN ('01', '02', '03', '04', '05',
> >> '06')) and ((to_number(to_char(INQPROD.ACC_INJ_TBL.AI_DT,'YYYY' ))) =
> >> 2009)) and INQPROD.ACC_INJ_TBL.MINE_ID = INQMSIS.mine_tbl.mine_id
> >> group by INQMSIS.mine_tbl.sic_desc order by 1 ;
>
> >> I am trying to modify a query to show an additional column. This
> >> column is suppose to multiply the Count Document No. column by
> >> 200,000. I am a beginner and cannot figure out what I'm doing wrong.
> >> Can somebody get me on the right path.
>
> >> Thanks
> >> Don
>
> > It would be helpful if you would post the actual Oracle error message
> > you are receiving or at least explicitly state what issue you face.
>
> > Generally speaking when you use a group by clause every column in the
> > select list either has to be part of the group by clause or have an
> > aggregate function applied to it. If you try to reference individual
> > column values you get an error.
>
> > Perhaps you want sum(col * 200000) or 200000 * sum(col) but from your
> > post I cannot tell.
>
> What is the difference?
>
> > HTH -- Mark D Powell --
>
> Shakespeare
Date: Fri, 21 May 2010 09:11:58 -0700 (PDT)
Message-ID: <04092d3f-5e34-422d-8331-af9bdc703d4f_at_a27g2000prj.googlegroups.com>
On May 21, 8:02 am, Shakespeare <what..._at_xs4all.nl> wrote:
> Op 21-5-2010 16:13, Mark D Powell schreef:
>
>
>
> > On May 19, 11:50 am, Don B<Braenovich...._at_DOL.GOV> wrote:
> >> select INQMSIS.mine_tbl.sic_desc, COUNT
> >> (INQPROD.ACC_INJ_TBL.AI_DOC_NUM), COUNT
> >> (INQPROD.ACC_INJ_TBL.AI_DOC_NUM) * 200000 from INQPROD.ACC_INJ_TBL,
> >> INQMSIS.mine_tbl where ((INQMSIS.mine_tbl.c_m_ind = 'M') and
> >> (INQPROD.ACC_INJ_TBL.INJ_DEGR_CD IN ('01', '02', '03', '04', '05',
> >> '06')) and ((to_number(to_char(INQPROD.ACC_INJ_TBL.AI_DT,'YYYY' ))) =
> >> 2009)) and INQPROD.ACC_INJ_TBL.MINE_ID = INQMSIS.mine_tbl.mine_id
> >> group by INQMSIS.mine_tbl.sic_desc order by 1 ;
>
> >> I am trying to modify a query to show an additional column. This
> >> column is suppose to multiply the Count Document No. column by
> >> 200,000. I am a beginner and cannot figure out what I'm doing wrong.
> >> Can somebody get me on the right path.
>
> >> Thanks
> >> Don
>
> > It would be helpful if you would post the actual Oracle error message
> > you are receiving or at least explicitly state what issue you face.
>
> > Generally speaking when you use a group by clause every column in the
> > select list either has to be part of the group by clause or have an
> > aggregate function applied to it. If you try to reference individual
> > column values you get an error.
>
> > Perhaps you want sum(col * 200000) or 200000 * sum(col) but from your
> > post I cannot tell.
>
> What is the difference?
>
> > HTH -- Mark D Powell --
>
> Shakespeare
It is possible to have rounding issues do funny things in sqlplus formatting. It may also be possible to have rounding differences in this type of sum and multiplication, depending on col values. Not everything can go cleanly from decimal to binary, even with 38 digits of precision.
jg
-- _at_home.com is bogus. http://yro.slashdot.org/story/10/05/21/1127210/Facebook-Others-Giving-User-Private-Data-To-AdvertisersReceived on Fri May 21 2010 - 11:11:58 CDT