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
ADVERTISEMENT
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
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
View Related
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
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
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
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
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
Oct 1, 2009
query in order to display a hierarchy list. Example:
Database has three tables
Heading --> id, status, order, title
Sub-heading --> id, status, order, title, heading_id
Item --> id, status, order, title, heading_id, sub-heading_id, actual_content
order = the sequence in which they are to be displayed a sub-heading's heading_id refers to the parent heading, likewise an item's sub-heading_id refers to the parent sub-heading.
I assumed there was no need to track child content.
Now, the output format should be something like this, where the sub-headings are not repeated, nor are the items. Sort of like chapters in a book, things in a catalogue, or even topics in a forum.
Heading 1
Sub-heading 1
Item 1
Item 2
Item 3
Sub-heading 2
Item 1
Item 2
Item 3
Heading 2
Sub-heading 1
Item 1
Item 2
Item 3
Heading 3
Sub-heading 1
Item 1
Item 2
Item 3
Sub-heading 2
Item 1
Item 2
Item 3
Sub-heading 3
Item 1
Item 2
Item 3
Where I'm getting hung up is how to query the data so it's quick, efficient, clean and easy to read and maintain. I've only gotten to listing the sub-headings and already the query looks convoluted. So, how should I approach this? Should it be done all in one query, and always checking against the results to know when to print a new heading or sub heading. Or should I run multiple queries and save the results to a variable, and then run sub-queries off that? Is there a better way to do this, am I going about it the hard way or am I in the right direction and just haven't gone far enough with it yet?
View 11 Replies
View Related
Mar 29, 2010
I had a table main with 2 fields. please see the contents below.
seq name
a aaa
b bbb
c ccc
d ddd
1. My query is as below
select seq,name from main where seq in ('a','b','c','b','d','d') output of query is
a aaa
b bbb
c ccc
d ddd
but I need the output to be as shown below.
a aaa
b bbb
c ccc
b bbb
d ddd
d ddd
I need to display all the values in the same order as in the "IN" list.
View 18 Replies
View Related
Feb 6, 2012
I have to write a proc to display data based on user input for t_grp, and it should exclude grp0
So below is the proc I have written, and I want to display the report for all t_grp when user gives user_input as All or gives null. How can I acheieve this is in procedure.
table: target_data
create table target_data
(
t_mon varcahr2(6)
, t_grp varchar2(30)
, t_data varchar2(50)
)
;
INSERT INTO target_data VALUES ('Apr-11', 'grp1', 'some_data');
INSERT INTO target_data VALUES ('Apr-11', 'grp2', 'some_data');
INSERT INTO target_data VALUES ('Apr-11', 'grp4', 'some_data');
[code]......
Now the above proc is running fine, if I give the input as grp1 or any other t_grp.
View 2 Replies
View Related
Sep 9, 2012
I am using Apex 4.1 and I have a select list in my page. I know i can access the value of the selected item by :P1_SelectList, but I would like to know how to access the display value of the selected item.
View 6 Replies
View Related
Feb 15, 2013
i am using List Manager Item .i have use List of values (lov)
SELECT LD.LOOKUP_NAME d ,LD.ID r FROM CRM_LOOKUP_MAS LM,
CRM_LOOKUP_DTL LD
WHERE LM.LOOKUP_ID=LD.LOOKUP_ID
AND LM.LOOKUP_TYPE='TRAVEL RHYTHM' and LD.ACTIVE_FLAG='Y'
order by LD.LOOKUP_NAME
}
when i click to add new value and click then pop up window open with LD.LOOKUP_NAME display value but when i click on add Button then value added in to below box with LD.ID
i want to display here my LD.LOOKUP_NAME value into below box.
How can i do this.
View 0 Replies
View Related
Jul 14, 2013
I've gotten it to display the percentage of markup correctly(as in, multiplied by 100 with no decimal places), but I cannot for the life of me get the percentage sign to display on the right of the values(i.e. 40, but not 40%).
Here's my code so far:
SELECT title, to_char(round((retail-cost)/retail*100), '99')
AS "MARKUP %" FROM BOOKS;
I've tried concatenating with both the + sign and the ampersand (&) sign, to no avail.
View 1 Replies
View Related
Sep 3, 2012
I have Select list itemS based on Lookup tables. When the form in Edit mode, I want to prevent the end user from changing the value of these Select list items. How can I accomplish this ??? **If I change the type of the item to Display only, then it will show the returned value of the Select list.
View 1 Replies
View Related
Jul 16, 2012
I need to display quarter of current year in select list.
for example current year is 2012 so, i want Jan-Mar 2012,Apr-Jun 2012,July-Sept 2012,Oct-Dec 2012 in select list.
View 3 Replies
View Related
Jan 28, 2013
I've installed XE and I want to know what accounts are active. If I log in to SQL*Plus as sys as SYSDBA how can I display a list of all user and admin accounts? I want to know who can log in.
View 3 Replies
View Related
Mar 23, 2013
i want to display a list of all users on a page those currently accessing or using the application. How do i accomplish this requirement?
View 1 Replies
View Related
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
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
Sep 27, 2011
I am creating the Dynamic list but when i am compiling the form it gives the compilation error "No list elements defined for the list item".
I can eliminate it by entering the dummy list element but this dummy value will be displayed at form run time.
View 1 Replies
View Related
Dec 14, 2011
Im trying to list the products list of a client grouped by type of the product. Ex:
product type
prod.A acid
prod.B flavour
prod.C acid
prod.D cleaner
prod.E flavour
I want to list something as:
Acid
Prod.A
Prod.C
Cleaner
prod.D
Flavour
prod.B
prod.E
View 1 Replies
View Related
Oct 30, 2011
DECLARE
CURSOR GRP IS
SELECT RowNum rn, Letter_Group_ID||'-'||A_Desc AName,Letter_Group_ID
FROM Hrs_Group;
BEGIN
Clear_list('Letter_Group_ID');
FOR I IN GRP LOOP
Add_List_Element('Letter_Group_ID',I.rn,I.AName,I.Letter_Group_ID);
end loop;
END;
FRM-30351: No list elements defined for list item.
List LETTER_GROUP_ID
View 4 Replies
View Related
Apr 22, 2013
I just want to list and group all my tables that are linked together by constraints. I just want my tables to be able to be listed together as one particular database. my tables are , CUSTOMER, ORDER_INFO, ORDER_LINE, PRODUCT. They're all linked together by way of constraint and I want to list and print them all together as one DB. HOW DO I put them all in one schema and then also list them all together and print/illustrate them as one. also, I tried to import them into their own scheme but i ran into a series of probs regaurding the .dmp file being read.
View 2 Replies
View Related
Sep 1, 2013
I want to get the list of values which is not the given list .
I have the below data in my emp table
SQL> select emp_id from emp;
EMP_ID
----------
101
102
103
104
105
106
108
110
120
220
225
11 rows selected.
But I need to display the values from the list of values which are not in the emp table . So the result will be '3000,3002,3003'.
SQL> select emp_id from emp where emp_id ?;
EMP_ID
----------
3000
3002
3003
View 9 Replies
View Related
Mar 11, 2013
Following query is giving me the required output but i want to use reg_exp from same output.
SELECT TO_NUMBER ((SUBSTR (narr,
INSTR (narr, '#') + 1,
(INSTR (narr, 'DT')) - (INSTR (narr, '#') + 1)
) "Bill No"
FROM (SELECT '25 NOS. BILL BOOK FOR FAISALABAD @80/- TH A.R. PRINTER AGST BILL#21 DT:31-01-2013 TH
[code]...
Required Output using Reg_Exp
SQL>/
Bill No
----------
21
9
View 6 Replies
View Related
Feb 2, 2010
One function returns table metadata in xml form. This works for current schema but not for other schemas. Is it a privilege problem?
my code is..
CREATE OR REPLACE FUNCTION TEST.F_DBEG2
RETURN XMLTYPE
AS
Handle NUMBER;
V_OUTPUT_TOTAL XMLTYPE;
V_CNT NUMBER := 0;
BEGIN
[code]....
but when i select the object of another schema, got output.
SELECT * FROM TEST_NEW.DEPT;
Which privilege i need?
View 9 Replies
View Related
Jan 12, 2012
Here i have one PL/SQL block which will returns the age of an employee using his id.
SET SERVEROUTPUT ON;
DECLARE
v_num NUMBER;
v_days NUMBER;
[code]...
It will returns an output of 27..I tried the same in Forms 6i using a text field and a button with a trigger "when_button_pressed". when i am entering the same id of employee i am getting a totally different answer
DECLARE
v_num NUMBER;
v_days NUMBER;
BEGIN
SELECT to_date(sysdate)-to_date(dob)
INTO v_num
FROM customer_details
WHERE application_id=:block3.day;
v_days :=floor(v_num/366);
MESSAGE(v_days);
MESSAGE(v_days);
END;
It is giving me a result of -73...i cleared my problem. but i cound not understand the internal work happening inside the forms and pl sql machine.
View 10 Replies
View Related
Jun 13, 2013
percentage_pointsmarksage95336.590346.575376.550496.595347903777549750337 if i fired the statementselect percentage_points from table1 where marks=33 and age=6.5 then output should beuser has score percentage between 90 and 95i can write case condition between 1 to 100 percentages but any other condition which easy
View 1 Replies
View Related
Dec 4, 2012
When I run the below procedure it does not return any data. But it returns stating Procedure created. When I just run the query it returns the result.
So what am I going wrong here.
set serveroutput on
CREATE OR REPLACE PROCEDURE PROD.STATUS_COUNTS
(
p_count IN OUT number,
p_status IN OUT varchar2,
p_mpp IN OUT number
)
AS
BEGIN
[Code]....
View 7 Replies
View Related
