sum expressions

hi
 i have the following code for summing transactions of my data

SELECT 
cr_count.cnt_item, cr_track.ti_name_a, cr_uom.uom_code,
 (select cr_count.cnt_openc from cr_count where cnt_date = '01-aug-13' and cnt_item = '20001'  and cnt_str_id = '2001') Open_Bal,
 sum(cr_count.cnt_trin) Tr_In, Sum(cr_count.cnt_rcv) Recv ,
       Sum(cr_count.cnt_dsd) DSD , Sum(cr_count.cnt_prod) Prod , Sum(cr_count.cnt_void) Void ,
         
      
       Sum(cr_count.cnt_trout) Tr_Out , Sum(cr_count.cnt_waste) Waste , Sum(cr_count.cnt_usage) Usage , 
       Sum(cr_count.cnt_cons) Cons , Sum(cr_count.cnt_sold) Sold ,
 (select cr_count.cnt_close from cr_count where cnt_date = '31-aug-13' and cnt_item = '20001'  and cnt_str_id = '2001') End_Bal,
     
 ((select cr_count.cnt_openc from cr_count where cnt_date = '01-aug-13' and cnt_item = '20001'  and cnt_str_id = '2001') +
 sum(cr_count.cnt_trin) + Sum(cr_count.cnt_rcv) + Sum(cr_count.cnt_dsd) + Sum(cr_count.cnt_prod) + Sum(cr_count.cnt_void)) Tot_In ,
 ( Sum(cr_count.cnt_trout) + Sum(cr_count.cnt_waste) + Sum(cr_count.cnt_usage)+Sum(cr_count.cnt_cons) + Sum(cr_count.cnt_sold) ) Tot_Out,
 
 ((select cr_count.cnt_openc from cr_count where cnt_date = '01-aug-13' and cnt_item = '20001'  and cnt_str_id = '2001') +
 sum(cr_count.cnt_trin) + Sum(cr_count.cnt_rcv) + Sum(cr_count.cnt_dsd) + Sum(cr_count.cnt_prod) + Sum(cr_count.cnt_void)) -
 ( Sum(cr_count.cnt_trout) + Sum(cr_count.cnt_waste) + Sum(cr_count.cnt_usage)+Sum(cr_count.cnt_cons) + Sum(cr_count.cnt_sold) ) Comp_Close ,
 
 (select cr_count.cnt_close from cr_count where cnt_date = '31-aug-13' and cnt_item = '20001'  and cnt_str_id = '2001')-
 (((select cr_count.cnt_openc from cr_count where cnt_date = '01-aug-13' and cnt_item = '20001'  and cnt_str_id = '2001') +
 sum(cr_count.cnt_trin) + Sum(cr_count.cnt_rcv) + Sum(cr_count.cnt_dsd) + Sum(cr_count.cnt_prod) + Sum(cr_count.cnt_void)) -
 ( Sum(cr_count.cnt_trout) + Sum(cr_count.cnt_waste) + Sum(cr_count.cnt_usage)+Sum(cr_count.cnt_cons) + Sum(cr_count.cnt_sold) )) Short_Over
     
FROM cr_count, cr_track, cr_uom
WHERE (    (cr_track.ti_id = cr_count.cnt_item)
        AND (cr_uom.uom_id = cr_track.ti_puom)
      ) 
      and cnt_date BETWEEN '01-aug-13' and '31-aug-13'
      and cnt_item ='20001'
      and cnt_str_id ='2001'
      group by cr_count.cnt_item,cr_track.ti_name_a, cr_uom.uom_code;

Open in new window



it works well
but i would like to reduce the expressions , for example
to use :
 ( Sum(cr_count.cnt_trout) + Sum(cr_count.cnt_waste) + Sum(cr_count.cnt_usage)+Sum(cr_count.cnt_cons) + Sum(cr_count.cnt_sold) ) Tot_Out,

Open in new window


 tot_out  again , i have to repeat the whole its formula again ?
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexander Eßer [Alex140181]Software DeveloperCommented:
This is untested, but may be a good starting point:

with count_openc as
 (select cr_count.cnt_openc
    from cr_count
   where cnt_date = '01-aug-13'
     and cnt_item = '20001'
     and cnt_str_id = '2001'),
count_close31 as
 (select cr_count.cnt_close
    from cr_count
   where cnt_date = '31-aug-13'
     and cnt_item = '20001'
     and cnt_str_id = '2001')
select cr_count.cnt_item,
       cr_track.ti_name_a,
       cr_uom.uom_code,
       (select cr_count.cnt_openc
          from cr_count
         where cnt_date = '01-aug-13'
           and cnt_item = '20001'
           and cnt_str_id = '2001') Open_Bal,
       sum(cr_count.cnt_trin) Tr_In,
       sum(cr_count.cnt_rcv) Recv,
       sum(cr_count.cnt_dsd) DSD,
       sum(cr_count.cnt_prod) Prod,
       sum(cr_count.cnt_void) Void,
       sum(cr_count.cnt_trout) Tr_Out,
       sum(cr_count.cnt_waste) Waste,
       sum(cr_count.cnt_usage) Usage,
       sum(cr_count.cnt_cons) Cons,
       sum(cr_count.cnt_sold) Sold,
       count_close31.cnt_close End_Bal,
       (count_openc.cnt_openc + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
       sum(cr_count.cnt_void)) Tot_In,
       (sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) + sum(cr_count.cnt_sold)) Tot_Out,
       (count_openc.cnt_openc + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
       sum(cr_count.cnt_void)) -
       (sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) + sum(cr_count.cnt_sold)) Comp_Close,
       count_close31.cnt_close -
       (((select cr_count.cnt_openc
            from cr_count
           where cnt_date = '01-aug-13'
             and cnt_item = '20001'
             and cnt_str_id = '2001') + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
       sum(cr_count.cnt_void)) -
       (sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) + sum(cr_count.cnt_sold))) Short_Over
  from count_openc,
       count_close31,
       cr_count,
       cr_track,
       cr_uom
 where ((cr_track.ti_id = cr_count.cnt_item) and (cr_uom.uom_id = cr_track.ti_puom))
   and cnt_date between '01-aug-13' and '31-aug-13'
   and cnt_item = '20001'
   and cnt_str_id = '2001'
 group by cr_count.cnt_item,
          cr_track.ti_name_a,
          cr_uom.uom_code;

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with above: using CTE is the best way to solve this.
0
NiceMan331Author Commented:
error , not group by
 count_close31.cnt_close End_Bal,

but any how , this not solving my issue
cause i would like to add another more expressions
i dislike to repeat again the whole statement every time
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Alexander Eßer [Alex140181]Software DeveloperCommented:
i dislike to repeat again the whole statement every time

Then use "WITH", this is what CTE is for/does...

or clarify your problem a bit more detailed please
0
PortletPaulfreelancerCommented:
I'd like more information.
Q1. what is the data type of cr_count.cnt_date ?
Q2, how is it you can use strings like  '31-aug-13' as a reliable date?

There are several subqueries in the selection list, e.g.
      , (
                SELECT
                        cr_count.cnt_openc
                FROM cr_count
                WHERE cnt_date = '01-aug-13'
                        AND cnt_item = '20001'
                        AND cnt_str_id = '2001'
        )                                                                                                                                   
        Open_Bal

Open in new window

Q3. Will this (and the other similar subquey) return just a single value - every time?
          (They will fail if they return > 1 value)
Q4. Why is that subquery for just one date and not the whole of August?

Q5. Why not use ANSI join syntax? e.g.

FROM cr_count AS C
INNER JOIN cr_track AS T ON cr_count.cnt_item = cr_track.ti_id
INNER JOIN cr_uom   AS U ON cr_track.ti_puom = cr_uom.uom_id
WHERE (     c.cnt_date >= to_date('2013-08-01', 'YYYY-MM-DD')
        AND c.cnt_date  < to_date('2013-09-01', 'YYYY-MM-DD') ) 

Open in new window

and avoiding use 'between' is shown above also
0
PortletPaulfreelancerCommented:
>>but any how , this not solving my issue
>>i dislike to repeat again the whole statement every time
that is exactly what Alex has solved, do it once (in CTE) then re-use it.

CTE = Common Table Expression, Oracle often refers to it as "Sub Query Factorization", but they are the same thing. Most of us (it appers) use CTE
they are "named" queries, that are reusable in the main query.

>>i would like to add another more expressions
what are they?

{+edit for spelling, sorry}
0
NiceMan331Author Commented:
ok  alex , let me try
but what about that error

error , not group by
at this statement

 count_close31.cnt_close End_Bal,



partuell
i would like to add :
having  short_over ( which is last expression in my first post )  > +1 or < -1
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
Unfortunately I cannot test this...

with count_openc as
 (select cr_count.cnt_openc
    from cr_count
   where cnt_date = '01-aug-13'
     and cnt_item = '20001'
     and cnt_str_id = '2001'),
count_close31 as
 (select cr_count.cnt_close
    from cr_count
   where cnt_date = '31-aug-13'
     and cnt_item = '20001'
     and cnt_str_id = '2001')
select cr_count.cnt_item,
       cr_track.ti_name_a,
       cr_uom.uom_code,
       max(count_openc.cnt_openc) Open_Bal,
       sum(cr_count.cnt_trin) Tr_In,
       sum(cr_count.cnt_rcv) Recv,
       sum(cr_count.cnt_dsd) DSD,
       sum(cr_count.cnt_prod) Prod,
       sum(cr_count.cnt_void) Void,
       sum(cr_count.cnt_trout) Tr_Out,
       sum(cr_count.cnt_waste) Waste,
       sum(cr_count.cnt_usage) Usage,
       sum(cr_count.cnt_cons) Cons,
       sum(cr_count.cnt_sold) Sold,
       max(count_close31.cnt_close) End_Bal,
       (max(count_openc.cnt_openc) + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
       sum(cr_count.cnt_void)) Tot_In,
       (sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) + sum(cr_count.cnt_sold)) Tot_Out,
       (max(count_openc.cnt_openc) + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
       sum(cr_count.cnt_void)) -
       (sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) + sum(cr_count.cnt_sold)) Comp_Close,
       max(count_close31.cnt_close) -
       ((max(count_openc.cnt_openc) + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
        sum(cr_count.cnt_void)) -
        (sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) + sum(cr_count.cnt_sold))) Short_Over
  from count_openc,
       count_close31,
       cr_count,
       cr_track,
       cr_uom
 where ((cr_track.ti_id = cr_count.cnt_item) and (cr_uom.uom_id = cr_track.ti_puom))
   and cnt_date between '01-aug-13' and '31-aug-13'
   and cnt_item = '20001'
   and cnt_str_id = '2001'
 group by cr_count.cnt_item,
          cr_track.ti_name_a,
          cr_uom.uom_code
having ((count_openc.cnt_openc + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
          sum(cr_count.cnt_void)) -(sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) +
                             sum(cr_count.cnt_sold))) not in(-1, 0, 1);

Open in new window

0
NiceMan331Author Commented:
again same error at this line

having ((count_openc.cnt_openc + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
          sum(cr_count.cnt_void)) -(sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) +
                             sum(cr_count.cnt_sold))) not in(-1, 0, 1);

Open in new window

0
Alexander Eßer [Alex140181]Software DeveloperCommented:
having ((max(count_openc.cnt_openc) + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
          sum(cr_count.cnt_void)) -(sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) +
                             sum(cr_count.cnt_sold))) not in(-1, 0, 1);
                                           
0
NiceMan331Author Commented:
yes it works
0
NiceMan331Author Commented:
it is ok now , but let me wait for partuel if he has a shorter solution


Q1. what is the data type of cr_count.cnt_date ?
         Date
Q2, how is it you can use strings like  '31-aug-13' as a reliable date?
       each one will bring calculated balance at several date
Q3. Will this (and the other similar subquey) return just a single value - every time?
          (They will fail if they return > 1 value)
        yes , it should
Q4. Why is that subquery for just one date and not the whole of August?
      the transaction will be for the whole month of august , but the specific formula of beg    & end balance will bring the specific data

Q5. Why not use ANSI join syntax? e.g.
     it could be , why not
0
PortletPaulfreelancerCommented:
NiceMan321, you are too nice to me.
No need to wait, Alex140181 is providing great advice.

Using the CTE is the best advice I could give (as it allows 'do it once' and 're-use')

i.e. I believe you have the answer already.

-------------- No points please ------------
a2. I strongly suggest you use YYYY-MM-DD instead of YY-Mon-DD
       OR, use the to_date() function
             to_date('31-aug-13','DD-MON-YY')

a3. "should" isn't strong enough. Force it to return just one value with rownum (line 8)
      , (
                SELECT
                        cr_count.cnt_openc
                FROM cr_count
                WHERE cnt_date = '01-aug-13'
                        AND cnt_item = '20001'
                        AND cnt_str_id = '2001'
                        AND rownum = 1
        )   as  Open_Bal

Open in new window

A5. please do use ANSI syntax, once you get used to it you will prefer it
0
NiceMan331Author Commented:
wellcome back my best freind
you often provides more than what i ask

a2. I strongly suggest you use YYYY-MM-DD instead of YY-Mon-DD

i really love to use it , but  sql developer dosent allow me to use else of YY-MMM-DD , i don't know how to change its setting

A5. please do use ANSI syntax, once you get used to it you will prefer it

what is the different between this :

FROM cr_count AS C
INNER JOIN cr_track AS T ON cr_count.cnt_item = cr_track.ti_id
INNER JOIN cr_uom   AS U ON cr_track.ti_puom = cr_uom.uom_id
WHERE (     c.cnt_date >= to_date('2013-08-01', 'YYYY-MM-DD')
        AND c.cnt_date  < to_date('2013-09-01', 'YYYY-MM-DD') ) 

Open in new window


and this :

FROM cr_count, cr_track, cr_uom
WHERE (    (cr_track.ti_id = cr_count.cnt_item)
        AND (cr_uom.uom_id = cr_track.ti_puom)
      ) 

Open in new window

0
PortletPaulfreelancerCommented:
-- before
FROM cr_count, cr_track, cr_uom

-- after
FROM cr_count AS C
INNER JOIN cr_track AS T ON cr_count.cnt_item = cr_track.ti_id
INNER JOIN cr_uom   AS U ON cr_track.ti_puom = cr_uom.uom_id

Open in new window

The syntax difference is that the joining logic is contained within the FROM clause  (and the FROM clause is evaluated before the WHERE clause).

To me, the real difference/advantage is:
a. The WHERE clause becomes easier to understand
b. as more people become mostly familiar with ANSI syntax, the code will be easier to maintain in the longer term
c. ANSI syntax is far easier to use for advanced join conditions
d. it is almost impossible for "miss a join" using ANSI syntax, but it is easy to do in older syntax

Compare these 2 where clauses:
-- before
 where ((cr_track.ti_id = cr_count.cnt_item) and (cr_uom.uom_id = cr_track.ti_puom))
   and cnt_date between '01-aug-13' and '31-aug-13'
   and cnt_item = '20001'
   and cnt_str_id = '2001'
  

-- after
 where cnt_date between '01-aug-13' and '31-aug-13'
   and cnt_item = '20001'
   and cnt_str_id = '2001'
 

Open in new window

0
NiceMan331Author Commented:
thanx for the explanation
FROM cr_count AS C
INNER JOIN cr_track AS T ON cr_count.cnt_item = cr_track.ti_id
INNER JOIN cr_uom   AS U ON cr_track.ti_puom = cr_uom.uom_id

Open in new window


i could use it like this :

FROM cr_count AS C
INNER JOIN cr_track AS T ON c.cnt_item = t.ti_id
INNER JOIN cr_uom   AS U ON t.ti_puom = u.uom_id

Open in new window


remaining one thing

a2. I strongly suggest you use YYYY-MM-DD instead of YY-Mon-DD
i really love to use it , but  sql developer dosent allow me to use else of YY-MMM-DD , i don't know how to change its setting

do u know how to change date format in sql developer
0
PortletPaulfreelancerCommented:
before going too far, please note we are "way off-topic" and that your question was handled by Alex. If the question has been answered it should be closed accordingly.

The other topics I have raised are purely advice/notes that you don't have to adopt.

You should be able to use 'YYYYMMDD' in your sql queries without changing date defaults and I'd rather not go too far into this here. I suggest you read this first:
The ultimate guide to the datetime datatypes (Tibor Karaszi)
0
NiceMan331Author Commented:
the solution of alex is correct , but still i'm looking for an answer :
is there any way to use the alias , or i should repeat it
for example :
select  sum(debit) deb , sum(credit) cred
from my_table;

if i would like to have the difference between deb & cred  ,
is there any way to use it like this :

select  sum(debit) deb , sum(credit) cred , (deb-cred) Diff
from my_table;

instead of say :

select  sum(debit) deb , sum(credit) cred , (sum(debit) - sum(credit)) Diff
from my_table;
0
PortletPaulfreelancerCommented:
well, you can "nest" a substantial part of the query perhaps, then reference the summed values by their aliases - like this - but it's totally untested
WITH
        count_openc
        AS (
                        SELECT
                                cr_count.cnt_openc
                        FROM cr_count
                        WHERE cnt_date = '01-aug-13'
                                AND cnt_item = '20001'
                                AND cnt_str_id = '2001'
                                and rownum = 1
                )
,
        count_close31
        AS (
                        SELECT
                                cr_count.cnt_close
                        FROM cr_count
                        WHERE cnt_date = '31-aug-13'
                                AND cnt_item = '20001'
                                AND cnt_str_id = '2001'
                                and rownum = 1
                )
SELECT
          cc.cr_count.cnt_item
        , cc.cr_track.ti_name_a
        , cc.cr_uom.uom_code
        , cc.Tr_In
        , cc.Recv
        , cc.DSD
        , cc.Prod
        , cc.Void
        , cc.Tr_Out
        , cc.Waste
        , cc.Usage
        , cc.Cons
        , cc.Sold
        , (count_openc.cnt_openc)+ cc.Tr_In + cc.Recv 
          + cc.DSD + cc.Prod + cc.Void)                        AS Tot_In
        , (cc.Tr_Out + cc.Waste + cc.Usage
          + cc.Cons + cc.Sold)                                 AS Tot_Out
        , (count_openc.cnt_openc)+ cc.Tr_In + cc.Recv
          + cc.DSD + cc.Prod + cc.Void)
          - (cc.Tr_Out + cc.Waste + cc.Usage
           + cc.Cons + cc.Sold)                              AS Comp_Close
        , count_close31.cnt_close
          -  ((count_openc.cnt_openc + cc.Tr_In + cc.Recv
               + cc.DSD + cc.Prod + cc.Void)
          -  (cc.Tr_Out + cc.Waste + cc.Usage
            + cc.Cons + cc.Sold))                            AS Short_Over
FROM (
                SELECT
                        cr_count.cnt_item
                      , cr_track.ti_name_a
                      , cr_uom.uom_code
                      , SUM(cr_count.cnt_trin)    AS Tr_In
                      , SUM(cr_count.cnt_rcv)     AS Recv
                      , SUM(cr_count.cnt_dsd)     AS DSD
                      , SUM(cr_count.cnt_prod)    AS Prod
                      , SUM(cr_count.cnt_void)    AS Void
                      , SUM(cr_count.cnt_trout)   AS Tr_Out
                      , SUM(cr_count.cnt_waste)   AS Waste
                      , SUM(cr_count.cnt_usage)   AS Usage
                      , SUM(cr_count.cnt_cons)    AS Cons
                      , SUM(cr_count.cnt_sold)    AS Sold
                FROM cr_count
                INNER JOIN cr_track ON cr_track.ti_id = cr_count.cnt_item
                INNER JOIN cr_uom ON cr_uom.uom_id = cr_track.ti_puom
                WHERE cnt_date BETWEEN '01-aug-13' AND '31-aug-13'
                        AND cnt_item = '20001'
                        AND cnt_str_id = '2001'
                GROUP BY
                         cr_count.cnt_item
                       , cr_track.ti_name_a
                       , cr_uom.uom_code
                HAVING ((count_openc.cnt_openc + SUM(cr_count.cnt_trin) + SUM(cr_count.cnt_rcv) + SUM(cr_count.cnt_dsd) + SUM(cr_count.cnt_prod) +
                SUM(cr_count.cnt_void)) - (SUM(cr_count.cnt_trout) + SUM(cr_count.cnt_waste) + SUM(cr_count.cnt_usage) + SUM(cr_count.cnt_cons) +
                SUM(cr_count.cnt_sold))) NOT IN (-1, 0, 1)
    ) CC
CROSS JOIN count_openc   /* MAX OF 1 RECORD */
CROSS JOIN count_close31 /* MAX OF 1 RECORD */

Open in new window

0
NiceMan331Author Commented:
it gives error at line 37
FROM keyword not found
0
PortletPaulfreelancerCommented:
line 37
line 41

change
 , (count_openc.cnt_openc)+

to

 , (count_openc.cnt_openc +

i.e. remove 2x ")" from the query

re-try
0
NiceMan331Author Commented:
error line 75

count_openc.cnt_openc

invalid identifier
0
PortletPaulfreelancerCommented:
Please try this. I have moved the cross joins to line 70 & 71, also note that if either CTEs results in NULL nothing will be returned. As I am unable to test any of this please don't keep sending error messages and waiting - I may not be online &  it is advised you attempt corrections yourself.
WITH
        count_openc
        AS (
                        SELECT
                                cr_count.cnt_openc
                        FROM cr_count
                        WHERE cnt_date = '01-aug-13'
                                AND cnt_item = '20001'
                                AND cnt_str_id = '2001'
                                and rownum = 1
                )
,
        count_close31
        AS (
                        SELECT
                                cr_count.cnt_close
                        FROM cr_count
                        WHERE cnt_date = '31-aug-13'
                                AND cnt_item = '20001'
                                AND cnt_str_id = '2001'
                                and rownum = 1
                )
SELECT
          cc.cr_count.cnt_item
        , cc.cr_track.ti_name_a
        , cc.cr_uom.uom_code
        , cc.Tr_In
        , cc.Recv
        , cc.DSD
        , cc.Prod
        , cc.Void
        , cc.Tr_Out
        , cc.Waste
        , cc.Usage
        , cc.Cons
        , cc.Sold
        , (cc.cnt_openc + cc.Tr_In + cc.Recv 
          + cc.DSD + cc.Prod + cc.Void)                        AS Tot_In
        , (cc.Tr_Out + cc.Waste + cc.Usage
          + cc.Cons + cc.Sold)                                 AS Tot_Out
        , (cc.cnt_openc + cc.Tr_In + cc.Recv
          + cc.DSD + cc.Prod + cc.Void)
          - (cc.Tr_Out + cc.Waste + cc.Usage
           + cc.Cons + cc.Sold)                              AS Comp_Close
        , count_close31.cnt_close
          -  ((cc.cnt_openc + cc.Tr_In + cc.Recv
               + cc.DSD + cc.Prod + cc.Void)
          -  (cc.Tr_Out + cc.Waste + cc.Usage
            + cc.Cons + cc.Sold))                            AS Short_Over
FROM (
                SELECT
                        cr_count.cnt_item
                      , cr_track.ti_name_a
                      , cr_uom.uom_code
                      , MAX(count_openc.cnt_openc)   AS cnt_openc
                      , MAX(count_close31.cnt_close) AS cnt_close
                      , SUM(cr_count.cnt_trin)       AS Tr_In
                      , SUM(cr_count.cnt_rcv)        AS Recv
                      , SUM(cr_count.cnt_dsd)        AS DSD
                      , SUM(cr_count.cnt_prod)       AS Prod
                      , SUM(cr_count.cnt_void)       AS Void
                      , SUM(cr_count.cnt_trout)      AS Tr_Out
                      , SUM(cr_count.cnt_waste)      AS Waste
                      , SUM(cr_count.cnt_usage)      AS Usage
                      , SUM(cr_count.cnt_cons)       AS Cons
                      , SUM(cr_count.cnt_sold)       AS Sold
                FROM cr_count
                INNER JOIN cr_track ON cr_track.ti_id = cr_count.cnt_item
                INNER JOIN cr_uom ON cr_uom.uom_id = cr_track.ti_puom
                CROSS JOIN count_openc   /* MAX OF 1 RECORD + WARNING, if null everything NULL */
                CROSS JOIN count_close31 /* MAX OF 1 RECORD + WARNING, if null everything NULL */   
                WHERE cnt_date BETWEEN '01-aug-13' AND '31-aug-13'
                        AND cnt_item = '20001'
                        AND cnt_str_id = '2001'
                GROUP BY
                         cr_count.cnt_item
                       , cr_track.ti_name_a
                       , cr_uom.uom_code
                HAVING ((count_openc.cnt_openc + SUM(cr_count.cnt_trin) + SUM(cr_count.cnt_rcv) + SUM(cr_count.cnt_dsd) + SUM(cr_count.cnt_prod) +
                SUM(cr_count.cnt_void)) - (SUM(cr_count.cnt_trout) + SUM(cr_count.cnt_waste) + SUM(cr_count.cnt_usage) + SUM(cr_count.cnt_cons) +
                SUM(cr_count.cnt_sold))) NOT IN (-1, 0, 1)
    ) CC
;

Open in new window

I have tested it for syntax errors such as missing or too many parentheses - but that is all I can really do I'm afraid.

nb: Take care to review the arithmetic, I cannot be certain that it is 100% - but  did try to be careful.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
ok dear
thanx any how
i will do to reconsile it
thanx
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
Nice work, Paul. Thought the points would have been distributed amongst us...
0
NiceMan331Author Commented:
i'm so sorry alex
it is my mistake
how do i repair it ?
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
No need to repair anything.  But keep that in mind for the next time. ..
0
NiceMan331Author Commented:
i'm so sorry
i really confused ,because the question solved time ago , i just noticed that it still open , i review who answer it by quickly , i found the last post of paul
ii apologize again
i hope you answer my next questions , thanx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.