My problem is i need to display the status and the record count in separate rows.How can i do it
Example
SELECT 'A' STAT, COUNT(A) CNT,'B' STAT, COUNT(B) CNT,'C' STAT, COUNT(C) CNT
FROM TAB1;
OUTPUT SHUD BE LIKE
STAT COUNT
A A_cnt
B B_CNT
C C_CNT
I have one requirement.i.e I want to display 'Do you want to save the changes you have made?[YES/NO]' message when record status is changed.In my form if i enter some value to the actual qty(db item) and press 'x' button it does't prompting 'Do you want to save the changes you have made?' message.
For displaying this message i have written following code in the APP_CUSTOM body:
IF (wnd = 'XXMZ_MRS_DET')
THEN
lv_wnd_stat := get_window_property('XXMZ_SUMMARY',VISIBLE);
if lv_wnd_stat = 'TRUE' THEN
IF :SYSTEM.form_status = 'CHANGED' THEN
CLEAR_BLOCK(ASK_COMMIT);
ELSE
[code]....
if i write above code in the app_custom body it's prompting the above message.But if i click 'YES' it does't save the records?
How can i save the records when i click 'yes'?
Our application has a homepage that displays results of several SQL statements that are defined as reports. One report in particular should only display a custom message when Count of Invoices having a particular status > 0, and not the actual invoice count itself.
I have gotten the layout to not display the count but I need to be able to display a custom message that says "Problem Invoices exist. Please see Invoices Report for more information." I've looked over the Report Attributes definition page but I cannot find how to display this custom message.
CREATE TABLE stud(id number,Status CHAR(1))
INSERT INTO stud values(10,'Y')
INSERT INTO stud values(10,'Y')
INSERT INTO stud values(10,'Y')
INSERT INTO stud values(10,'N')
[code]...
ID STATUS
10 Y
10 Y
10 Y
10 N
20 Y
[code]...
I want to display the out put the IDs which are having the status both 'Y' and 'N'The output should be
ID STATUS
10 Y
10 Y
10 Y
10 N
20 Y
20 N
20 N
i am migrating forms 6i to 10g, when i compile one form to checking purpose all are successfully fine but the record status bar not showing there fore message are not appear how to solve this problem
View 1 Replies View RelatedI am manually committing the form by writing commit statement in key - commit trigger for some reason.
Everything is working fine. I want to display a message if some necessary fields are not filled properly, like if a user filled half table and mistakenly he press the commit button. Here I want to show him a message that you can't saved the data because the table is not completely filled.
I have a form which will be processing various steps each might take some time. To facilitate the user, I have included some status displaying after every process completed. To refresh the form I have user SYNCHRONIZE to display the status of ever process after completion of every process.
It is working perfectly if am not minimizing the forms screen or opeing another window. After doing anyone of the above and when I reopen the form again, the SYNCHRONIZE is not working and am not able to see the form refreshing after every process as I have planned.
I am trying to use model clause to get comma separate single row for multiple rows. My scenario is like this:
SQL> desc test1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
VALUE CHAR(6)
SQL> select * from test1 order by id;
ID VALUE
---------- ------
1 Value1
2 Value2
3 Value3
4 Value4
5 Value4
6
7 value5
8
The query that I have is:
SQL> with t as
2 ( select distinct substr(value,2) value
3 from test1
4 model
5 ignore nav
6 dimension by (id)
7 measures (cast(value as varchar2(100)) value)
8 rules
9 ( value[any] order by id = value[cv()-1] || ',' || value[cv()]
10 )
11 )
12 select max(value) oneline
13 from t;
ONELINE
---------------------------------------------------------------------------------------------------
Value1,Value2,Value3,Value4,Value4,,value5,
what I want is : null value should not come and duplicate value should not come (Value4 in output above)
I have one multi record block in that i have one LOV..If i choose lov in first record that won't be display in second record lov...
View 4 Replies View RelatedI want to get top two rows based on ACCT_UNIT & order by status_date, if there is only one row on acct_unit, get one row. IF more than two rows available, want to get the top two rows based on status_date.
SELECT ACTIVE_STATUS, ACCT_UNIT, DESCRIPTION, DIRECTOR, DIRECTOR2, STATUS_DATE, OBJ_ID, STATUS_FLAG, SUR_KEY
FROM STSI
I need a query to display the office and count(*)
where rownum > 6 i.e
here is the data
office
------
hq
hq
hq
hq
hq
hq1
hq1
[code]....
I need a query to display the top 3 counts and the 4 row should be sum of count of other offices.
my result should look like this
office count(*)
--------- --------
hq 5
hq1 4
hq2 3
other 10
I have to display count of employees that belongs to different categories.
is the situatio There is a category table CATEGORY with three columns (PK,NAME,TREEPOSITION) and we have categories A, B, C these three categories can further have sub-categories so the treeposition for the sub categories will be followed by their root category with _ 'symbol'
Now I have table for the employees with 3 columns (pk,name,category_id), where employees.category_id=category.pk So I want to calculate the number of employees in each category or sub-category.
since the number of categories will be large and each will be having different names so going through names will be bad option left is grouping through the treepostion
the problem is I cant use like using IN for the TREEPOSITION. .
TABLE EMPLOYEES ( PK,NAME,CATEGORY_ID)
TBALE CATEGORIES (PK,NAME,TREEPOSITION)
What is the maximum number of rows that is possible to be produced by inner joining two tables having N and M (M < N) number of records on a non-prime column?
View 3 Replies View RelatedI am trying to get a record count of this table and i get 2 different output , select count(*) shows 68560 and after updating the stats the NUM_ROWS shows 68323, why the difference .
SQL> select count(*) from dcepcd;
COUNT(*)
----------
68560
SQL> select num_rows from dba_tables where table_name='DCEPCD';
NUM_ROWS
----------
68323
I'm trying to work out how to take a table like this:
IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09
And display the data like this:
IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09
As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.
We are trying insert records from a select query in to temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?
It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.
Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem
Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.
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.
Is there any defined record count range for the following ways of bulk insert :
INSERT INTO ABCTEMP SELECT * FROM DEFTEMP;
OR
through a cursor, bulk fetch and bulk insert under a loop.
Why showing at the status bar "3 records applied and saved" instated of "2 records applied and saved". 2 means 1 for Header and 1 for Detail. How I change status bar.
View 7 Replies View RelatedI am using an query to fetch the data from oracle DB and fill dataset using oledb dataadapter in ASP.net.When i run the same query in PL/SQL i am getting 14952 records,but when i am filling it to dataset i am getting only 13700 records.
View 2 Replies View RelatedI think this is easier than I think but all I want to do is count the distinct rows in a table.
WITH data AS
(SELECT 1 id1, 1 id2, 111 val1
FROM dual
UNION ALL
SELECT 1, 2, 222
FROM dual
UNION ALL
[Code]..
seems to get what I want
ID1VAL1CNT1
111114
212224
321114
422224
but can the count be done in a stand alone query such as??
SELECT DISTINCT id1,
val1,
COUNT(*) over() cnt1,
COUNT(*) over(PARTITION BY id1) cnt2
FROM data
ORDER BY id1, val1
NOT what I want
ID1VAL1CNT1CNT2
1111152
2122252
3211153
4222253
I have tried to execute this query
select partition_name, num_rows from dba_tab_partitions where table_name='Partisi_Book_Issue';
it display 0 num_rows. But i have made non partitioned table to partitioned table with the same number of rows. how to display number of record in each partition in any table?
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 need to get the number of rows based on one condition for certain time period. For example, writing a query for 3(any) hrs , i need to get the output as count of rows as hourly basis. Like these:
Date Time Count
12/08/2010 16:00 234
12/08/2010 17:00 345
12/08/2010 18:00 645
Am working on a workbook to count the number of enrolments and withdrawals in the program. My data looks like this
name semester status year
A 1 enrol 2010
A 2 withdraw 2010
A 3 enrol 2010
B 1 enrol 2010
B 2 withdraw 2010
I want to count their latest status only. It should come up with
Total Enrol - 2
Total Withdrawn - 1
For total Withdrawn, I tried 'rank' and filter to equals 1 but it does not allow me. Is there any way to have this work?
Here's my calculation:(decode((FIRST_VALUE(status) OVER(PARTITION BY year, name ORDER BY semester DESC)),'withdraw', name)) It tells me that 'Aggregation of Analytic function not allowed'
I have created a sql loader control file to load a delimeter file in a table.
The file gets loaded into the table properly, however the commit is happening for every 64 records.
My aim is to rise the commit count by 1000 records.
I want to create a SELECT, that shall give back only a special amount of rows, depending on the sum of one of the selected fields.
At first a code sample of the complete selection:
SELECT DISTINCT mnr, ktxt,
(SELECT Sum(meng_4)FROM reldb d1 WHERE d1.mnr=d.mnr)qty
FROM reldb d
WHERE mnr IN (SELECT mnr FROM relac WHERE Lower(rlnr) NOT LIKE 'platte geprÃĪgt%')
AND saext='M'
ORDER BY qty DESC,ktxt;
This selection produces some lines of output (in my case i.e. like 300). What I want to see is only that much lines that the condition 'sum of all items listed below meng_4<=sum of all items meng_4 of the whole selection * 0.9' is fulfilled.
So, if the whole selection produces a total of 10000 as sum for all items meng_4, I want to see only that amount of rows that sums a total of at least 9000 for all items meng_4.
I hope, this specification is exactly enough to understand my intent.
1. Can I do this in a query?
2. If yes, what would this query look like?
from within plsql i am doing
insert into t_target (...) select (fiels,fields,fiels) from sourcetable;
commit;
i want to capture number of rows committed and assign it to a variable or insert it into a table, i can not rely on count of source table or t_target because both those tables are changing through out of the day/ data are being inserted and removed from those tables. is this possible
I have a following table,
create table test ( col1 date,col2 number);
Data:
col1 Col2
----------
NULL NULL
select * from test where col2=123 will give me null because It doesnt have any values, But can we display some harcoded value when I do not have anything ?
I have a form in which the fields are not database items i want to display the next record, i know i can use the next_record for the same but it does not work since it is not the database item, is there any way when the item is not the database item and i can see the next record. I use the cursor for loop but since it fecthes all the records in one go it cannot display one record at the time in the form..
View 3 Replies View Related