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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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

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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
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!

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
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
Query Syntax

From novice to tech pro — start learning today.