[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

RE: [sqr-users] ORA-04031 Error --- How can I resolve this??

you could try using sub-query instead of the IN statement.. this might solve
ur problem in IN statement is the culprit
Also are you getting group by function error? if you use aggregate function
like SUM, you have to
use group by clause as well..

dinesh



-----Original Message-----
From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On
Behalf Of Poonam Auluck
Sent: Monday, March 17, 2003 3:23 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] ORA-04031 Error --- How can I resolve this??


Hello!

I've created some reports that dynamically build the from, where, and group
by clause.  For the where clause, I had to use the IN function a lot, and
now that we are testing the reports out, we're receiving an ORA-04031
error.  I think it has to do with the in clause being so long, but instead
of re-structuring the code and re-coding, I was curious if there was
another way to resolve this error.  Any ideas?  You'll laugh at my sql
statement, but here is what is bombing out...

Thanks for your help!
Poonam

SELECT sum(data)  from bf_data where bf_fund_cd in

('0001','0002','0003','0004','0005','0006','0007','0008','0009','0010','0

011','0012','0013','0014','0015','0016','0017','0018','0020','0021','0022

','0023','0024','0025','0026','0027','0028','0029','0031','0032','0033','

0034','0036','0038','0039','0040','0041','0042','0043','0044','0045','004

6','0047','0048','0049','0050','0052','0053','0054','0055','0056','0057',

'0058','0059','0060','0061','0062','0063','0064','0065','0066','0067','00

69','0070','0072','0073','0074','0075','0076','0077','0078','0079','0080'

,'0081','0084','0085','0086','0088','0089','0092','0094','0096','0098','0

100','0101','0105','0106','0107','0108','0112','0113','0114','0115','0116

','0117','0118','0119','0121','0122','0123','0124','0126','0127','0128','

0130','0131','0132','0133','0134','0135','0136','0137','0138','0139','014

0','0141','0142','0143','0144','0146','0147','0148','0149','0151','0152',

'0154','0157','0158','0159','0160','0161','0162','0163','0164','0165','01

66','0167','0169','0170','0171','0172','0174','0175','0177','0178','0179'

,'0180','0181','0182','0183','0184','0185','0189','0190','0191','0192','0

193','0194','0195','0196','0197','0198','0201','0202','0203','0204','0205

','0206','0207','0208','0209','0210','0211','0212','0213','0214','0215','

0216','0217','0218','0219','0220','0221','0222','0223','0224','0225','022

6','0228','0229','0230','0231','0232','0233','0234','0235','0236','0237',

'0238','0239','0240','0241','0242','0243','0245','0246','0247','0248','02

49','0250','0251','0253','0254','0256','0257','0258','0260','0261','0262'

,'0263','0264','0266','0267','0268','0269','0270','0271','0272','0273','0

274','0275','0277','0278','0279','0280','0281','0282','0283','0284','0285

','0286','0287','0288','0289','0290','0292','0293','0294','0295','0297','

0298','0299','0300','0301','0302','0303','0304','0305','0306','0308','030

9','0310','0311','0312','0313','0316','0317','0318','0319','0320','0321',

'0323','0324','0325','0326','0327','0328','0329','0330','0331','0333','03

34','0335','0336','0337','0338','0339','0340','0341','0342','0343','0344'

,'0345','0346','0347','0348','0349','0350','0351','0352','0353','0354','0

355','0356','0358','0359','0360','0361','0362','0363','0364','0365','0366

','0367','0368','0369','0370','0371','0372','0373','0374','0375','0376','

0377','0378','0379','0380','0381','0382','0383','0384','0385','0386','038

7','0388','0389','0390','0391','0392','0393','0394','0395','0396','0397',

'0400','0401','0402','0403','0404','0405','0406','0407','0408','0409','04

10','0411','0412','0413','0415','0421','0422','0423','0424','0425','0426'

,'0427','0428','0430','0431','0432','0433','0434','0435','0436','0437','0

438','0439','0440','0441','0442','0444','0445','0447','0448','0450','0451

','0453','0454','0457','0458','0459','0462','0463','0465','0466','0467','

0468','0470','0471','0472','0473','0474','0475','0476','0477','0478','047

9','0480','0481','0484','0485','0488','0489','0490','0493','0496','0497',

'0498','0506','0510','0516','0523','0532','0597','0598','0609','0612','06

13','0614','0631','0632','0633','0634','0635','0637','0638','0639','0640'

,'0642','0643','0644','0645','0646','0651','0657','0659','0660','0661','0

662','0663','0664','0665','0666','0667','0669','0675','0676','0677','0678

','0679','0680','0681','0682','0683','0684','0685','0686','0687','0690','

0691','0693','0694','0696','0697','0698','0699','0700','0701','0702','070

5','0706','0707','0708','0709','0710','0711','0712','0713','0716','0717',

'0723','0728','0733','0738','0742','0743','0744','0745','0791','0792','07

93','0801','0802','0803','0804','0805','0806','0807','0809','0810','0811'

,'0812','0813','0814','0815','0817','0818','0819','0820','0821','0822','0

824','0825','0826','0827','0831','0833','0837','0838','0839','0840','0841

','0845','0846','0848','0849','0850','0851','0853','0854','0855','0856','

0859','0863','0865','0867','0868','0870','0871','0877','0878','0879','088

0','0882','0884','0886','0887','0890','0891','0898','0900','0901','0902',

'0904','0905','0906','0907','0908','0910','0911','0914','0915','0922','09

24','0926','0991','0030','0051','0068','0071','0082','0083','0087','0099'

,'0102','0103','0109','0129','0150','0153','0155','0168','0173','0176','0

186','0187','0188','0200','0227','0244','0252','0255','0259','0265','0276

','0291','0296','0307','0314','0315','0332','0357','0398','0399','0414','

0416','0417','0418','0419','0420','0449','0455','0461','0469','0482','048

7','0495','0499','0504','0513','0554','0641','0652','0653','0656','0692',

'0714','0718','0719','0720','0721','0722','0726','0729','0730','0731','07

37','0746','0747','0749','0808','0823','0858','0860','0889','0903','0912'

,'0916','0923','0A00','0A02','0A03','0A20','0A21','0A22','0A23','0A24','0

A25','0A26','0A50','0A60','0A70','0A75','0A80','0A85','0A90','0A95','0A99

','0B00','0D00','0I00','0I05','0I08','0I10','0I12','0I14','0I16','0I18','

0I20','0I21','0I22','0I23','0I24','0I25','0I26','0I27','0I28','0I29','0I3

0','0I31','0I50','0I60','0I70','0I80','0I85','0I90','0I99','0N00','0N21',

'0N22','0N23','0N24','0N25','0N26','0N27','0N28','0N29','0N50','0N60','0N
      70','0N80','0N85','0N90','0N95','0N99') and bf_orgn_cd in

('1121001','1121002','1122001','1122300','1122301','1122302','0093470','0
      093471') and bf_tm_perd_cd = 'PY1ACT' and bf_bdob_cd in ('101')



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users