Skip to Main Content
Forums

SQL & PL/SQL

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.

aggregate functions not allowed in where clause

RobeenNov 17 2021 — edited Nov 17 2021

Oracle DB 12.1.0.2
AIX
Hello Team,
can you please advise how I can use aggregate functions with WHERE clause?

SELECT DS_SUB.adjusted_cost FROM FA.FA_DEPRN_SUMMARY DS_SUB,FA.FA_DEPRN_DETAIL DD,FA.FA_DEPRN_SUMMARY DS 
HAVING DS.PERIOD_COUNTER =NVL (MIN (DS_SUB.PERIOD_COUNTER), DD.PERIOD_COUNTER)
GROUP BY DS_SUB.adjusted_cost,DS_SUB.PERIOD_COUNTER,DD.PERIOD_COUNTER;
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:   
*Action:

Please find attached sample data:

Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,17503.96);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,423294.71);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,184594.53);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,30623.15);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,95658.2);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,9202.08);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,152863.5);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,88691.1);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,36324);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,222989);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,109947.5);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,6410.93);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,105083.57);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,222989);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,235253.4);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,11583.32);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,11583.32);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,4136.9);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,4136.9);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,9928.56);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,9928.56);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,140355.94);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,87722.46);
Insert into FA_DEPRN_SUMMARY (PERIOD_COUNTER,ADJUSTED_COST) values (24139,298256.36);
Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Insert into FA_DEPRN_DETAIL (PERIOD_COUNTER) values (24139);

Thanks,
Roshan
This post has been answered by Frank Kulash on Nov 17 2021
Jump to Answer

Comments

Gaz in Oz

Please MOVE your post to the correct forum by clicking on "Move" under ACTIONS in the top right of the post.

You will get specific APEX help from there on how to properly export stuff.

Mary Bagir

Hi Gaz,

I know how export apex application from apex builder to include private reports. Apex Builder default export not included private reports, but it can be change by check box include before start export. I thought something like this in sqlcl apex export command exists too.

Thanks Mary

1 person found this helpful
Gaz in Oz
Answer

Ah, ok so the sqlcl APEX options (in 18.1.0.0) are:

SQL> show version
Oracle SQLDeveloper Command-Line (SQLcl) version: 18.1.0.0
SQL> apex export
    -applicationid:    ID for application to be exported
    -workspaceid:      Workspace ID for which all applications to be exported or the workspace to be exported
    -instance:         Export all applications
    -expWorkspace:     Export workspace identified by -workspaceid or all workspaces if -workspaceid not specified
    -expMinimal:       Only export workspace definition, users, and groups
    -expFiles:         Export all workspace files identified by -workspaceid
    -skipExportDate:   Exclude export date from application export files
    -expPubReports:    Export all user saved public interactive reports
    -expSavedReports:  Export all user saved interactive reports
    -expIRNotif:       Export all interactive report notifications
    -expTranslations:  Export the translation mappings and all text from the translation repository
    -expFeedback:      Export team development feedback for all workspaces or identified by -workspaceid to development or deployment
    -expTeamdevdata:   Export team development data for all workspaces or identified by -workspaceid
    -deploymentSystem: Deployment system for exported feedback
    -expFeedbackSince: Export team development feedback since date in the format YYYYMMDD
    -expOriginalIds:   If specified, the application export will emit ids as they were when the application was imported
    -split:            Split the exported file
    -splitFlat:        Split with no directory strucure
    -splitUpdate:      Generate update.sql file while splitting
    -splitNoCheckSum:  Overwrite all files
 Application Example:            apex export -applicationid 31500
 Workspace Example:              apex export -workspaceid 9999
  Instance Example:               apex export -instance
 Export All Workspaces Example:  apex export -expWorkspace
 Export Feedback to development environment:
         apex export -workspaceid 9999 -expFeedback
 Export Feedback to deployment environment EA2 since 20100308:
         apex export -workspaceid 9999 -expFeedback -deploymentSystem EA2 -expFeedbackSince 20100308
SQL>

...do an "apex export" via your version of sqlcl to see what options are available for that version.

Marked as Answer by Mary Bagir · Sep 27 2020
1 - 3

Post Details

Added on Nov 17 2021
18 comments
3,635 views
2 mentions