Oracle views- Is there a way to use a where condition and group by clause together within a view?

Is there a way to use a where condition and group by clause together within a view?

my code :
create or replace force view cu.ac_commerical_vw
(
inqu_number
,co_item_number
,com_code
,month
,weight_um
,weight
,org_weight
,weight_release
,weight_ship
)
BEQUEATH DEFINER
AS
select aci.inqu_number
          ,aci.co_item_number
          ,aci.com_code
         ,trunc(month, 'MM')
         ,aci.weight_um
         ,sum(aci.weight)
        ,sum(aci.org_weight)
       , nvl(asi.weight, 0)
      *vc_fgk_utilities.um_convert(asi.weight_um,vc_fgk_utilities.default_comp_um
(asi.com_code, vc_fgk_const.a_weight)) weigth_rel ,nvl(iasi.weight, 0)
*vc_fgk_utilities.um_convert(iaci.weight_um, vc_fgk_utilities.default_comp_um
(asi.com_code, vc_fgk_const.a_weight)) weight_ship
FROM cu.mc_items                                              aci
            cu.ss_items                                                  asi
            cu.inv_lines                                               iasi
WHERE aci.inqu_number = asi.inqu_number
and aci.co_item_number = asi.co_item_number
and TO_CHAR(aci.due_date, 'MM/YYYY') =  TO_CHAR(asi.due_date, 'MM/YYYY')
GROUP BY aci.inqu_number, asi.co_item_number, aci.com_code, trunc(month, 'MM'), aci.weight_um;

create or replace public synonym ac_commerical_vw for cu.ac_commerical_vw;
grant select on ac_commerical_vw to discover_role_all;
grant selet on ac_commerical_vw to report_role;
grant delete, insert, select, update, debug on ac_commerical_vw to ac_cll_role;
Tay JohnsonAsked:
Who is Participating?
 
sdstuberCommented:
yes it is possible.

Your view doesn't execute because you're missing commas between the tables in the FROM clause

change this

FROM cu.mc_items                                              aci
            cu.ss_items                                                  asi
            cu.inv_lines                                               iasi

Open in new window


to this

 FROM cu.mc_items aci, cu.ss_items asi, cu.inv_lines iasi

Open in new window


you can split them onto multiple lines if you want just remember to include the commas after "aci" and "asi"


or rewrite your joins to use INNER JOIN ON syntax
0
 
Ryan ChongCommented:
Is there a way to use a where condition and group by clause together within a view?
sure why not?

but wondering if you want to do the condition before or after the group by?

if you want to do it before the group by clause, you can specify it when you select your tables, like:

FROM (select * from cu.mc_items where yourCondition = 1)    aci , 
            (select * from cu.ss_items  where otherCondition = 'blabla')  asi ,
            cu.inv_lines  iasi

Open in new window


if you want to do it after the group by clause, you can use having clause.

Oracle / PLSQL: HAVING Clause
https://www.techonthenet.com/oracle/having.php
0
 
Tay JohnsonAuthor Commented:
I want to do the condition before the group by clause so within the FROM clause write 3 subqueries then perform a group by clauses.
Can you explain the subqueries in a little more detail. Maybe I created my where clauses wrong an it did not work.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ryan ChongCommented:
check some explanation here:

SQL Subqueries
https://www.w3resource.com/sql/subqueries/understanding-sql-subqueries.php

the reason why it's not working with you could due to wrong joining of conditions or no data appears with the conditions. try to break your complex SQL into smaller parts for debugging and find the root cause of the issue.
0
 
Tay JohnsonAuthor Commented:
I rewrote my queries above in several different variations and it still does not work. I am trying to write this query which has a SELECT list with multiple columns, multiple group functions, FROM clause using multiple tables and use the GROUP BY clause. Can you provide me with a link to an example similar of this magnitude using SQL query in the ANSI-89 standard. I understand subqueries, group functions, group by clause, from clause but I can not get it to work.  

Here is one example where I broke down the query.

select aci.inqu_number
          ,aci.co_item_number
          ,aci.com_code
         ,trunc(asi.month, 'MM')
         ,aci.weight_um
         ,sum(aci.weight)
        , sum(nvl(vc_fgk_utilities.aci_weight(asi.com_code, asi.inqu_number),0))weight_release
        , sum(nvl(dbk_sl_rts.si_itm_shp_weight(asi.com_code, asi.inqu_number, 'LG'),0))weight_ship
FROM (SELECT  aci.inqu_number, asi.co_item_number, aci.com_code, asi.month, aci.weight_um
              FROM cu.mc_items  aci,  cu.ss_items  asi
              WHERE aci.inqu_number(+) = asi.inqu_number) aci
             
GROUP BY aci.inqu_number, asi.co_item_number, aci.com_code, trunc(asi.month, 'MM'), aci.weight_um;

it complied but will not run. This is the same query but smaller. Can you provide me with a link to an example similar of this magnitude using SQL query in the ANSI-89 standard. I'm not sure what I'm doing wrong. Are my group functions the problem? Is it the From clause? I need to know more advance view using multiple tables, ect.... Thank you in advance
0
 
Tay JohnsonAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.