Home » SQL & PL/SQL » SQL & PL/SQL » GROUP BY COST CENTER
GROUP BY COST CENTER [message #659215] |
Mon, 09 January 2017 11:34 |
|
vharish006
Messages: 70 Registered: August 2015 Location: Chicago
|
Member |
|
|
How do i include GROUP BY in below .I want to GROUP BY COST CENTER.
SELECT t."Fund" ,
t."Cost Center" as "CostCenter",
DECODE(t."Cost Center" ,'8620',DECODE(t."Location",'0001','8620',t."Location") ,t."Location" ) "Location",
t."Account" ,
t."Program" ,
t."Categories" ,
t."Session" ,
t."Project",
DECODE(SUBSTR(t."Account",1,1) ,'6', t."Data" ,NULL ) as "Debit",
DECODE(SUBSTR(t."Account",1,1) ,'4', t."Data" ,'3', t."Data",NULL ) as "Credit",
('JAN-'||SUBSTR(t.YEAR,-2)) as "Period",
('20'||SUBSTR(t.YEAR,-2)||' Upload') as "Line Description"
FROM
(SELECT SUBSTR(FUND, -3) "Fund" ,
SUBSTR(ENTITY, -4) "Cost Center" ,
DECODE(SUBSTR(NVL(LOCATION,'LOC_0001'),-4) ,'0001',NVL(
(SELECT b.flex_value segment_value
FROM fnd_flex_value_sets a,
fnd_flex_values_vl b
WHERE a.flex_value_set_id = b.flex_value_set_id
AND b.enabled_flag = 'Y'
AND a.flex_value_set_name = 'FAMIS_Location'
AND b.flex_value =SUBSTR(stg.ENTITY,-4)
AND ROWNUM =1
) ,'0001') ,SUBSTR(NVL(LOCATION,'LOC_0001'),-4) ) "Location" ,
ACCOUNT "Account" ,
'00001' "Program" ,
'01' "Categories" ,
SUBSTR(GL_SESSION,-2) "Session" ,
'00001' "Project" ,
UPPER(Period) "Period" ,
BUDGET "Data" ,
DECODE(UPPER(Period),'JAN',1,'FEB',2,'MAR',3,'APR',4,'MAY',5,'JUN',6,'JUL',7,'AUG',8,'SEP',9,'OCT',10,'NOV',11,'DEC',12,'YEARTOTAL',0 ,13) AS "MON_NUM" ,
SCENARIO,
VERSION,
YEAR
FROM CPD_GL_FROM_HYP_ADBF_STG stg--CPD_GL_FROM_HYP_CSCV_STG stg
WHERE 1 =1
AND UPPER(Period)='YEARTOTAL'
ORDER BY 1,2,3,4,5,6,7,8,11
)t
WHERE 1=1
--moderator update: I've added [code] tags, please do so yourself in future.
[Updated on: Mon, 09 January 2017 11:39] by Moderator Report message to a moderator
|
|
|
Re: GROUP BY COST CENTER [message #659216 is a reply to message #659215] |
Mon, 09 January 2017 11:41 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Are you familiar with using aggregations? The rule is simple: every column you project must either be aggregated (with, for example, MAX or COUNT) or be included in the GROUP BY clause.
|
|
|
Re: GROUP BY COST CENTER [message #659217 is a reply to message #659215] |
Mon, 09 January 2017 11:50 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
From your previous topics:
BlackSwan wrote on Mon, 10 August 2015 19:12Welcome to this forum.
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
...
Barbara formatted your post in your next topic and you still continued to post not formatted.
So once more:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
[Updated on: Mon, 09 January 2017 11:51] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:39:08 CDT 2024
|