SQL & PL/SQL :: Display Output IDs Which Are Having Status Both Y And N?

May 11, 2012

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

View 12 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Display The Status And Record Count In Separate Rows

Mar 6, 2013

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

View 13 Replies View Related

Forms :: Unable To Display A Message When Record Status Is Changed?

Jun 2, 2010

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

View 3 Replies View Related

Forms :: User SYNCHRONIZE To Display Status After Completion Of Every Process

Apr 27, 2011

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.

View 5 Replies View Related

SQL & PL/SQL :: Output Layout And Display Changes?

Oct 11, 2011

SQL

SELECT ug_name, rss_user_name
FROM ru_ug
WHERE rss_name = 'EXAMPLE' AND ug_name LIKE 'cn=SNG-PWI11_%'
ORDER BY ug_name

Output

UG_NAMERSS_USER_NAME
cn=SNG-PWI11_DBE_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname55,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_DBE_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname49,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_DBE_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname19,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_I_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname932,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_I_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname56,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_I_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname77,ou=Netgroup,dc=TMM,dc=netops

But I wish other output:

UG_NAME1 UG_NAME2
RSS_USER_NAME1 RSS_USER_NAME3
RSS_USER_NAME2

Table:

CREATE TABLE RU_UG
(
RSS_USER_NAME VARCHAR2(255 BYTE) NOT NULL,
UG_NAME VARCHAR2(255 BYTE) NOT NULL,
RSS_NAME VARCHAR2(32 BYTE) NOT NULL,
RSS_TYPE VARCHAR2(12 BYTE) NOT NULL,

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Display In List Non-Existent Value In Output

Mar 6, 2012

I am not sure if this is even possible however I will give it a try here. is there a way to take a value from an In List that doesn't exist in the table and get SQL PLus to output that value with an assigned decode construct. example.

1 select decode(count(object_name),1,'EXIST',null,'NOT EXIST',0,'NOT EXIST'),
2 object_name, object_type
3 from dba_objects where object_name in (
4 'GV_$METRICNAME',
5 'GV$METRICNAME',
6 'GV_$METRICGROUP',
7 'GV$METRICGROUP',
8 'ABC'
9 )
10* group by object_name, object_type
SQL> /

DECODE(CO OBJECT_NAME OBJECT_TYPE
--------- ------------------------- -------------------
EXIST GV_$METRICNAME VIEW
EXIST GV_$METRICGROUP VIEW
EXIST GV$METRICNAME SYNONYM
EXIST GV$METRICGROUP SYNONYM

and have it look like this:

DECODE(CO OBJECT_NAME OBJECT_TYPE
--------- ------------------------- -------------------
EXIST GV_$METRICNAME VIEW
EXIST GV_$METRICGROUP VIEW
EXIST GV$METRICNAME SYNONYM
EXIST GV$METRICGROUP SYNONYM
NOT EXIST ABC

View 4 Replies View Related

SQL & PL/SQL :: Display Output For Null Column

Jul 24, 2012

I am having following table, and below are the out put and desired output.

CREATE TABLE tbl1
(
mon VARCHAR2(10)
, grp VARCHAR2(50)
, visits NUMBER
, redirect VARCHAR(50)
)
;

[Code]....

Query:

SELECT
mon
, grp
, SUM(visits)
FROM
tbl1
WHERE
redirect IS NOT NULL
GROUP BY mon, grp
;

Output:

May-12 Green 16
May-12 Blue 20
May-12 Yellow 13

Desired Output:

May-12 Green 16
May-12 Blue 20
May-12 Yellow 13
May-12 Red 0
May-12 Orange 0

How can this be acheived?

View 1 Replies View Related

Client Tools :: Display Output In Sql

May 15, 2010

having trouble displaying stuff on screen. It compiles the program successfully but just doesnt show any output Try using this (SET SERVEROUTPUT ON SIZE 4000) does nothing though. here my code

DECLARE
/*This program coverts a date from one fomat eg 1/31/10
into another format eg January 31,2010 */
/*declaring variables
assign a date that is in one format*/
EXAMPLE_DATE DATE := TO_DATE(' 1/31/06',' MM/DD/YY' );
/*finding the position of where the forward slash are*/
position_finder NUMBER := INSTR(EXAMPLE_DATE,'/');

[Code]....

View 2 Replies View Related

Sql To Display Output In Horizontal View?

Sep 8, 2012

I need to display the sql query output in horizontal manner. Below I have given the script.

create table TestTable (id number, name varchar2(10))
insert into TestTable values (1, 'John')
insert into TestTable values (2, 'Mckensy')
insert into TestTable values (3, 'Valneech')
insert into TestTable values (4, 'Zeebra')

[code]....

But for my requirement, I need to display in horizontal manner.

ID   1       2       3           4
Name John    Mckensy Valneech    Zeebra

View 3 Replies View Related

PL/SQL :: Display Output Of O_store_rec Object

May 11, 2013

while compiling this pl/sql block i am getting error ora-6531 :reference to uninitialized collection.

Declare
O_error_message RTK_ERRORS.RTK_TEXT%TYPE;
O_store_rec STORE_SQL.STORE_REC;
I_message RIB_XSTOREDESC_REC;
I_message_type VARCHAR2(50);
result boolean;
Begin
O_store_rec.loc_trait_tbl := ORGANIZATION_SQL.LOC_TRAIT_TBL();
O_store_rec.walk_through_tbl := STORE_SQL.WALK_THROUGH_STORE_TBL();
[code]........

View 4 Replies View Related

SQL & PL/SQL :: Display Grid Output Using Execute Immediate Or SysRefCursor Constructs?

Mar 21, 2011

I am in a state of confusion and requires a solution for this urgently:I used to use 'Execute Immediate' statement or 'Sys_RefCursor' for forming parts of query dynamically based on user inputs and then finally execute them at runtime for updating database / saving results into a variable etc.

But as per the latest requirement, I wanted to have the resultset of such a query displayed in the grid area / results area of a database tool; say, SQL Navigator, Toad or PL/SQL developer.; just like you see the results of a normal 'SELECT * FROM TABLE1'; Is this really possible ??

see the below examples:

Eg: for Execute Immediate:

declare
vtest VARCHAR2 := NULL;
vdisplay VARCHAR2;
begin
vtest := 'SELECT FIELD_1 INTO vdisplay FROM TABLE_A';
vtest := trim(vtest) || 'WHERE FIELD_2 = ''XYZ''';
execute immediate TRIM(vtest);
dbms_output.put_line(vdisplay);
end;

---> This would display output into the 'Output' tab of the database tool / editor;

Eg: for SysRefCursor:

declare
vCur SYS_REFCURSOR;
vdisplay VARCHAR2;
begin
OPEN vCur for 'SELECT NULL' || var1 || 'WHERE FIELD_1 = ' || Value1;
FETCH vCur INTO vdisplay;
dbms_output.put_line(vdisplay);
end;

---> Even this would display output only into the 'Output' tab of the database tool / editor;

===> But my requirement is: Different from these, is there any way to get this output displayed into the normal output grid / result area of a database tool ? just like you see the results of a normal 'SELECT * FROM TABLE1' query ?

View 5 Replies View Related

SQL & PL/SQL :: How To Print The Multiple Output Statements In Single Output

Jul 1, 2013

DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......

this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.

View 2 Replies View Related

SQL & PL/SQL :: Splitting 1 Output Row Into 3 Output Rows

Sep 25, 2013

Currently I have a requirement where I need to create 2 more output rows using each result row.

In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.

--Create tables charges
create table charges
(
charge_type varchar2(10) ,
charge number
);

[Code]....

My expected output should be like below:

Item_type amount
-------------------- ----------
charge1 100
Charge1_tax1 10
Charge1_tax2 20
charge2 200
Charge2_tax1 20
Charge2_tax2 40

how I can achieve the expected output using a single sql query

View 6 Replies View Related

Status Online Logfile

Nov 2, 2010

I delete member logfile from disk with statement: shred -fu /u01/app/oracle/redologs/activeredolog/DB1/redo0102.log

But if I check status one logfile with statement:

select status from v$logfile wheremember='/u01/app/oracle/redologs/activeredolog/DB1/redo0102.log';

Status is still null. I think status should be change.

My database is 11.2.0.1 with single instance.

Status is changed after restart instance.

Haw can I monitor status logfiles without restart instance?

View 2 Replies View Related

SQL & PL/SQL :: Batch ID From Child Status

Mar 27, 2013

Im looking for a query which returns the batch for which all the child should either be in 'A_STATUS','B_STATUS' or 'C_STATUS'. In this query im expecting a query which returns batch 2,3 and 4.

create table batch (batchid number);
insert into batch values(1);
insert into batch values(2);
insert into batch values(3);
insert into batch values(4);

[Code]...

View 9 Replies View Related

Forms :: Record Status Bar?

Jun 2, 2011

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 Related

SQL & PL/SQL :: Query To Get Overall Status For Clients

Sep 11, 2010

I have a clients table called CLIENTS. One of the fields in this table is called 'status'

I need to set this status field, depending on if the transactions for this client have passed.

The transactions are in a table called TXNS and has fields
client_id, txn_id, txn_status

txn_status can be 'success' or 'failed'

I need to somehow set the overall 'status' field on CLIENTS to success or fail depending on if any transactions failed or not

So for every client I want to list the status, any query that can possible list the overall status for the client?

select client_id, MAX(status)
from txns
group by client_id

but the max(status) bit needs to get the overall status part

View 10 Replies View Related

SQL & PL/SQL :: Get Continuous Dates With Same Status Value

Feb 23, 2012

I'm looking for a way to get continuous dates as a period in the result.My tabledata contains a date and a status, I want to get continuous dates with the same status value.

That's my tabledata:
select * from (
select to_date('01022012', 'ddmmyyyy') as thedate, 1 as status from dual
union select to_date('02022012', 'ddmmyyyy') as thedate, 1 as status from dual
union select to_date('03022012', 'ddmmyyyy') as thedate, 2 as status from dual

[code]...

THEDATE STATUS
---------- ------
01.02.2012 1
02.02.2012 1
03.02.2012 2
04.02.2012 1
05.02.2012 1

[code]...

And that's the result I am looking for:
select * from (
select to_date('01022012', 'ddmmyyyy') as startdate,
to_date('02022012', 'ddmmyyyy') as enddate from dual
union select to_date('04022012', 'ddmmyyyy') as startdate,

[code]....

View 6 Replies View Related

SQL & PL/SQL :: Monitoring Status Of Procedure?

Feb 24, 2010

I've got a pl/sql procedure that i would like to monitor its progress. Its been running for quite a few hours now and i dont really know if it is stuck in a loop or if it is just taking too long read/execute queries. Is there a way to find out what is the status of the procedure? or what it is currently doing?

I looked at views like v$sessions or v$session_longops but they dont actually tell me what it is currently doing. All of them just point to the call to the pl/sql procedure. Also, is it possible to look at the physical files on the filesystem to know if there is actually any processing going on and files are being written to? Is this possible or a files written to only after a commit?

View 17 Replies View Related

Upgrade :: Get Listener Status?

Oct 31, 2012

We are upgrading 10.2.0.4 enterprise edition single instance to 11.2.0.3 enterprise edition single edition manually. What should the listener status while doing upgradation. This is my first upgradation project.

View 5 Replies View Related

How To Change Remote_login_password File Status

Jan 21, 2006

How to change the status of remote_login_password file!

View 14 Replies View Related

Create A Procedure To Update Status?

Mar 10, 2011

create a procedure that will update the column status from active to closed after every two years.

View 4 Replies View Related

How To Check Status Of Import (old Method) Job

Aug 23, 2011

Question:

How to find out the status of the import job(how much work is done, ETA), i have started the job few days back it still running on the server. i am using import not datapump, because i got the dump which took with export. DB version is 11.2.0.2.0

for datapump i know i can use the following queries, but these are not giving any output l think these are only for datapump

select sid, serial#, sofar, totalwork from v$session_longops;
select sid, serial# from v$session s, dba_datapump_sessions d where s.saddr = d.saddr;

View 1 Replies View Related

How To Change Tablespace Plugged_in (no Status To Yes)

Aug 6, 2010

how to change tablespace plugged_in =no status to yes

View 1 Replies View Related

SQL & PL/SQL :: Change The Temp Table Status?

Jun 21, 2011

In one of the databases, we have created more temp tables as on commit preserve rows. but i want to change the on commit preserve rows to on commit delete rows as per application requirements.I have searched the google but i didnt find any alter scripts for it.?

View 1 Replies View Related

Forms :: How To Check Record Status

Sep 7, 2011

I 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.

View 17 Replies View Related

SQL & PL/SQL :: Fetch Records Based On 2 Status

Jan 25, 2013

create table test_exp (oid number, ioid number, status varchar2(20));

Below are the insert statements.

Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 100, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 101, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 102, 'CANCELLED');
Insert into TEST_EXP (OID, IOID, STATUS) Values (2, 103, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (3, 104, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 105, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 106, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 107, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 108, 'CANCELLED');
[code]...

Now my problem is we should fetch the data based on the below rules

If an OID contains 2 IOIDs for which there is a NEW and DISCO status attached, then fetch the 2 records
If an OID has only 1 of these status, then ignore the same
If an OID has none of the 2 status, then ignore the same.

The expected output will be like below:

OID|IOID|STATUS
1 |100 |NEW
1 |101 |DISCO
4 |105 |DISCO
4 |106 |NEW

View 4 Replies View Related

SQL & PL/SQL :: Query On V$session - Status Column

Dec 2, 2011

If a session runs a query, the status will be inactive after completing the database call. I know this concept. But I just want to know what will be session status, if it running a procedure contains many select queries, DML quries and loop? Whether it will be active until procedure completes or it will be switching active and inactive?

View 3 Replies View Related

Forms :: Want To Check For Checkbox Status With One More Corresponding Value

Aug 3, 2011

I was working with forms and found a problem related to accessing of status of checkbox. In a block with multi record to be checked, if we want to check all the checkbox status which is already clicked, Then what should i do.

Now i am using the function Checkbox_check as:

if Checkbox_Checked('FVA.STATUS')

But the problem with this function, it is giving status of last clicked checkbox only.

View 17 Replies View Related

RAC & Failsafe :: RMAN Backup Status?

Oct 14, 2012

How to verify if the backups are successfully completed in RAC environment ?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved