select customer_no, account_no, sum(accounted_dr), sum(accounted_cr), to_char(effective_date, 'YYYY-MM') as "Effective Months and Years"
from GL_JOURNAL_LINES
group by account_no, customer_no, to_char(effective_date, 'YYYY-MM')
order by to_char(effective_date, 'YYYY-MM') asc
The Table:
" ";"CUSTOMER_NO";"ACCOUNT_NO";"SUM(ACCOUNTED_DR)";"SUM(ACCOUNTED_CR)";"Effective Months and Years"
"1";"5010011";"19";"141,23";"0";"2021-01"
"2";"5010047";"1";"0";"603";"2021-01"
"3";"5010047";"3";"8436459,22";"10194636,54";"2021-01"
"4";"5010047";"6";"0,95";"0";"2021-01"
"5";"5010047";"17";"3697,88";"3000";"2021-01"
"6";"5010047";"26";"0,95";"0";"2021-01"
"7";"5010047";"31";"2000000";"3439585,07";"2021-01"
"8";"5010047";"155";"0,96";"0,13";"2021-01"
"9";"5010047";"160";"8,36";"0";"2021-01"
"10";"5010047";"165";"2,36";"0";"2021-01"
"11";"5010081";"2";"8287023,64";"8109857,53";"2021-01"
"12";"5010081";"6";"7406954,3";"10900099,1";"2021-01"
"13";"5010081";"9";"0";"135000";"2021-01"
"14";"5010081";"23";"458168";"17000";"2021-01"
"15";"5023913";"7";"3788,03";"5611,5";"2021-01"
"16";"5023913";"24";"4156,4";"4151,4";"2021-01"
"17";"5023913";"26";"3055,4";"993,06";"2021-01"
"18";"5023913";"27";"3055,4";"993,06";"2021-01"
"19";"5023913";"29";"9,93";"30,5";"2021-01"
"20";"5010011";"19";"16";"0";"2021-02"
"21";"5010011";"27";"130500";"0";"2021-02"
"22";"5010047";"1";"0";"25";"2021-02"
"23";"5010047";"2";"116227,01";"6209,1";"2021-02"
"24";"5010047";"3";"7435295,97";"556214,25";"2021-02"
"25";"5010047";"4";"7735,98";"0";"2021-02"
"26";"5010047";"6";"0,95";"0";"2021-02"
"27";"5010047";"17";"3388,76";"0";"2021-02"
"28";"5010047";"26";"169,58";"0";"2021-02"
"29";"5010047";"31";"0";"345670,02";"2021-02"
"30";"5010047";"64";"16800";"0";"2021-02"
"31";"5010047";"154";"1200";"0";"2021-02"
"32";"5010047";"155";"0,22";"1,05";"2021-02"
"33";"5010047";"159";"10000";"0";"2021-02"
"34";"5010047";"160";"0";"8,38";"2021-02"
"35";"5010047";"165";"2,36";"2,33";"2021-02"
"36";"5010081";"2";"4808378,18";"3956792,08";"2021-02"
"37";"5010081";"6";"3817981,11";"0";"2021-02"
"38";"5010081";"9";"0";"135000";"2021-02"
"39";"5010081";"23";"60960";"206380";"2021-02"
"40";"5023913";"7";"5640,14";"5641,5";"2021-02"
"41";"5023913";"24";"5958,4";"5963,4";"2021-02"
"42";"5023913";"26";"2958,4";"2985,37";"2021-02"
"43";"5023913";"27";"2958,4";"2985,37";"2021-02"
"44";"5023913";"29";"29,86";"29,64";"2021-02"
"45";"5010011";"19";"0";"1,74";"2021-03"
"46";"5010011";"21";"1,74";"0";"2021-03"
"47";"5010047";"1";"0";"25";"2021-03"
"48";"5010047";"2";"0";"6599,34";"2021-03"
"49";"5010047";"3";"20656118,36";"8102019,63";"2021-03"
"50";"5010047";"4";"2216630";"0";"2021-03"
"51";"5010047";"5";"1800";"0";"2021-03"
"52";"5010047";"6";"0,95";"0";"2021-03"
"53";"5010047";"17";"3872,46";"6000";"2021-03"
"54";"5010047";"26";"282,27";"0";"2021-03"
"55";"5010047";"31";"4181565,82";"651830,91";"2021-03"
"56";"5010047";"32";"15000";"0";"2021-03"
"57";"5010047";"165";"2,36";"2,33";"2021-03"
"58";"5010081";"2";"6275422,32";"3387645,75";"2021-03"
"59";"5010081";"6";"141666,66";"0";"2021-03"
"60";"5010081";"9";"293282";"0";"2021-03"
"61";"5010081";"22";"141666,66";"0";"2021-03"
"62";"5010081";"23";"465342,03";"511274";"2021-03"
"63";"5023913";"7";"6222,03";"3111,5";"2021-03"
"64";"5023913";"24";"3630,58";"3630,58";"2021-03"
"65";"5023913";"26";"630,58";"2958,4";"2021-03"
"66";"5023913";"27";"630,58";"2958,4";"2021-03"
"67";"5023913";"29";"29,58";"6,31";"2021-03"
"68";"5010047";"1";"0";"25";"2021-04"
"69";"5010047";"2";"0";"6021,6";"2021-04"
"70";"5010047";"3";"2512720,68";"3713671,2";"2021-04"
"71";"5010047";"4";"3890599,59";"494829,12";"2021-04"
"72";"5010047";"6";"0,95";"0";"2021-04"
"73";"5010047";"17";"9150,79";"9000";"2021-04"
"74";"5010047";"23";"285,09";"576,45";"2021-04"
"75";"5010047";"26";"1,9";"0";"2021-04"
"76";"5010047";"31";"200100";"374805,44";"2021-04"
"77";"5010047";"64";"4185";"0";"2021-04"
"78";"5010047";"97";"4185";"0";"2021-04"
"79";"5010047";"164";"2790";"0";"2021-04"
"80";"5010047";"165";"0,86";"3,29";"2021-04"
"81";"5010081";"2";"3264298,78";"3099755,45";"2021-04"
"82";"5010081";"6";"141666,66";"18948,97";"2021-04"
"83";"5010081";"9";"376172";"409481";"2021-04"
"84";"5010081";"23";"319563,97";"115000";"2021-04"
"85";"5023913";"7";"200";"3311,5";"2021-04"
"86";"5023913";"24";"3117,14";"3133,64";"2021-04"
"87";"5023913";"26";"1933,64";"1104,48";"2021-04"
"88";"5023913";"27";"1933,64";"1104,48";"2021-04"
"89";"5023913";"28";"5,01";"5,01";"2021-04"
"90";"5023913";"29";"11,06";"19,35";"2021-04"
"91";"5010047";"1";"0";"25";"2021-05"
"92";"5010047";"2";"207090";"1115589,02";"2021-05"
"93";"5010047";"3";"2510986,53";"1136695,23";"2021-05"
"94";"5010047";"4";"0";"8422,62";"2021-05"
"95";"5010047";"6";"0,95";"0";"2021-05"
"96";"5010047";"8";"200,19";"0";"2021-05"
"97";"5010047";"17";"4821,26";"3245,2";"2021-05"
"98";"5010047";"26";"0,95";"0";"2021-05"
"99";"5010047";"31";"1531537,97";"1363867,48";"2021-05"
"100";"5010047";"167";"400";"0";"2021-05"
"101";"5010081";"1";"17855";"1000000";"2021-05"
"102";"5010081";"2";"3903462";"4648287,38";"2021-05"
"103";"5010081";"3";"1273659,2";"636415,54";"2021-05"
"104";"5010081";"4";"120,06";"0";"2021-05"
"105";"5010081";"6";"141666,66";"283333,32";"2021-05"
"106";"5010081";"9";"601056,52";"946000";"2021-05"
"107";"5010081";"23";"263049,3";"1000000";"2021-05"
"108";"5023913";"7";"5680,08";"3333,5";"2021-05"
"109";"5023913";"24";"3231,02";"3186,02";"2021-05"
"110";"5023913";"26";"1870,02";"1355,74";"2021-05"
"111";"5023913";"27";"1870,02";"1355,74";"2021-05"
"112";"5023913";"29";"13,56";"18,25";"2021-05"
"113";"5010047";"2";"135529,57";"0";"2021-06"
"114";"5010047";"3";"473623,9";"447708,72";"2021-06"
"115";"5010047";"4";"21716,58";"17000";"2021-06"
"116";"5010047";"17";"3500,66";"3000";"2021-06"
"117";"5010047";"23";"98,7";"0";"2021-06"
"118";"5010047";"26";"0,95";"0";"2021-06"
"119";"5010047";"31";"62699,14";"180834,07";"2021-06"
"120";"5010081";"2";"2402807,59";"2872723,45";"2021-06"
"121";"5010081";"9";"600000";"1350000";"2021-06"
"122";"5010081";"13";"369817,68";"242244,9";"2021-06"
"123";"5010081";"23";"352919,5";"0";"2021-06"
"124";"5023913";"7";"7491,42";"9670";"2021-06"
"125";"5023913";"24";"4091,57";"4136,57";"2021-06"
"126";"5023913";"26";"2014,97";"3561,59";"2021-06"
"127";"5023913";"27";"2014,97";"3561,59";"2021-06"
"128";"5023913";"28";"12,91";"12,91";"2021-06"
"129";"5023913";"29";"35,63";"20,61";"2021-06"
I was asked to pivot this table in a way that it showed the sums of all debits and credits divided by month.
"CUSTOMER_NO";"ACCOUNT_NO";"SUM(ACCOUNTED_DR)2021-01";"SUM(ACCOUNTED_CR)2021-01";"SUM(ACCOUNTED_DR)2021-02";"SUM(ACCOUNTED_CR)2021-02" etc. the columns should be like this.
This is what i wrote:
select * from(select customer_no, account_no, sum(accounted_dr), sum(accounted_cr), to_char(effective_date, 'YYYY/MM') as "Effective Months and Years" from GL_JOURNAL_LINES)
pivot(sum(accounted_dr), sum(accounted_cr) for to_char(effective_date, 'YYYY-MM') in ("2021/01", "2021/02", "2021/03", "2021/04", "2021/05", "2021/06"));
order by to_char(effective_date, 'YYYY-MM')
It shows missing In keyword. What should i do?
CodePudding user response：
- Double quotes
""are for identifier (column and table aliases) and single quotes''are for string literals. - You do not need to aggregate in the inner sub-query, leave that to the
PIVOTin the outer query.
Like this:
select *
from (
select customer_no,
account_no,
accounted_dr,
accounted_cr,
to_char(effective_date, 'YYYY/MM') as yyyy_mm
from GL_JOURNAL_LINES
)
pivot(
sum(accounted_dr) AS dr,
sum(accounted_cr) AS cr
for yyyy_mm in ( -- use the alias from the sub-query
'2021/01' AS "2021/01", -- literal AS identifier
'2021/02' AS "2021/02",
'2021/03' AS "2021/03",
'2021/04' AS "2021/04",
'2021/05' AS "2021/05",
'2021/06' AS "2021/06"
)
);
CodePudding user response：
You can't apply a function as part of the for - which is causing the initial ORA-01738 error - and should be using a value form the subquery instead. You're also aggregating inside the subquery, which you shouldn't be doing, and referring to columns form the base table outside the subquery, where they won't be recognised. And using double quotes when they should be single quotes to represent literals.
So you want something like this, truncating the effective date to the start of the month and treating that as a date:
select * from (
select customer_no, account_no, accounted_dr, accounted_cr,
trunc(effective_date, 'MM') as effective_month
from GL_JOURNAL_LINES
)
pivot (
sum(accounted_dr) as dr, sum(accounted_cr) as cr
for effective_month
in (
date '2021-01-01' as "2021/01", date '2021-02-01' as "2021/02",
date '2021-03-01' as "2021/03", date '2021-04-01' as "2021/04",
date '2021-05-01' as "2021/05", date '2021-06-01' as "2021/06"
)
)
or converting to a string in the subquery as you already were:
select * from (
select customer_no, account_no, accounted_dr, accounted_cr,
to_char(effective_date, 'YYYY-MM') as effective_month
from GL_JOURNAL_LINES
)
pivot (
sum(accounted_dr) as dr, sum(accounted_cr) as cr
for effective_month
in (
'2021-01' as "2021/01", '2021-02' as "2021/02", '2021-03' as "2021/03",
'2021-04' as "2021/04", '2021-05' as "2021/05", '2021-06' as "2021/06"
)
)
(I changed the format of the string slightly to show the literal value being used in the in, and that the result uses the alias - so the string format doesn't matter as long as it's representative.)