OraFAQ Forum: SQL & PL/SQL » Problem in pivot query

Home » SQL & PL/SQL » SQL & PL/SQL » Problem in pivot query (12.1.0.1 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem in pivot query [message #689414]

Fri, 15 December 2023 22:06

OraFerro
Messages: 433
Registered: July 2011

Senior Member

Dear All,

I have an error that I cannot explain in a pivot query. following case shows a test table that works just fine but when using the view that carries the actual data it fails. I tried revising the data and even getting a small sample of the view data to make sure that Due_month has normal values but could not figure out what is wrong.

My case:

-- The test case that is working:
CREATE TABLE TEST_PVT 
  (
    CODE NUMBER(3),
    AGR_NO VARCHAR2(15),
    DUE_MONTH NUMBER(2),
    DUE_YEAR NUMBER(4),
    WTD_AMOUNT NUMBER(15,3)
  );

INSERT ALL
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(87,'107',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'139',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(87,'141',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(87,'150',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'163',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'178',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'181',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'182',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'182',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'182',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'182',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'182',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(87,'200',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'206',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(87,'209',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'216',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'219',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(87,'225',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(87,'226',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'227',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'289',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'317',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(103,'329',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'331',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'336',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'343',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'346',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'350',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'352',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'355',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'356',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'357',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'362',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'363',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'370',6,2024,8122422.699)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'373',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'375',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(139,'376',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'383',5,2024,3354936.716)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'386',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'389',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'391',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'392',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'393',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'394',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'395',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(103,'398',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'399',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'400',6,2024,1697796.616)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'400',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'400',12,2024,1697796.616)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'403',3,2024,3194398.45)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'405',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'407',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'408',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'409',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'410',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'412',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'413',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'414',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'417',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'418',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'419',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'420',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'422',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'423',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'425',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'428',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'429',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'430',2,2024,7419071.419)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'430',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'432',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'433',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'434',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'435',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'436',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(103,'437',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'438',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'439',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'440',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'443',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'444',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'445',3,2024,244828.605)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'446',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'447',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'448',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'449',2,2024,3845052.783)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'449',8,2024,3845052.783)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'453',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'454',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'455',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'456',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'457',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(103,'458',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'459',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'461',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'462',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'463',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'464',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'465',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'467',3,2024,2754848.281)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'468',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'469',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'470',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'471',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'472',2,2024,3614285.984)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'474',3,2024,2465776.103)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'475',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'476',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(165,'477',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'478',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'480',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'482',4,2024,8442842.896)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'483',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'484',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'486',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'488',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'490',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'492',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'494',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'495',2,2024,2931462.605)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'496',2,2024,5309689.095)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'497',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'499',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'500',2,2024,8750132.355)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'502',3,2024,4188683.106)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'503',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'504',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'507',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'508',4,2024,11386211.553)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'509',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'510',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'511',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'512',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'513',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'514',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'515',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'517',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'518',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'520',4,2024,7837597.174)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'521',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'522',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'523',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'524',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'525',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(165,'526',4,2024,7319012.59)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'527',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'528',1,2024,1366167.58)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'529',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'531',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'532',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'532',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'534',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'535',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'536',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'537',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'538',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'539',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'540',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(165,'541',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'542',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'543',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'544',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(165,'545',5,2024,7234388.195)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'546',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'547',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'548',5,2024,802305.964)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'549',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'550',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'553',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'554',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'555',4,2024,832811.164)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'556',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'557',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'558',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'559',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'560',4,2024,1114584.938)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'561',2,2024,3996603.353)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'562',2,2024,2173727.422)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'563',3,2024,7084699.55)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'564',2,2024,15011787.217)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'566',4,2024,5409817.205)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'567',1,2024,2481451.193)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'568',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'569',2,2024,1406994.38)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'569',8,2024,1406994.38)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'570',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'571',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'572',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'573',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'574',2,2024,8276414.674)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'574',8,2024,8276414.674)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'575',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'576',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'577',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'578',4,2024,18470853.117)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'579',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'580',4,2024,1400212.388)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'582',4,2024,10859572.454)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'583',5,2024,6201215.808)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'583',11,2024,6201215.808)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'584',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'585',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'587',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'588',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'590',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'591',4,2024,19783603.862)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'591',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'592',2,2024,9083108.052)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'593',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'595',3,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'598',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'598',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'600',2,2024,318938)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'600',8,2024,318938)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'601',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'602',6,2024,15872183.925)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'603',5,2024,120035.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'603',11,2024,120035.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'604',5,2024,10867956.899)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'605',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'607',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'606',1,2024,22398724.208)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'608',4,2024,3007746.3055)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'608',10,2024,3007746.3055)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'610',2,2024,992210.226)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'609',4,2024,1872001)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'609',10,2024,1872001)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'611',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'611',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'611',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'615',6,2024,16960536.378)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'616',4,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'617',2,2024,3160139.196)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'617',8,2024,3160139.196)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'14-P      ',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'620',6,2024,4310409.95)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'619',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'618',1,2024,1103.223)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'621',4,2024,4644763.6885)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'621',10,2024,4644763.6885)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'622',6,2024,3648034.166)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'622',12,2024,3648034.166)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'623',6,2024,2390225)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'623',12,2024,2390225)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'624',4,2024,3183024.857)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'625',2,2024,18952631.732)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'625',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'630',1,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'628',5,2024,10040142.716)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'628',11,2024,10040142.716)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'629',2,2024,8654025.898)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'631',3,2024,2718174.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'631',9,2024,2718174.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'632',2,2024,3502946.208)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'632',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'634',6,2024,31980197.728)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'635',2,2024,12828784.582)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'635',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'636',2,2024,211310.693)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'637',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'639',2,2024,1405221.538)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'640',4,2024,3900362.6165)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'640',10,2024,3900362.6165)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'641',3,2024,56281.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'641',9,2024,56281.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'644',4,2024,878743.735)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'644',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'645',2,2024,14277939.3555)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'645',8,2024,14277939.3555)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'646',2,2024,2234326)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'646',8,2024,2234326)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'647',4,2024,593191)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'647',10,2024,593191)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'650',3,2024,2094364)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'650',9,2024,2094364)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'649',2,2024,13163434)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'649',8,2024,13163434)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'651',3,2024,1172587)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'651',9,2024,1172587)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'652',3,2024,3012036.987)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'652',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'652',9,2024,3012036.987)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'653',2,2024,19432)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'653',8,2024,19432)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'655',2,2024,570000)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'655',8,2024,570000)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'656',2,2024,998284)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'656',8,2024,998284)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'657',3,2024,4086257.587)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'657',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'658',6,2024,2014099.939)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'658',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'659',2,2024,3827771)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'659',8,2024,3827771)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'660',2,2024,3559245)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'660',8,2024,3559245)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'661',3,2024,1079)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'661',9,2024,1079)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'662',2,2024,1046813.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'662',8,2024,1046813.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'15-P      ',3,2024,1020000)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'668',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'668',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'663',2,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'663',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'664',3,2024,788263.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'664',9,2024,788263.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'670',6,2024,1028527.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'670',12,2024,1028527.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'671',6,2024,1491801.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'671',12,2024,1491801.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'672',2,2024,3736920.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'672',8,2024,3736920.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'675',1,2024,1748353.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'675',7,2024,1748353.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'677',6,2024,1421012)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'677',12,2024,1421012)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'678',6,2024,101)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'678',12,2024,101)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'680',6,2024,3718757.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'680',12,2024,3718757.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'679',6,2024,660000)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'679',12,2024,660000)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'681',6,2024,5740910)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'681',12,2024,5740910)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'682',2,2024,1025154)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'682',8,2024,1025154)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'683',4,2024,2471652.567)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'683',10,2024,2471652.567)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'684',4,2024,2447584)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'684',10,2024,2447584)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'685',2,2024,421101.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'685',8,2024,421101.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'16-P      ',6,2024,295394.898)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'687',1,2024,819020.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'687',7,2024,819020.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'688',4,2024,136199.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'688',10,2024,136199.5)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(139,'692',5,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'492',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'468',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'504',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'607',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'438',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'497',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'474',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'619',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'538',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'289',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'494',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'529',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'445',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'517',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'572',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'588',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'554',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'549',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'636',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'429',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'373',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(87,'225',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'350',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'495',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(139,'692',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'472',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(103,'458',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'443',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'139',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'407',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'590',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'434',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'356',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'432',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'428',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'399',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'362',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'605',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'584',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'395',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'405',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'455',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(165,'541',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'509',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'463',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'389',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'582',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'620',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'352',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'456',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'570',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'624',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'548',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'563',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'464',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'163',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'525',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'510',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'448',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'580',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(87,'141',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'602',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'573',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'502',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'507',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'14-P      ',6,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'435',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(103,'437',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'601',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'618',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'386',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'457',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'593',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'446',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'469',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'585',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'629',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'488',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'543',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'459',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'409',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(87,'209',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'534',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(165,'526',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'476',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'579',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'503',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'639',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'604',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'592',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(165,'477',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'536',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'521',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(87,'150',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'484',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'336',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'511',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'531',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(165,'545',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'559',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'555',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'490',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'178',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'462',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(103,'398',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'346',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'567',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'514',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'539',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'16-P      ',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'439',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'453',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'444',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'562',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'423',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'408',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'470',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'447',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'413',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'436',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'392',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'15-P      ',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'422',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(139,'376',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'566',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'630',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'417',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(87,'107',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'637',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'419',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'478',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'465',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'595',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'480',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'414',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'535',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'544',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'391',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'206',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'393',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'512',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'524',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'471',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'499',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'403',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'578',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'634',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'500',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'216',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'219',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'440',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'560',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'513',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(137,'576',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'564',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'486',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'227',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'331',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(87,'200',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'508',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'575',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'606',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'568',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'553',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'577',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'483',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'615',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'587',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'561',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'547',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'383',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(16,'370',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'425',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'420',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'523',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'433',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(103,'329',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'496',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(186,'518',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'14-P      ',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'515',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'571',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'610',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'14-P      ',12,2023,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'550',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'394',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'520',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'540',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'410',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'528',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(176,'537',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(54,'461',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'557',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'475',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'616',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(87,'226',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(49,'412',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'527',7,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'375',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(100,'418',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(171,'181',8,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'482',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(117,'454',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'546',10,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'357',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'522',11,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'558',12,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(135,'467',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(93,'542',9,2024,0)
into test_pvt(code, agr_no, due_MONTH, DUE_YEAR, wtd_amount) values(124,'556',9,2024,0)

SELECT * FROM DUAL;



SELECT code, DUE_YEAR, JAn, FEb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec,  NVL(JAN,0)+NVL(FEB ,0)+NVL(MAR,0)+NVL(APR ,0)+NVL(MAY,0)+NVL(JUN ,0)+NVL(JUL,0)+NVL(AUG ,0)+NVL(SEP,0)+NVL(OCT ,0)+NVL(NOV,0)+NVL(DEC ,0) YEAR_TOTAL
    FROM
    (
      SELECT * 
        FROM 
        (
           SELECT CODE, DUE_MONTH, DUE_YEAR, SUM(WTD_AMOUNT) WTD_YEAR
             FROM TEST_PVT 
           GROUP BY CODE, DUE_MONTH,  DUE_YEAR
        )
      PIVOT(
        SUM(WTD_YEAR) FOR DUE_MONTH IN (1 AS "JAN", 2 "FEB", 3 "MAR", 4 "APR", 5 "MAY", 6 "JUN", 7 "JUL", 8 "AUG", 9 "SEP", 10 "OCT", 11 "NOV", 12 "DEC")
      )
      ORDER BY CODE  
    ) DTA;

As you can see I am trying to get values per code, per year, pivoted by month (due_month). However, when I use a view instead of the test table:

SELECT COUNTRY_CODE, DUE_YEAR, JAn, FEb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec,  NVL(JAN,0)+NVL(FEB ,0)+NVL(MAR,0)+NVL(APR ,0)+NVL(MAY,0)+NVL(JUN ,0)+NVL(JUL,0)+NVL(AUG ,0)+NVL(SEP,0)+NVL(OCT ,0)+NVL(NOV,0)+NVL(DEC ,0) YEAR_TOTAL
    FROM
    (
      SELECT * 
        FROM 
        (
          select country_code, due_month, due_year, sum(wtd_year ) wtd_year
            from v_frc_cashflow_y
          group by country_code, due_month, due_year
        )
      PIVOT(
        SUM(WTD_YEAR) FOR DUE_MONTH IN (1 "JAN", 2 "FEB", 3 "MAR", 4 "APR", 5 "MAY", 6 "JUN", 7 "JUL", 8 "AUG", 9 "SEP", 10 "OCT", 11 "NOV", 12 "DEC")
      )
      ORDER BY COUNTRY_CODE  
    ) DTA;

I get: 1 ORA-00979: not a GROUP BY expression!
In fact, the error is coming from the inner query:

      SELECT * 
        FROM 
        (
          select country_code, due_month, due_year, sum(wtd_year ) wtd_year
            from v_frc_cashflow_y
          group by country_code, due_month, due_year
        )
      PIVOT(
        SUM(WTD_YEAR) FOR DUE_MONTH IN (1 "JAN", 2 "FEB", 3 "MAR", 4 "APR", 5 "MAY", 6 "JUN", 7 "JUL", 8 "AUG", 9 "SEP", 10 "OCT", 11 "NOV", 12 "DEC")
      )
      ORDER BY COUNTRY_CODE

In fact, I created test_pvt table with a sample data from the view v_frc_cashflow_y but only for years before 2025. When I try the inner query with this where condition it fails for the same error!:

      SELECT * 
        FROM 
        (
          select country_code, due_month, due_year, sum(wtd_year ) wtd_year
            from v_frc_cashflow_y where due_year < 2025
          group by country_code, due_month, due_year
        )
      PIVOT(
        SUM(WTD_YEAR) FOR DUE_MONTH IN (1 "JAN", 2 "FEB", 3 "MAR", 4 "APR", 5 "MAY", 6 "JUN", 7 "JUL", 8 "AUG", 9 "SEP", 10 "OCT", 11 "NOV", 12 "DEC")
      )

Can you please help me by guiding me on what I am doing wrong?

Regards,
Ferro

Report message to a moderator

Re: Problem in pivot query [message #689415 is a reply to message #689414]

Sat, 16 December 2023 17:29

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

Change sum(wtd_year) to sum(wtd_amount)

SCOTT@orcl_12.1.0.2.0> select banner from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

SCOTT@orcl_12.1.0.2.0> SET LINESIZE 200
SCOTT@orcl_12.1.0.2.0> SELECT code, DUE_YEAR, JAn, FEb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec,  NVL(JAN,0)+NVL(FEB ,0)+NVL(MAR,0)+NVL(APR ,0)+NVL(MAY,0)+NVL(JUN ,0)+NVL(JUL,0)+NVL(AUG ,0)+NVL(SEP,0)+NVL(OCT ,0)+NVL(NOV,0)+NVL(DEC ,0) YEAR_TOTAL
  2  	 FROM
  3  	 (
  4  	   SELECT *
  5  	     FROM
  6  	     (
  7  		SELECT CODE, DUE_MONTH, DUE_YEAR, SUM(WTD_AMOUNT) WTD_YEAR
  8  		  FROM TEST_PVT
  9  		GROUP BY CODE, DUE_MONTH,  DUE_YEAR
 10  	     )
 11  	   PIVOT(
 12  	     SUM(WTD_YEAR) FOR DUE_MONTH IN (1 AS "JAN", 2 "FEB", 3 "MAR", 4 "APR", 5 "MAY", 6 "JUN", 7 "JUL", 8 "AUG", 9 "SEP", 10 "OCT", 11 "NOV", 12 "DEC")
 13  	   )
 14  	   ORDER BY CODE
 15  	 ) DTA
 16  /

      CODE   DUE_YEAR        JAN        FEB        MAR        APR        MAY        JUN        JUL        AUG        SEP        OCT        NOV        DEC YEAR_TOTAL
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        16       2024   819020.5                     0                        8122422.7   819020.5                     0                                0  9760463.7
        49       2024   1103.223 12828784.6 3012036.99                                0          0          0 3012036.99                                0 18853961.8
        54       2023                                                                                                                                   0          0
        54       2024          0 8349490.48 5258844.59            27229350.9   49236129          0  4735204.5    1172587              16361394          0  112343001
        87       2024                                           0                                                                 0                                0
        93       2024 2481451.19          0          0          0          0 7360634.95          0          0          0          0          0    3050225 12892311.1
       100       2024          0 28593778.3          0 4644763.69            1697796.62          0 4183422.78          0 4644763.69            1697796.62 45462321.7
       103       2024                                                      0                                                                 0                     0
       117       2024          0 47786747.8  1076281.5 8167203.35          0 4534529.94          0 15684933.7    56281.5 5055436.07          0    2520430 84881843.9
       124       2024             3160139.2   788263.5          0          0 23238975.6             3160139.2   788263.5          0          0 7366791.67 38502572.6
       135       2024 22398724.2 17185514.6   17467458 28781236.6 3354936.72          0          0          0          0          0          0          0 89187870.1
       137       2024  1748353.5 26894377.2                                     5740910  1748353.5   25277845                                     5740910 67150749.2
       139       2024                                                      0                                                                 0                     0
       165       2024                     0            7319012.59  7234388.2                                0                     0          0            14553400.8
       171       2024 1366167.58          0  2465776.1 54523846.6 802305.964          0          0          0          0          0          0          0 59158096.3
       176       2024                     0          0                     0          0                     0          0                     0          0          0
       186       2024            21926126.1  4813617.5 12556325.8          0    1421012            8846414.67  4813617.5 9373300.92          0    1421012 65171426.5

17 rows selected.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE VIEW v_frc_cashflow_y AS
  2  SELECT code AS country_code,
  3  	    due_month, due_year, wtd_amount
  4  FROM   test_pvt
  5  /

View created.

-- CORRECTED QUERY WITH sum(wtd_amount) instead of sum(wtd_year):

SCOTT@orcl_12.1.0.2.0> SELECT COUNTRY_CODE, DUE_YEAR, JAn, FEb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec,  NVL(JAN,0)+NVL(FEB ,0)+NVL(MAR,0)+NVL(APR ,0)+NVL(MAY,0)+NVL(JUN ,0)+NVL(JUL,0)+NVL(AUG ,0)+NVL(SEP,0)+NVL(OCT ,0)+NVL(NOV,0)+NVL(DEC ,0) YEAR_TOTAL
  2  	 FROM
  3  	 (
  4  	   SELECT *
  5  	     FROM
  6  	     (
  7  	       select country_code, due_month, due_year, sum(wtd_amount) wtd_year
  8  		 from v_frc_cashflow_y
  9  	       group by country_code, due_month, due_year
 10  	     )
 11  	   PIVOT(
 12  	     SUM(WTD_YEAR) FOR DUE_MONTH IN (1 "JAN", 2 "FEB", 3 "MAR", 4 "APR", 5 "MAY", 6 "JUN", 7 "JUL", 8 "AUG", 9 "SEP", 10 "OCT", 11 "NOV", 12 "DEC")
 13  	   )
 14  	   ORDER BY COUNTRY_CODE
 15  	 ) DTA
 16  /

COUNTRY_CODE   DUE_YEAR        JAN        FEB        MAR        APR        MAY        JUN        JUL        AUG        SEP        OCT        NOV        DEC YEAR_TOTAL
------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          16       2024   819020.5                     0                        8122422.7   819020.5                     0                                0  9760463.7
          49       2024   1103.223 12828784.6 3012036.99                                0          0          0 3012036.99                                0 18853961.8
          54       2023                                                                                                                                   0          0
          54       2024          0 8349490.48 5258844.59            27229350.9   49236129          0  4735204.5    1172587              16361394          0  112343001
          87       2024                                           0                                                                 0                                0
          93       2024 2481451.19          0          0          0          0 7360634.95          0          0          0          0          0    3050225 12892311.1
         100       2024          0 28593778.3          0 4644763.69            1697796.62          0 4183422.78          0 4644763.69            1697796.62 45462321.7
         103       2024                                                      0                                                                 0                     0
         117       2024          0 47786747.8  1076281.5 8167203.35          0 4534529.94          0 15684933.7    56281.5 5055436.07          0    2520430 84881843.9
         124       2024             3160139.2   788263.5          0          0 23238975.6             3160139.2   788263.5          0          0 7366791.67 38502572.6
         135       2024 22398724.2 17185514.6   17467458 28781236.6 3354936.72          0          0          0          0          0          0          0 89187870.1
         137       2024  1748353.5 26894377.2                                     5740910  1748353.5   25277845                                     5740910 67150749.2
         139       2024                                                      0                                                                 0                     0
         165       2024                     0            7319012.59  7234388.2                                0                     0          0            14553400.8
         171       2024 1366167.58          0  2465776.1 54523846.6 802305.964          0          0          0          0          0          0          0 59158096.3
         176       2024                     0          0                     0          0                     0          0                     0          0          0
         186       2024            21926126.1  4813617.5 12556325.8          0    1421012            8846414.67  4813617.5 9373300.92          0    1421012 65171426.5

17 rows selected.

Report message to a moderator

Re: Problem in pivot query [message #689416 is a reply to message #689415]

Sun, 17 December 2023 11:30

OraFerro
Messages: 433
Registered: July 2011

Senior Member

@Barbara

Thanks a lot for your reply.
In fact the view has the same column named WTD_YEAR not amount. Its a bit confusing as I had to make sure that the column names are identical before creating the test case, sorry for that (I modified it for the following examples).

The issue is that I suspect its something either related to the set of data or to the use of the view:

SELECT COUNTRY_CODE, DUE_YEAR, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, NVL(JAN,0)+NVL(FEB ,0)+NVL(MAR,0)+NVL(APR ,0)+NVL(MAY,0)+NVL(JUN ,0)+NVL(JUL,0)+NVL(AUG ,0)+NVL(SEP,0)+NVL(OCT ,0)+NVL(NOV,0)+NVL(DEC ,0) YEAR_TOTAL
    FROM
    (
      SELECT * 
        FROM 
        (
          SELECT COUNTRY_CODE, DUE_MONTH, DUE_YEAR, SUM(WTD_amount ) WTD_YEAR
            FROM v_FRC_CASHFLOW_Y
          GROUP BY COUNTRY_CODE, DUE_MONTH, DUE_YEAR
        )
      PIVOT(
        SUM(ROUND(WTD_YEAR,0)) FOR DUE_MONTH IN (1 "JAN", 2 "FEB", 3 "MAR", 4 "APR", 5 "MAY", 6 "JUN", 7 "JUL", 8 "AUG", 9 "SEP", 10 "OCT", 11 "NOV", 12 "DEC")
      )
      ORDER BY COUNTRY_CODE  
    ) DTA;

The above leads to the same error. However when I turn the view same exact view into a table, it works!!:

CREATE TABLE FRC_CASHFLOW_Y AS
  SELECT * FROM V_FRC_CASHFLOW_Y;


SELECT COUNTRY_CODE, DUE_YEAR, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, NVL(JAN,0)+NVL(FEB ,0)+NVL(MAR,0)+NVL(APR ,0)+NVL(MAY,0)+NVL(JUN ,0)+NVL(JUL,0)+NVL(AUG ,0)+NVL(SEP,0)+NVL(OCT ,0)+NVL(NOV,0)+NVL(DEC ,0) YEAR_TOTAL
    FROM
    (
      SELECT * 
        FROM 
        (
          SELECT COUNTRY_CODE, DUE_MONTH, DUE_YEAR, SUM(WTD_amount ) WTD_YEAR
            FROM FRC_CASHFLOW_Y
          GROUP BY COUNTRY_CODE, DUE_MONTH, DUE_YEAR
        )
      PIVOT(
        SUM(ROUND(WTD_YEAR,0)) FOR DUE_MONTH IN (1 "JAN", 2 "FEB", 3 "MAR", 4 "APR", 5 "MAY", 6 "JUN", 7 "JUL", 8 "AUG", 9 "SEP", 10 "OCT", 11 "NOV", 12 "DEC")
      )
      ORDER BY COUNTRY_CODE  
    ) DTA;

What do you think?

Report message to a moderator

Re: Problem in pivot query [message #689417 is a reply to message #689416]

Sun, 17 December 2023 20:26

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

@OraFerro,

I strongly suspect that you have a minor typing error in your query that is not being posted here. You need to post a copy and paste of an actual complete run as I have done below, including the view creation, including line numbers, and including the complete error message. I have included an example of one such thing that would raise the error that you are receiving. It uses your original create table and sample data without reposting it here. I have used only the inner query that you have narrowed the problem down to, in order to simplify things and make it so that there is less code to check. It should be a simple matter of comparing the columns in the select clause to the columns in the group by clause and seeing which is missing in the group by clause or misspelled or the wrong column is used.

-- test environment and settings:

SCOTT@orcl_12.1.0.2.0> select banner from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

SCOTT@orcl_12.1.0.2.0> SET LINESIZE 200

-- inner query that works on original table:

SCOTT@orcl_12.1.0.2.0> SELECT CODE, DUE_YEAR, JAn, FEb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec,  NVL(JAN,0)+NVL(FEB ,0)+NVL(MAR,0)+NVL(APR ,0)+NVL(MAY,0)+NVL(JUN ,0)+NVL(JUL,0)+NVL(AUG ,0)+NVL(SEP,0)+NVL(OCT ,0)+NVL(NOV,0)+NVL(DEC ,0) YEAR_TOTAL
  2  	 FROM
  3  	 (
  4  	   SELECT *
  5  	     FROM
  6  	     (
  7  	       select code, due_month, due_year, sum(wtd_amount) wtd_year
  8  		 from test_pvt
  9  	       group by code, due_month, due_year
 10  	     )
 11  	   PIVOT(
 12  	     SUM(WTD_YEAR) FOR DUE_MONTH IN (1 "JAN", 2 "FEB", 3 "MAR", 4 "APR", 5 "MAY", 6 "JUN", 7 "JUL", 8 "AUG", 9 "SEP", 10 "OCT", 11 "NOV", 12 "DEC")
 13  	   )
 14  	   ORDER BY CODE
 15  	 ) DTA
 16  /

      CODE   DUE_YEAR        JAN        FEB        MAR        APR        MAY        JUN        JUL        AUG        SEP        OCT        NOV        DEC YEAR_TOTAL
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        16       2024   819020.5                     0                        8122422.7   819020.5                     0                                0  9760463.7
        49       2024   1103.223 12828784.6 3012036.99                                0          0          0 3012036.99                                0 18853961.8
        54       2023                                                                                                                                   0          0
        54       2024          0 8349490.48 5258844.59            27229350.9   49236129          0  4735204.5    1172587              16361394          0  112343001
        87       2024                                           0                                                                 0                                0
        93       2024 2481451.19          0          0          0          0 7360634.95          0          0          0          0          0    3050225 12892311.1
       100       2024          0 28593778.3          0 4644763.69            1697796.62          0 4183422.78          0 4644763.69            1697796.62 45462321.7
       103       2024                                                      0                                                                 0                     0
       117       2024          0 47786747.8  1076281.5 8167203.35          0 4534529.94          0 15684933.7    56281.5 5055436.07          0    2520430 84881843.9
       124       2024             3160139.2   788263.5          0          0 23238975.6             3160139.2   788263.5          0          0 7366791.67 38502572.6
       135       2024 22398724.2 17185514.6   17467458 28781236.6 3354936.72          0          0          0          0          0          0          0 89187870.1
       137       2024  1748353.5 26894377.2                                     5740910  1748353.5   25277845                                     5740910 67150749.2
       139       2024                                                      0                                                                 0                     0
       165       2024                     0            7319012.59  7234388.2                                0                     0          0            14553400.8
       171       2024 1366167.58          0  2465776.1 54523846.6 802305.964          0          0          0          0          0          0          0 59158096.3
       176       2024                     0          0                     0          0                     0          0                     0          0          0
       186       2024            21926126.1  4813617.5 12556325.8          0    1421012            8846414.67  4813617.5 9373300.92          0    1421012 65171426.5

17 rows selected.

-- view creation:

 
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE VIEW v_frc_cashflow_y AS
  2  SELECT code AS country_code,
  3  	    due_month, due_year,
  4  	    wtd_amount AS wtd_year
  5  FROM   test_pvt
  6  /

View created.

-- example of inner query on the view that raises the error
-- because the group by clause does not include due_year but has wtd_year instead:

SCOTT@orcl_12.1.0.2.0> SELECT COUNTRY_CODE, DUE_YEAR, JAn, FEb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec,  NVL(JAN,0)+NVL(FEB ,0)+NVL(MAR,0)+NVL(APR ,0)+NVL(MAY,0)+NVL(JUN ,0)+NVL(JUL,0)+NVL(AUG ,0)+NVL(SEP,0)+NVL(OCT ,0)+NVL(NOV,0)+NVL(DEC ,0) YEAR_TOTAL
  2  	 FROM
  3  	 (
  4  	   SELECT *
  5  	     FROM
  6  	     (
  7  	       select country_code, due_month, due_year, sum(wtd_year) wtd_year
  8  		 from v_frc_cashflow_y
  9  	       group by country_code, due_month, wtd_year
 10  	     )
 11  	   PIVOT(
 12  	     SUM(WTD_YEAR) FOR DUE_MONTH IN (1 "JAN", 2 "FEB", 3 "MAR", 4 "APR", 5 "MAY", 6 "JUN", 7 "JUL", 8 "AUG", 9 "SEP", 10 "OCT", 11 "NOV", 12 "DEC")
 13  	   )
 14  	   ORDER BY COUNTRY_CODE
 15  	 ) DTA
 16  /
          select country_code, due_month, due_year, sum(wtd_year) wtd_year
                                          *
ERROR at line 7:
ORA-00979: not a GROUP BY expression

-- query that works on view:

SCOTT@orcl_12.1.0.2.0> SELECT COUNTRY_CODE, DUE_YEAR, JAn, FEb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec,  NVL(JAN,0)+NVL(FEB ,0)+NVL(MAR,0)+NVL(APR ,0)+NVL(MAY,0)+NVL(JUN ,0)+NVL(JUL,0)+NVL(AUG ,0)+NVL(SEP,0)+NVL(OCT ,0)+NVL(NOV,0)+NVL(DEC ,0) YEAR_TOTAL
  2  	 FROM
  3  	 (
  4  	   SELECT *
  5  	     FROM
  6  	     (
  7  	       select country_code, due_month, due_year, sum(wtd_year) wtd_year
  8  		 from v_frc_cashflow_y
  9  	       group by country_code, due_month, due_year
 10  	     )
 11  	   PIVOT(
 12  	     SUM(WTD_YEAR) FOR DUE_MONTH IN (1 "JAN", 2 "FEB", 3 "MAR", 4 "APR", 5 "MAY", 6 "JUN", 7 "JUL", 8 "AUG", 9 "SEP", 10 "OCT", 11 "NOV", 12 "DEC")
 13  	   )
 14  	   ORDER BY COUNTRY_CODE
 15  	 ) DTA
 16  /

COUNTRY_CODE   DUE_YEAR        JAN        FEB        MAR        APR        MAY        JUN        JUL        AUG        SEP        OCT        NOV        DEC YEAR_TOTAL
------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          16       2024   819020.5                     0                        8122422.7   819020.5                     0                                0  9760463.7
          49       2024   1103.223 12828784.6 3012036.99                                0          0          0 3012036.99                                0 18853961.8
          54       2023                                                                                                                                   0          0
          54       2024          0 8349490.48 5258844.59            27229350.9   49236129          0  4735204.5    1172587              16361394          0  112343001
          87       2024                                           0                                                                 0                                0
          93       2024 2481451.19          0          0          0          0 7360634.95          0          0          0          0          0    3050225 12892311.1
         100       2024          0 28593778.3          0 4644763.69            1697796.62          0 4183422.78          0 4644763.69            1697796.62 45462321.7
         103       2024                                                      0                                                                 0                     0
         117       2024          0 47786747.8  1076281.5 8167203.35          0 4534529.94          0 15684933.7    56281.5 5055436.07          0    2520430 84881843.9
         124       2024             3160139.2   788263.5          0          0 23238975.6             3160139.2   788263.5          0          0 7366791.67 38502572.6
         135       2024 22398724.2 17185514.6   17467458 28781236.6 3354936.72          0          0          0          0          0          0          0 89187870.1
         137       2024  1748353.5 26894377.2                                     5740910  1748353.5   25277845                                     5740910 67150749.2
         139       2024                                                      0                                                                 0                     0
         165       2024                     0            7319012.59  7234388.2                                0                     0          0            14553400.8
         171       2024 1366167.58          0  2465776.1 54523846.6 802305.964          0          0          0          0          0          0          0 59158096.3
         176       2024                     0          0                     0          0                     0          0                     0          0          0
         186       2024            21926126.1  4813617.5 12556325.8          0    1421012            8846414.67  4813617.5 9373300.92          0    1421012 65171426.5

17 rows selected.

Report message to a moderator

Re: Problem in pivot query [message #689419 is a reply to message #689417]

Thu, 21 December 2023 00:47

OraFerro
Messages: 433
Registered: July 2011

Senior Member

@Barbara
Apologies for not being able to respond to you before now and thanks a lot for your reply.

I really tried to figure out what is missing but could not. Here is the case the you requested, I even simplified the inner query further and removed the grouping with the same result:

1	SELECT * FROM V_FRC_CASHFLOW_Y;

COUNTRY_CODE DUE_MONTH DUE_YEAR WTD_AMOUNT INTEREST INSTALLMENT
87 4 2024 0 123750 550000
87 10 2024 0 116875 550000
87 4 2027 0 82500 550000
87 4 2028 0 68750 550000
117 1 2026 0 8207.5 88500
117 1 2027 0 7765 88500
117 7 2028 0 7101.25 88500
117 7 2029 0 6658.75 88500
117 7 2041 0 1348.75 88500
117 10 2031 0 988454.669 4308311.285
117 4 2034 0 769950 3476000
117 4 2035 0 688670 3476000
117 10 2040 0 365067.5 1591000
117 10 2041 0 335212.5 1591000
117 4 2043 0 290430 1591000
117 4 2044 0 260575 1610000
117 8 2028 0 1853302.5 5279000
117 2 2036 0 907590 5279000
117 8 2037 0 718447.5 5279000
117 2 2040 0 417710 4119000
117 8 2041 0 284067.5 3639000
117 5 2024 0 100102.048 761000
117 11 2033 0 5220 96000
117 5 2034 0 4980 96000
--... there are 2171 records just showing a sample


1	SELECT COUNTRY_CODE, DUE_MONTH, DUE_YEAR, WTD_amount WTD_YEAR
2	  FROM V_FRC_CASHFLOW_Y;

COUNTRY_CODE DUE_MONTH DUE_YEAR WTD_YEAR
137 2 2028 0
186 3 2024 4813617.5
186 6 2028 0
49 8 2025 0
124 6 2026 3422442.10571428571428571428571428571429
137 8 2029 0
137 8 2025 27485641.82123333333333333333333333333333
54 8 2027 0
124 12 2026 3422442.10571428571428571428571428571429
49 9 2027 0
117 12 2030 0
171 4 2027 0
100 2 2024 28174792.44
117 8 2028 0
49 3 2028 0
--... the same 2171 records just showing a sample

1	SELECT * 
2	    FROM 
3	    (
4	      SELECT COUNTRY_CODE, DUE_MONTH, DUE_YEAR, WTD_amount WTD_YEAR
5	        FROM V_FRC_CASHFLOW_Y
6	    )
7	  PIVOT(
8	    SUM(ROUND(WTD_YEAR,0)) FOR DUE_MONTH IN (1 "JAN", 2 "FEB", 3 "MAR", 4 "APR", 5 "MAY", 6 "JUN", 7 "JUL", 8 "AUG", 9 "SEP", 10 "OCT", 11 "NOV", 12 "DEC")
9	  );

1 ORA-00979: not a GROUP BY expression SQL1.sql 4 15

But when I converted the view into a table:

1	CREATE TABLE FRC_CASHFLOW_Y AS
2	  SELECT * FROM V_FRC_CASHFLOW_Y;
3	
4	SELECT * FROM FRC_CASHFLOW_Y;

COUNTRY_CODE DUE_MONTH DUE_YEAR WTD_AMOUNT INTEREST INSTALLMENT
87 4 2024 0 123750 550000
87 10 2024 0 116875 550000
87 4 2027 0 82500 550000
87 4 2028 0 68750 550000
117 1 2026 0 8207.5 88500
117 1 2027 0 7765 88500
117 7 2028 0 7101.25 88500
117 7 2029 0 6658.75 88500
117 7 2041 0 1348.75 88500
117 10 2031 0 988454.669 4308311.285
117 4 2034 0 769950 3476000
117 4 2035 0 688670 3476000
117 10 2040 0 365067.5 1591000
117 10 2041 0 335212.5 1591000
117 4 2043 0 290430 1591000
117 4 2044 0 260575 1610000
117 8 2028 0 1853302.5 5279000
--... the same 2171 records just showing a sample

Now it works! by adding the same exact pivot inner query but using the table instead of the view!:

1	SELECT * 
2	  FROM 
3	  (
4	    SELECT COUNTRY_CODE, DUE_MONTH, DUE_YEAR, WTD_amount WTD_YEAR
5	      FROM FRC_CASHFLOW_Y
6	  )
7	PIVOT(
8	  SUM(ROUND(WTD_YEAR,0)) FOR DUE_MONTH IN (1 "JAN", 2 "FEB", 3 "MAR", 4 "APR", 5 "MAY", 6 "JUN", 7 "JUL", 8 "AUG", 9 "SEP", 10 "OCT", 11 "NOV", 12 "DEC")
9	);

COUNTRY_CODE	DUE_YEAR	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
165	2029	(null)	0	(null)	0	0	(null)	(null)	0	(null)	0	0	(null)
93	2029	0	0	0	(null)	0	0	0	0	0	(null)	0	0
54	2038	(null)	0	0	(null)	0	0	(null)	0	0	(null)	0	0
135	2024	22398724	17185515	17467458	28781237	3354937	0	0	0	0	0	0	0
186	2040	(null)	0	0	0	(null)	0	(null)	0	0	0	(null)	0
186	2044	(null)	0	0	0	(null)	0	(null)	0	0	0	(null)	0
124	2039	(null)	0	0	0	0	0	(null)	0	0	(null)	0	0
171	2029	0	(null)	0	0	0	0	0	(null)	0	0	0	0
171	2033	0	(null)	0	0	0	0	0	(null)	0	0	0	(null)
16	2028	850439	(null)	0	(null)	(null)	(null)	0	(null)	0	(null)	(null)	(null)
--... there are 342 records but I am just showing a sample

I really could not figure what am I doing wrong.

Sorry again for my late reply,
Ferro

Report message to a moderator

Re: Problem in pivot query [message #689420 is a reply to message #689419]

Thu, 21 December 2023 10:10

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

What is the query that is used to create the view? There may be some conflict with name or scope that is eliminated when you create a table from the view. If your view contains an aggregate that could be the problem.

Report message to a moderator

Re: Problem in pivot query [message #689421 is a reply to message #689420]

Thu, 21 December 2023 11:09

OraFerro
Messages: 433
Registered: July 2011

Senior Member

@Barbara
Yes it does and I suspected that this might have been the problem but couldn't figure out why! Which is why I created the table of the view to try and understand.

create or replace view v_frc_cashflow_y as
           SELECT COUNTRY_CODE, EXTRACT(MONTH FROM DUE_DATE) DUE_MONTH, EXTRACT(YEAR FROM DUE_DATE) DUE_YEAR, SUM(WTD_AMOUNT) WTD_AMOUNT, SUM(INTEREST) INTEREST, SUM(INST_AMOUNT) INSTALLMENT
             FROM V_FRC_CASHFLOW_LOAN 
           GROUP BY COUNTRY_CODE, EXTRACT(MONTH FROM DUE_DATE), EXTRACT(YEAR FROM DUE_DATE);

Please tell me why does the aggregation causing a problem and how to avoid it?
Thanks,
Ferro

Report message to a moderator

Re: Problem in pivot query [message #689422 is a reply to message #689421]

Thu, 21 December 2023 12:19

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

There are too many inconsistencies in what you are posting. It appears that you are creating a view on another view and some things say code and some say country_code and some say wtd_amount and some say wtd_year. You need to post a complete script that reproduces the problem, starting from the base table that the views are created on and not posting mismatched pieces of various tests. I cannot reproduce the problem from what you have posted. You do not need to post a huge amount of data. Just make it as simple as possible. I would try to be as consistent as possible with names, but differentiate where things are different. For example, if you have sum(wtd_year) don't name the alias wtd_year as well. Name it something like sum_wtd_year, so that when you use wtd_year Oracle knows which you mean. If you can post the whole script that can be copied and pasted and post a run of the script with line numbers and error message separately.

Report message to a moderator

Previous Topic:	Help need in writing query to get JSON format output
Next Topic:	problem with sql passthru syntax - get "FROM keyword not found where expected (4 merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 10:52:56 CDT 2024