[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??

Subject: Re: [sqr-users] ORA-04031 Error --- How can I resolve this??
From: Ray Ontko <rayo@ontko.com>
Date: Tue, 18 Mar 2003 07:41:38 -0500
In-reply-to: <OFBD69080F.770BE549-ON85256CEC.00801893-85256CEC.00806739@ams.com>
List-help: <mailto:sqr-users-request@sqrug.org?subject=help>
List-id: This list is for discussion about the SQR database reporting language from Brio Software. <sqr-users.sqrug.org>
List-post: <mailto:sqr-users@sqrug.org>
List-subscribe: <http://www.sqrug.org/mailman/listinfo/sqr-users>,<mailto:sqr-users-request@sqrug.org?subject=subscribe>
List-unsubscribe: <http://www.sqrug.org/mailman/listinfo/sqr-users>,<mailto:sqr-users-request@sqrug.org?subject=unsubscribe>
References: <OFBD69080F.770BE549-ON85256CEC.00801893-85256CEC.00806739@ams.com>
User-agent: Mutt/1.3.28i

Poonam,

I think the limit in Oracle is 255 elements in an IN list.
If you can, use a join or subquery.  Otherwise, you might
be able to OR a bunch of IN clauses together.  It won't
be fast, but if you're unable to use a join or subquery
(because the IN data comes from the program and not from
the database) but it should work.

Ray

On Mon, Mar 17, 2003 at 06:22:30PM -0500, Poonam Auluck wrote:
> 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
----------------------------------------------------------------------
Ray Ontko   rayo@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
Ray Ontko & Co.   Software Consulting Services   http://www.ontko.com/

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

References:
- [sqr-users] ORA-04031 Error --- How can I resolve this??
  - From: "Poonam Auluck" <poonam.auluck@ams.com>

Prev by Date: RE: [sqr-users] Use of Wingdings Special character
Next by Date: RE: [sqr-users] ORA-04031 Error --- How can I resolve this??
Previous by thread: Re: [sqr-users] ORA-04031 Error --- How can I resolve this??
Next by thread: Re: [sqr-users] ORA-04031 Error --- How can I resolve this??
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page