PL/SQL :: Single Select Output
Jun 19, 2012
Having a select query where the output looks as below, as you can see when there is no record for a particular date(01-JUn-12,03-JUN-12) the count will be zero.
SELECT TRUNC(updated_date) DATE1 , COUNT(1) COUNT FROM Temp_a
WHERE ZU_flag='N' AND circle_id 'NA'
AND TRUNC(updated_date) >=TRUNC(SYSDATE-18) AND TRUNC(updated_date) <=TRUNC(SYSDATE)
GROUP BY TRUNC(updated_date)
ORDER BY TRUNC(updated_date) DESC;
O/p
DATE1 count
10-JUN-12 14208
08-JUN-12 307825
06-JUN-12 138790
05-JUN-12 167562
04-JUN-12 51870
02-JUN-12 130582
01-JUN-12 239806
But i require a query in such a way that when there is no record for a date , the date has to get populated with zero count
Excepted O/P
DATE1 count
10-JUN-12 14208
09-JUN-12 0
08-JUN-12 307825
07-JUN-12 0
06-JUN-12 138790
[Code]....
I have tried with Case conditions and try to fetch that data through Sequence generating Date(not useful). I can do this with Cusror, but i need this to be done through single select query
View 5 Replies
ADVERTISEMENT
Aug 11, 2013
I want to merge the following two sql statements into single output.
select id,count(*) from derailed where changed_on between to_date('26-july-13 18:30:00','DD-Mon-YY hh24:MI:SS') and to_date('01-August-13 18:29:00','DD-Mon-YY HH24:MI:SS') group by id;
select id,code from dbo;
View 11 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
Apr 12, 2011
I'm trying to return the number of records in my link table that contains the excursion_id I pass in by counting them. It doesn't seem to like the select count(*) into my output variable.
create or replace
PROCEDURE BOOK_PASSENGER(
EXCURSION_ID IN excursion_booking.excursion_id%TYPE,
PASSENGER_ID IN excursion_booking.passenger_id%TYPE,
NUMBER_BOOKED OUT NUMBER)
AS
BEGIN
INSERT INTO EXCURSION_BOOKING VALUES(EXCURSION_ID, PASSENGER_ID, NULL);
SELECT COUNT(*) INTO NUMBER_BOOKED FROM EXCURSION_BOOKING WHERE EXCURSION_ID = EXCURSION_ID;
END BOOK_PASSENGER;
View 6 Replies
View Related
Sep 19, 2013
write a query to get the first row after order by clause using single query alone.Example:I can write following query to select first rowselect * from (selec * from t order by col1) where rownum = 1;But here I should not use inline view to get the result. Because my original requirement needs to use this query in select list and it needs to use a column (of a table from the FROM clause) in the where clause of inline query. Because there is restriction that we can not use the column (of a table from the FROM clause) more than one level of inline query.
View 6 Replies
View Related
Apr 22, 2013
I am trying to find the best solution for the following problem
I have a table called HIERARCHY with 3 columns.
col1 col2 col3
------------------------
1
1 2
1 2 3
4 5
6 7 8
I would like to check if the table contains invalid records. Col1 , Col2 and Col3 are seen as hierarchy levels and col1 is the top level.
For instance record 1,2,3 (level3) is valid because it exists record 1,2 (level 2). Moreover 1,2 record (level 2) is valid because it exists record 1 (top level).
However 4,5 and 6,7,8 are not valid as higher levels are not completed.
How can I check in a single SELECT statement if the table contains invalid records? Maybe analitical functions or regular expressions?!
View 5 Replies
View Related
Sep 4, 2013
The description field in the item table has the single quote used as the symbol for feet. I have the same issue pulling from a last name field in other tables. (Like O'Connor)
select descrip into v_result
from c_ship_hist
where shipment_dtl_id = :SDID;
exception when others then null;
The error I get is "Missing right quote". How do I code around this issue without having to change the data?
View 8 Replies
View Related
Jul 8, 2011
i have a problem in the following query. i need to fetch the rows such that i want to fetch all the records keeping "segment1" column as distinct and sum all of the corresponding "quantities" column.
select prha.segment1 --as requisition_no
,prha.creation_date
,sum(prla.quantity)
,prha.description
[code]...
i tried to use the partition technique. using partition solved the problem apperently. the sum function worked but redundancy in "segment1" column still persists. i used the sum function only to extract the distinct "segment1" column and summing its corresponding "quantity" column (only quantity column differs in the redundant rows...)
the second query was like:
SELECT prha.segment1,
prha.creation_date,
SUM(prla.quantity) OVER(PARTITION BY prha.segment1) AS qty,
prha.DESCRIPTION,
[code]...
View 1 Replies
View Related
Oct 2, 2010
presenting the case as follows:There are two dates with time components like
26-sep-2010 13:00 and 29-sep-2010 19:00
In between these dates hours between 20.00 to 05.00 (night hours)need to be considered and In these night hours less than three hours can be ignored and between 3 to 9 hours should be treated as 1. Need the number of one's in between the dates.
Procedure, cursor solution is not needed and need single select statement.
View 5 Replies
View Related
May 17, 2010
select * from course_section
where term_id = (select term_id from term where term_desc in ('Spring 2006','Spring 2007'));
and it is showing:
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
View 5 Replies
View Related
Oct 17, 2012
I want to run multiple IF Else statements in a single select SQL, each statement is one SQL operating on the same table, what is the best way to write this select SQL query ? If it is PL/SQL, when i get the result from the first IF statement I will skip the remaining execution, and so on..
View 9 Replies
View Related
Oct 5, 2011
I have following tables with data as under:
table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9
I need to generate output as :
column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null
Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?
View 5 Replies
View Related
Mar 15, 2011
I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.
Name id dept_no
Ram 101 10
20
30
Ani 201 10
20
View 1 Replies
View Related
Mar 8, 2010
how can i track the exception for three select statement in one pl-sql block. here is synario.......
begin
select * from emp where empno=1234; --statement 1
select * from cusotmers where cust_id=125; --statement 2
select * from products where product_id='a-3'; --statement 3
end;
i want to track exception any one for ex no_data_found for all these three different statement.
I know if i put this three statement in three different pl-sql sublock then i can trap it....
how can i trap it in one single block?
View 4 Replies
View Related
Jul 30, 2012
Earlier i used select list item type with single select values so, According to my requirement in where clause i have written the condition like this
NVL(publish_id, :P91_PUBLISH_ID) = NVL(:P91_PUBLISH_ID,publish_id)
but my requirement is multi selection. How could i write same condition for multi selection.
write the same condtion for multi selection.
View 8 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
Nov 21, 2012
I have an script.sql that receives as a parameter an string.
example:
@C:/myscript.sql "o'connor"
user_account_value varchar2(120) := '&1';
EXECUTE IMMEDIATE "Select * from Table where column = :1 " USING user_account_value I am not sure how to deal with string that contains single quotes.
If the parameter were passed as : "o''connor" this will work
If the parameter is pass as: "o'connor" this will not work.
so my question is what options do I have to deal with dynamic queries and single quotes.
I tried replacing replace(myParameter,'''',''''''); but not working well.
View 11 Replies
View Related
Mar 3, 2011
I have a table with around 80 columns. All i need is to select first 40 columns.
Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.
View 2 Replies
View Related
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
Oct 5, 2012
I M USING APEX 4.1 AND CREATED SELECT LIST ON PAGE, I WANT TO SHOW MIN VALUE OF THE SELECT LIST FOR THAT I WROTE IN THAT SELECT LIST PROPERTIES UNDER DEFAULT TAG MIN; AND CHOOSE PL/SQL EXPRESSION BUT ITS GIVING ERROR "Error computing item default value for page item P1_PRODUCT."
BUT IF I HARDCORE THE VALUE CONTAINING IN MY DATA LIKE PRODUCT ID = 1, I HARDCODED IN DEFAULT VALUE 1 AND SELECT PL/SQL EXPRESSION IT WORKS.
BUT ITS NOT DONE LIKE THIS I WANT TO SELECT BY DEFAULT MIN VALUE OF THE SELECT LIST, SO THAT THE DATA SHOULD BE DISPLAYED ACCORDING TO THAT.
THE EXACT REQUIREMENT IS TO ENTER THE SELECT LIST DEFAULT VALUE IN SESSION SO THAT DATA IS TO BE DISPLAYED.
View 7 Replies
View Related
Dec 29, 2010
Why Blind select is better than Conditional select Statement?
View 10 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
Jun 18, 2012
I have created scripts as follow
CREATE TABLE STUDENTS(
STD_ID Number,
STD_NAME Varchar2(25 ) CONSTRAINT SYS_C002716 NOT NULL,
PHONE_NUMBER Char(20 ),
FATHER_ADDRESS Varchar2(100 ),
[Code]...
-- Add keys for table STUDENTS
ALTER TABLE STUDENTS ADD CONSTRAINT SYS_C002717 PRIMARY KEY (STD_ID)
/
-- Table CLASSES
CREATE TABLE CLASSES(
CLASS_NAME Char(40 ),
CLSS_NUM Number(38,0) CONSTRAINT SYS_C009724 NOT NULL,
LEV_ID Number(38,0)
)
[Code]....
------------------------- ----------
abk amr
saad amr
abk OSM
saad OSM
I expected the query gives me student_name in what class name and didn't expect to get frequency in output
View 2 Replies
View Related
Jan 2, 2013
I am getting an error while printing a sql query which is in a string inside a stored proc. I used
dbms_output.enable(1000000);
dbms_output.put_line(v_query);
and error "I get ORU-10028: line length overflow, limit of 255 bytes per line"
How do I print everything?
View 3 Replies
View Related
Aug 27, 2012
I'm trying to test moving a single instance 11202 database to single instance w/ grid infra.
Here is what I've done:
1. Install a database (11202), single instance and create a database
2. Install Grid Software only (user: grid)
3. start the cluster
4. "srvctl add database -d orclsidb -o $ORACLE_HOME" to register the database with grid.
4.1> I was able to start/stop the database with srvctl command here onwards
5. configure disks using asmlib and start the asm
6. "srvctl add asm" to register asm with grid.
7. PROBLEM ... when I try to "backup as copy database format '+ASM_DATA_DG';" from oracle user it errors out as below:
RMAN> backup as copy database format '+ASM_DATA_DG';
Starting backup at 24-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/fs0/oracle/oradata/orclsidb/sjc883p_indx_large_01.dbf
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/24/2012 08:53:29
ORA-19504: failed to create file "+ASM_DATA_DG"
ORA-12547: TNS:lost contact
ORA-15001: diskgroup "ASM_DATA_DG" does not exist or is not mounted
ORA-15055: unable to connect to ASM instance
ORA-12547: TNS:lost contactConsidering "TNS: lost contact" I tried to see if grid listener is aware of ASM instance:
[Code]....
All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=usracdb03.rwcats.com)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 22-AUG-2012 06:08:53
Uptime 2 days 2 hr. 50 min. 48 sec
Trace Level off
[Code]....
View 10 Replies
View Related
Mar 15, 2007
I have the following fields:
Addressln1
Addressln2
Suburb
Town
I know how to concatenate them
trim(Addressln1) || ',' || trim(Addressln2) || ',' || trim(Suburb) || ',' || trim(Town) as Address
1. I would like to know if any of the fields are empty I would like to eliminate the comma character from the string.
2. Can I replace the comma with a new line character and what character to be used in the syntax.
View 2 Replies
View Related
