Get the following error from Oracle ORA-24333: zero iteration count Cause: An iteration count of zero was specified for the statementAction: Specify the number of times this statement must be executed on whether is this a Oracle bug or if not what should be set to avoid this failure.
View 2 RepliesError@ ORA-24333 - Zero Iteration Count.
Here is the program,..for my report, Strangely I am able to run the query Successfully for my report in SQL developer and report builder and I can even give a bind parameters using both , but the requirement for this report is I should ether run this report in discoverer or xml For that I have successfully created a report in report builder and when I create a Concurrent Program and Run It,… I get an error ORA-24333 - Zero Iteration Count
=> with Tender As ( Select F1.Store_code as Store_Code, ……
With Tenders As ( Select F1.Store_code AS Store_code,
F2.Tender_type_code AS Tender_type_code,
Sum(F2.Payment_amount) As Trans_amount,
Count(F2.trx_id) as Trans_count
From ors_transactions F1
[code]....
I am trying to determine the number of times a value appears and display the count. However the value can only be counted once per 'trip' even though it may appear several times per trip.
for e.g.
Quote:trip table
-------
trip_id start_date end_date duration
445 01-jan-12 03-jan-12 3
Quote:pickup table
--------
pickup_id trip_id company
1 445 randomname
2 445 randomname
3 445 google.inc
4 878 randomname
with the above data the expected value would be two because the trip id appears twice so it was just the one trip - given a count of one. I am not sure how create a query to check this.
how many number of times the sql's had been executed/run in an oracle session. Executions in V$sql, is it the right place to look at? Will it give the number of times the sql has been executed in a particular session?
View 3 Replies View Relatedim working with apex 4.2.1 and when i try to define a Dynamic action on any item, if the action type is "Set Value" or execute SQL/Javascript code it should appear a text field that let me define the action taking place. However this wont do..
this is what happens:
And this is what should happen:
I know that patch 4.2.2 solves some issues regarding dynamic actions but in the bug report there's no reference to such a problem.
how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1
View 5 Replies View RelatedI have one table as shown below,
CREATE TABLE MAT
(matrl varchar2(100),
date_man date,
weight number(10)
);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat1','12-DEC-10',100);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat2','13-DEC-10',200);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat3','21-DEC-10',300);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat4','26-DEC-10',400);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat5','22-DEC-10',500);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat6','02-DEC-10',600);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat7','23-DEC-10',700);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat8','07-DEC-10',800);
The data will look like this .
matrldate_manWeight
mat112-Dec-10100
mat213-Dec-10200
mat321-Dec-10300
mat426-Dec-10400
mat522-Dec-10500
mat602-Dec-10600
mat723-Dec-10700
mat807-Dec-10800
I need to order by this data in terms of date and need to get the data as shown below. Acc_wt is accumulated weight till the material.
Date Weightacc_wt
mat602-Dec-106000
mat807-Dec-10800600
mat112-Dec-101001400
mat213-Dec-102001500
mat321-Dec-103001700
mat522-Dec-105002000
mat723-Dec-107002500
mat426-Dec-104003200
Can we make use of oracle lag and lead functions here?Or some other methods?
using the sql statement in SQL server. I noticed some of the sql syntax are different. My sql statement is as below;
---Select fieldA, Sum(fieldB+fieldC) as fieldD, fieldE, fieldF from TableA group by fieldA, fieldE, fieldF ---
the output will be as below;
fieldA fieldD fieldE fieldF
------ ------------- ------ ------
fieldA fieldB+fieldC fieldE fieldF
However, I need to have the total of no of records displayed for the sql statement and I need it in one sql statement with the one above so that I can display the total no of records along with the information for each field.
I need to count number of elements in the same catagory of an array.. For example, an array consists of {'a','b','c','c','a','d','c'} means, i need to display like a=2, b=2, c=3, d=1.
I have written the below code.
declare
type array_val is varray(10) of varchar2(15000);
counter number:=0;
SMQ_NAME ARRAY_VAL:=ARRAY_VAL();
begin
[code]....
But its not showing exact output as my requirement..
To count the number of rows to the below table entries with certain conditions
my table
col1(varchar2(4000)) col2(varchar2(10))
3-1808116101 3-Standard
3-1808312141 3-Standard
3-1808312061 3-Standard
3-1807714441 2-Significant
3-1808284201 2-Significant
3-1808278361 2-Significant
3-1808284111 3-Standard
3-1807714261 3-Standard
3-1807128371 3-Standard
3-1807128351 3-Standard
3-1808301721 2-Significant
3-1808301701 3-Standard
3-1808322501 2-Significant
where conditions:
1)need to take the count of rows(col1).where col2 value is 3 or 2 .
2)value 3 or 2 is extracted and conditioned in the query
i have two tables one as test_master and other one limit master.
test master table structure
ct_id ct_a ct_b ct_C ct_d ct_e...... etc
1 -- -- -- -- --
1 -- -- -- -- --
2 -- -- -- -- --
limit master structure
limit_id ct_a_limit ct_b_limit ct_c_limit ct_d_limit ct_e_limt ..... etc
1 -- -- -- -- -- -- -- --
2 -- -- -- -- -- -- -- --
ct_id and limit_id is match column used for mapping.
now i want the total count of no of records in test_master in which test_master column value not exceed limit master column limit value if any one column exceeds its limit then the same row will be in my count criteria.
how to achieve the same using sql/oracle query.
I want to know that whether we can write a query to add total number of count of each table.
for example:
select count(*) from emp;
count(*)
14
select count(*) from emp_1;
count(*)
15
select count(*) from emp_2;
count(*)
16
My question is that is it possible to add this all the three results and get the value as 45.
ApplicationCurrent SQLbxaptxn8wf54jCurrent SQL CommandSELECTLast Call Duration26:7 (mm:ss)
SQL TraceDISABLEDCurrent SQL Trace Level1Trace With Wait InformationDISABLEDTrace With Bind InformationDISABLED
Open Cursors71ProgramUnavailableServicePRODCurrent ModuleIEUSEUWQCurrent ActionFRM:JOSONC1:RBI Collections Ag.
The above is from dbconsole - it shows the "current module" and the "current action".
GeneralModuleIEUSEUWQActionFRM:VICKERYJ:RBI
Collections AgeParsing SchemaAPPSPL/SQL Source (Line Number)Not ApplicableSQL Profilen/aSQL Plan Baselinen/a The above is also from the same module, but this time we have "action".
What would be the difference between "action" and "current action"?? I have checked several documents, such as URL....which deal with "action", but nothing to reference "current action" 11.2.0.3RHEL 5.5
i have inner query result like
Emp status
----- ----------
ram a
ram a
ram b
ram a
i want write a query to find a maximum occurrence of status and update in status column.
result should be
Emp status
----- ----------
ram a
ram a
ram a
ram a
i tried lot of thing but not to go.
I have been asked to show the names and room number according to students who have 4 classes in a room.
select s.name, r.room_no
from s, r
where r.room_no = u.room_no
and e.unit_code = u.unit_code
GROUP BY s.name, r.room_no
HAVING COUNT(DISTINCT s.stu_no) > 4;
However I get the error 'ERROR at line 4:
ORA-00904: "U"."UNIT_CODE": invalid identifier'
I have a string like this:
s_list varchar2(234) :=
'asdasd
asfsdf
dsfsdfs
dfsdfs';
How can I find the number of lines in this string? I tried using
INSTR('s_list', '
', 1, 1)
but it gives 0.
Is there any inbuilt function/proc SQL or PL/SQL which can do this?
I need writing sql which can return the Count of Comma's in a string. Here is my table and data
CREATE TABLE TEST1(SNO NUMBER,STR1 VARCHAR2(30));
INSERT INTO TEST1 VALUES(1234,'ABCD,LL LT,MP');
INSERT INTO TEST1 VALUES(1456,'PP MR');
INSERT INTO TEST1 VALUES(1589,NULL);
INSERT INTO TEST1 VALUES(1897,'PP MR,FTR CLR ON');
Here is the output I am expecting
SNO STR1 STR1_COUNT
1234 ABCD,LL LT,MP 3
1456 PP MR 1
1589 0
1897 PP MR,FTR CLR ON 2
Basically I need to the count of Words separated by comma
how to count how many items present in a particular data block in oracle forms 10g.whether it is a text_item or display_item or list_item etc is there any method to do this.
i have written this when-button pressed but the problem is how to get the next item name in the block
DECLARE
V_COUNT NUMBER:=0;
V_FIRSTITEM VARCHAR2(100):='';
BEGIN
GO_BLOCK('BLOCK_ITEMCOUNT');
V_FIRSTITEM := GET_BLOCK_PROPERTY('BLOCK_ITEMCOUNT', FIRST_ITEM);
LOOP
[code]....
I have two columns Department and EmpName:
Department EmpName
____________________________________
Sales empname1
Sales empname2
Marketing empname3
Development empname4
Now I want to count the number of employees in every department. I want the output to be
Department Total
______________________
Sales 2
Marketing 1
Development 1
I am retrieving names of the department through a subquery
The query I am trying to execute is:
SELECT Department, Employee FROM
( SELECT ...query from other table) AS Department, count( A.EmpName) AS Employee
FROM Employer A, EmployeeInfo B
WHERE (A.EmpID = B.EmpID AND A.EmpCategory like 'Category2')
GROUP BY Department
I know that you cannot group by using aliases and hence a little work around, but still the query isn't working.
I'm trying to do a count on the number of combinations of country/state codes that are invalid (in Australia) for driving licenses.
select COUNTRY_CODE, STATE_CODE, count(*) from
(select COUNTRY_CODE, STATE_CODE from CUSTOMER_TABLE
where DRIVING_LICENCE is not null
and not (COUNTRY_CODE in ('AUST') AND STATE_CODE in ('VIC', 'NSW', 'SA', 'QLD', 'NT', 'TAS', 'WA', 'ACT')))
group by COUNTRY_CODE, STATE_CODE
The output is okay...for example I get these results:
INTINT
NZSI
NZINT
AUSTINT
NZNSW
NZ <null>
However, what I am missing is the combination of "AUST" & <null> for country/state respectively. Am I writing the code correctly?
we are inserting a data in table by selecting data from another table.This is part of a very big procedure and the code is like
Loop
SELECTMAX(biz_trn_no) + 1
INTOv_biz_tran_no
FROMnew_business_transactions
WHEREnb_srno = nb_sr_no;
[code]....
Is there any way I can get this functionality without using a sequence? I assume corelated query would be slow and analytic.
I need to concatenate string to the number field in an update statement like this:
update test1 set model_pin = seq_no || '_' || model
where eoc_code like 'AEW%'
When I run this command in sql , I get ERROR at line 1:
ORA-01722: invalid number
I am facing a problem in bulk insert using SELECT statement.My sql statement is like below.
strQuery :='INSERT INTO TAB3
(SELECT t1.c1,t2.c2
FROM TAB1 t1, TAB2 t2
WHERE t1.c1 = t2.c1
AND t1.c3 between 10 and 15 AND)' ....... some other conditions.
EXECUTE IMMEDIATE strQuery...These SQL statements are inside a procedure. And this procedure is called from C#.The number of rows returned by the "SELECT" query is 70.
On the very first time call of this procedure, the number rows inserted using strQuery is 70. But in the next time call (in the same transaction) of the procedure, the number rows inserted is only 50.And further if we are repeating calling this procedure, it will insert sometimes 70 or 50 etc. It is showing some inconsistency.On my initial analysis it is found that, the default optimizer is "ALL_ROWS". When i changed the optimizer mode to "rule", this issue is not coming.I am using Oracle 10g R2 version.
Is there any way i can Get how many rows are processing with UPDATE statement while the Update statement is still running.
View 2 Replies View RelatedI have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.
CREATE OR REPLACE PROCEDURE Del_emp IS
del_records NUMBER:=0;
BEGIN
DELETE
FROM candidate c
WHERE empid in
(select c.empid
from employee e,
candidate c
where e.empid = c.empid
and e.emp_stat = 'TERMINATED'
);
[code]....
I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProdMy problem is, There is data for IN time and OUT time data type is DATE. I need to calculate OT hour from this two field and store into a third one column. But i'm confuse what should be the data type of the third column. Is it number or else ?
I know difference of two date column is number. So, first i need to know that data type i should use ?
Secondly, how i get the sum of the all stored time ? for example data are
1.30 -- One hour and thirty min.
2.45
3.50
I found this query in one of my stored procedures that updates a key for a value in a data table by reading the information from a master table.
The data table is: ITEM_INVENTORY
The master table is: MASTER_SOURCE_SYSTEM
UPDATE ITEM_INVENTORY I
SET I.SOURCE_SYSTEM_ID =
(SELECT NVL(M.SRC_SYS_ID,-100)
FROM MASTER_SOURCE_SYSTEM M
WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )
WHERE ORG_CODE = 'TNXC'
AND EXISTS (SELECT 1 FROM MASTER_SOURCE_SYSTEM M
WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )
The situation here is that:
1. There are about 15000 rows that match ORG_CODE = 'TNXC'.
2. The SOURCE_SYSTEM_CODE is same for all the 15000 rows and there is a matching entry for it in the MASTER_SOURCE_SYSTEM table.
My question is: Do both the inner select statements execute 15000 times?
The statement executes within a second and updates 15000 rows. How is this made possible?
i want one query which return minute between two times which is in this format: 12:00:00 and 06:00:00
so in this it should return 360 minutes.
How to get rows N times in a result set?
For obscure test purposes I need to modify an existing SQL query to emit the rows N times instead once. I'm aware of the possibility to "UNION ALL" the query with itself to get the all rows twice.
But as I require the resulting rows to be emitted around ten to hundred times this approach doesn't seem sensible to me. Not to speak of the missing possibility to parametrize the number of "repetitions".
I am dealing with a bunch of tables containing sales information for an New Zealand organisation. The sale datetime has been recorded as UTC.
New Zealand operates Daylight Savings, so twice a year it changes its clocks.
When New Zealand is on standard time it is UTC+12.
When New Zealand is on daylight savings time it is UTC+13.
Thus an event which actually occurred when New Zealand was on standard time at 2011-08-31 15:20:52 local time, is recorded in the database as having occurred at 2011-08-31 03:20:52. However, an event that actually occurred when New Zealand was on daylight savings time at 2011-10-06 15:20:52 local time, is recorded in the database as having occurred at 2011-10-06 02:20:52.
I want to be able to read the sales dates from my table and convert them to the actual time in New Zealand when the event occurred. The table will contain data for sales that occurred in both standard and daylight savings times.
I do not think that the data has been stored with time zone information, simply that the application writing the data to the Oracle database, calculated the event time as UTC when it occurred and wrote that time to the table.
Does Oracle only know about what UTC-offset is in force right now or is it capable of determining what offset from UTC is required for any given historical date ?