SQL & PL/SQL :: How To Return In One Row Only
Aug 21, 2012
i need to have an SQL statement that will return only in one row.
in this SELECT statement,
select NVL(a.jj_crdr,'CR'),
nvl(a.CR_cnt,0) CR_cnt,
NVL(a.CR_amt,0) CR_amt,
NVL(b.jj_crdr,'DR'),
NVL(b.DR_cnt,0) DR_cnt,
NVL(b.DR_amt,0) DR_amt
[Code]...
it returned
Tot_cr tot_cr_amt tot_dr tot_dr_amt
1 100 0 0
because there is record for CR and there is no DR.
but when there's record in DR and no in CR, there's no record returned.
because the outer join is in the 2nd Select. i need to change the condition to: 'where a.jj_creator(+) = b.jj_creator' in order to return a record.
what i need is to be flexible so that it will return records even if one of the source doesn't have record.
View 10 Replies
ADVERTISEMENT
Oct 17, 2012
In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.
Is there a simple statement that can be used to fix this in the apex dictionary?
View 1 Replies
View Related
Oct 31, 2013
I have some XML being returned from a web service, and it returns almost 900 variables. Whilst I am familiar with how to return these in a single row, do I can return a row for each variable? My DBA is very uncomfortable with creating a table with almost 900 columns, for obvious reasons. However, we already have plenty of tables with tens of millions of rows, so he's fine with that. I'll try and expand on the requirement. Below is some XML from the data returned to us:
<APPLICANT app_no="1">
<APPLSUMMARY>
<MAIN W="ZZ" X="{ND}"/>
<COUNTS Z="3" AB="0" BB="3" CB="0" DB="3" EB="3" FB="3" GB="0"/>
</APPLSUMMARY>
</APPLICANT>
I would like to be able to return a new row for each variable, For example:
VARIABLE | VALUE
----------------
W | ZZ
X | {ND}
Z | 3
And so on.
View 2 Replies
View Related
Sep 1, 2010
This query returns 2 rows and the output is displayed as well. how I can return just the first row where the max end_date is 4/30/2011?
select
pt.customer_number,
pt.customer_name,
lease.lease_number,
lease.lease_name,
lease.property_name_disp,
lease.location_code_disp,
MAX(pt.end_date) end_date,
pt.attribute1 Disabled
[code]...
View 19 Replies
View Related
Apr 22, 2010
Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0930-Jun-09
abcman11-Jul-0931-Dec-09
abcman21-Jan-1031-Mar-10
abcman21-Apr-1031-Aug-10
I need to code a SQL statement (Not PL/SQL) to display following records:
Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0931-Dec-09
abcman21-Jan-1031-Aug-10
View 4 Replies
View Related
Sep 30, 2013
I am fetching records from many voluminous tables having multiple joins based on filter criteria filled from frond end application. As per the selected criteria, I want to have a pre-check if query would return more than 1000 rows then I have to show user a message saying that he should refine the search. Is there any performant way to query db and dynamically find record count and stops executions if it is going return more than the specified no of rows.
View 5 Replies
View Related
Feb 17, 2012
I have two Oracle instances, both are 10.2.0.3..Executing the following results in two different results:
select N'Test" "result" from dual;
On one the output is:
resu
----
Test
and on the other the output is
result
----------------
Test
This seems to suggest that one is returning "Test" as a char(16) and the other as a varchar2 or nvarchar2. In both cases, NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is AL16UTF16.
Not being an Oracle DBA, I am not sure where to look.
View 1 Replies
View Related
Dec 5, 2006
The function definition in PL/SQL has IN OUT parameter as well as return statement. Using both we can return the values. Basic definition of a function is function can return only one value at a time.
The question is, Can we return a number thru return statement and a char value thru INOUT parameter. Is it possible to return two different values using these?
View 1 Replies
View Related
Aug 8, 2011
i am storing image in label how to return for calling place.
View 1 Replies
View Related
Jul 22, 2010
SELECT
REF.REFERRAL_TO,
REF.LOCAL_PATIENT_NUM,
REF.REFERRAL_REFERENCE_NUM,
TO_CHAR (REF.REFERRAL_DTE, 'DD/MM/YYYY') REFDATE,
TO_CHAR (OPC.CONTACT_DATE, 'DD/MM/YYYY') CONTACTDATE,
TO_DATE (OPC.CONTACT_DATE, 'DD/MM/YYYY') - TO_DATE (REF.REFERRAL_DTE, 'DD/MM/YYYY')
FROM
PAS.REFERRAL_DETAILS REF
FULL JOIN
PAS.OUTPATIENT_CONTACT OPC
[Code]...
This calculates the difference between a set date and a contact date, I only want it to return the minimum of the results set for each patient, how can this be achieved? At the moment it pulls off several records for each patient as there are several contacts.
View 2 Replies
View Related
Aug 24, 2010
I want to execute a procedure and view its output. The procedurename is GET_clnts which has one of the parameters as clob where i am passing xmltype data to it.How can i execute the procedure to view the output. i can execute the SQL statement by itself, but not sure how to call a procedure from a plsql block..
DECLARE
clientid NUMBER;
OUT_RESULTSET clob;
BEGIN
GET_clnts ( clientid, OUT_RESULTSET);
DBMS_OUTPUT.Put_Line('');
END;
[code]...
View 2 Replies
View Related
Nov 28, 2012
work_order unitid frommi tomi frompm topm
2666054111 06-154 77.000 85.000 77.370 null
2666054111 06-154 77.000 85.000 null 85.370
2666054111 06-154 77.000 85.000 null null
I used select distinct(work_order) to come up with the three different possible scenarios the problem is that i need all this information on a single row
work_order unitid frommi tomi frompm topm
2666054111 06-154 77.000 85.000 77.370 85.370
this is a conversion for distance. when i get this to work properly, it will generate reports on thousands of work orders with their converted distance markers.
View 3 Replies
View Related
Apr 30, 2010
I need to return results for the following query only when there are two or more rows found by the following:
SELECT DISTINCT D.PUBLICATION,
D.ADI,
D.DISTRICT,
D.ACCOUNT,
D.ROUTE,
[code]......
Expected results would be:
1 145 CRF TUANR000 VM BILLETING 16-APR-10 5 5 23-APR-10 48623
1 145 CRF TUANR000 VM BILLETING 16-APR-10 5 4 23-APR-10 48629
View 19 Replies
View Related
Apr 28, 2011
I want to search a some values in oracle table and then return the corresponding column names.
View 1 Replies
View Related
Nov 14, 2011
Following is the scenario:
CREATE OR REPLACE TYPE OBJ IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
[Code]..
I WANT USED VALUE TEST_ID AND TEST_DESC THE EXISTING IN FUNCTION FN_MY_DATA WITH A VARIABLES :
DECLARE
X NUMBER(9);
Y VARCHAR(30);
BEGIN
X := -- VALUE TEST_ID EXISTING IN FN_MY_DATA;
Y := -- VALUE TEST_DESC EXISTING IN FN_MY_DATA;
END;
View 3 Replies
View Related
Aug 2, 2010
whether a function can return two values?
View 4 Replies
View Related
Feb 17, 2012
I have two Oracle instances, both are 10.2.0.3
Executing the following results in two different results:
select N'Test" "result" from dual;
On one the output is:
resu
----
Test
and on the other the output is
result
----------------
Test
This seems to suggest that one is returning "Test" as a char(16) and the other as a varchar2 or nvarchar2. In both cases, NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is AL16UTF16.
Not being an Oracle DBA, I am not sure where to look.
View 6 Replies
View Related
Aug 26, 2010
I want to return 100 values by using the procedure. In that we have to declare 100 OUT parameters. So it's a time consuming process.
Instead of declaring 100 OUT parameters. How can we return 100 values in a single shot.
View 1 Replies
View Related
Oct 5, 2012
is there a way to return the next 7 dates just using a query... for example, I need a query that returns:
select (I don't know that put here) from dual
Date
2012-10-05
2012-10-06
2012-10-07
2012-10-08
2012-10-09
2012-10-10
2012-10-11
If possible, I would like to know if there's a way to pass a date and based on it, the query returns the next 7 dates based on the passed date... for example:
select (I don't know that put here) from dual where date > '2012-10-15'
Date
2012-10-16
2012-10-17
2012-10-18
2012-10-19
2012-10-20
2012-10-21
2012-10-22
View 13 Replies
View Related
Aug 30, 2012
I have a database in 10g in entreprise edition but i don't understand why the dba_feature_usage_statistics return no rows ? On some database when i look at dba_ feature_usage_statistics Protection Mode - Maximum Performance is used and active why?
View 4 Replies
View Related
Sep 4, 2012
Is there any way to return a result set (using a Database Link) from one Database to another Database? Database A needs to call Database B and Database B needs to return a result set via a stored package/function. The stored procedure/function on Database B also accepts 1 input parameter. Something like "select test.test('some input parameter')@database_b from dual'.
View 39 Replies
View Related
Nov 6, 2012
I would like to know, if there is a solution under 10g for something like this
select case when "C" = 1
then
(select 1,2,3,4,5 from dual)
else
(select 6,7,8,9,0 from dual)
end
from dual,
(select 1 "C" from dual)
What I really need is the full result of (select 1,2,3,4,5 from dual) or (select 6,7,8,9,0 from dual) if a condition as a result of a different sql-statement is true or false. It doesn't matter if the solution uses "case when" or anything else.
View 6 Replies
View Related
Oct 31, 2013
Oracle 11g For a procedure, if I use the hard-code procedure
sp_test( p_rc out sys_refcursor, p_1... p_2 ... ) is begin open p_rc for select * from TEST_TABLE... But now, I want to use the dynamically constructed sql, such as if condition1 then v_sql := ... elsif condition2 then v_sql := ... execute immedite v_sql
How can I return the the cursor p_rc in this case?
View 2 Replies
View Related
Dec 19, 2012
the following procedure successfully compiled and executed. How to access the values returning from this procedure in another procedure or PLSQL block.
CREATE OR REPLACE PROCEDURE test IS
cursor c1 is SELECT a.idnumber, a.idp FROM holdings a;
r1 c1%rowtype;
BEGIN
open c1;
loop
fetch c1 into r1;
exit when c1%notfound;
DBMS_OUTPUT.put_line (r1.idnumber||'--'||r1.idp);
end loop;
close c1;
END;
View 14 Replies
View Related
May 14, 2013
Allow me to preface this with the notice that I am not familiar with XML outside of its hierarchical structure, and am not familiar with what you can do with it using formatting.
As an example, let us say you have the following table:
Object_Type | Object_Name | Descriptor |
------------------------------------------------------------
Fruit | Apple | Crunchy |
Fruit | Orange | Sour |
Utensil | Pencil | Wooden |
Now let's say you want to query this table to return an XML format, which will be used in a web site to display the information, and you want to group the display by Object_Type, so that you want an XML format like this:
<Object Group>
<Object Type>Fruit</Object Type>
<Object>
<Object Name>Apple</Object Name>
<Descriptor>Crunchy</Descriptor>
[code]........
However, from what I can tell, using the XMLELEMENT function, it appears the closest I can get is following:
SELECT XMLELEMENT("Object Group",
XMLELEMENT("Object Type", object_type),
XMLELEMENT("Object",
XMLELEMENT("Object Name", object_name),
XMLELEMENT("Descriptor", descriptor)
)
)
FROM object_tbl;
<Object Group>
<Object Type>Fruit</Object Type>
<Object>
<Object Name>Apple</Object Name>
[code].........
Is it possible to group it in a way so that Apple and Orange end up in the the same <Object Group>? Or is that meaningless and such grouping can be done on the web site itself by formatting the XML?
View 5 Replies
View Related
Mar 27, 2013
In PL/SQL, the following sql causes an error when there is no record matching the search criteria:
-----
select pin_number from table1 where id = 1;
-----
error: No Data Available
Is there a way to get a return value of 0 whenever there is no matching record?
View 3 Replies
View Related
Aug 9, 2010
What is the difference between these return statuses for Report_object_status:
Canceled, Invalid_job, terminated_with_error
View 2 Replies
View Related
Sep 26, 2010
I was just wondering that do we have any function available in oracle which returns the last Friday of the month.
In our company we close our monthly books on last Friday of the month and there are few activities that we have to do on the following Monday as a part of month-end activity. Now the following Monday can fall on the same month or at times it falls on the following month.
I have to schedule a report to be sent to a user on the following Monday after the month-end. I need to schedule it using the cron job.
We are using Oracle 9i on Linux platform.
View 2 Replies
View Related
Oct 11, 2011
I wonder if there is any way to return the columns of an select with its letters lowercase?
I have a piece of code that creates an script wich returns an SQL result to be confronted with some templates. My template have the column names in lowercase and because It is case sensitive the Uppercase returned by Oracle,
View 5 Replies
View Related
Mar 29, 2010
I have a table like this basic example:
ID Product Color Time-In
1 Apple Green May
2 Apple Red April
3 Pear Green May
4 Pear Green April
5 Plum Blue June
In SQL I want to return all 4 fields of the records except those records where Product and Color are identical - in that case it should return the latest (by name of month - preferred) or just the first it finds
So I should get these
1 Apple Green May
2 Apple Red April
3 Pear Green May
5 Plum Blue June
If I do a select distinct then I will only get those fields I test on (product and color), not the rest.
View 1 Replies
View Related
