SQL & PL/SQL :: Query To Return Value / String If No Records Found
Jun 17, 2012
Having following table:
UserID REC_TYP REC_CD
12345 'OFFR' 12
23456 'MSG' 13
I'd like to construct the query which in this particular case would return the REC_CD as 'Record_ID' for REC_TYP='OFFR' where USERID=? (always fetched by the application) and if such USER_ID doesn't exists (for the particular REC_TYP of course) to return string or any other value. e.g. The result for this query in case of user_id 23456 = would be "doesn't exist" or sth for instance 'FALSE' and for 123456 it would be '12'
I've stucked with a query. I have a table that i store the IDs of logically equal records.
For example; A = B B = C X = Y Z = Y
My query must return all equivalent records. If you call the query with parameter 'A', the result set must contain B and C. And if you call the query with parameter 'Y', the result set will contain X AND Z. I have thought that i can write the query wity using start with connect by statement. But the query does not work as i expected. Here is my code and sample data:
create table temptable (ID1 number,ID2 number);/
insert into temptable values(11,12);/ insert into temptable values(12,13);/ insert into temptable values(13,14);/ insert into temptable values(13,15);/
SELECT distinct ID1 from ( SELECT * FROM temptable START WITH ID1 = 13 OR ID2 = 13 CONNECT BY NOCYCLE ( (PRIOR ID1 = ID1) OR (PRIOR ID1 = ID2) OR (PRIOR ID2 = ID1) OR (PRIOR ID2 = ID2)) ) WHERE ID1 <> 13 union [code]....
When i call the query with parameter 13, i'm expecting to get 11,12,14,15. But it returns only 12,14 and 15.
I have created domain indexes on text columns of a materialised view to use "contains" clause when searching for data. The select query with "contains" clause does not return any records, however I was able to retrive data using via regular query using a like search.
-> will exec ctx_ddl.sync_index('index_name')'resolve my problem? -> since the view is a materialized view, how can i make sure that the latest data added are also picked up?
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.
I am trying to return a value of an oracle stored procedure using Excel VBA.
OS: Win XP SP3 Excel 2003 Ora Client: 11g
By trying different things I have noticed, that I could have troubles with the ODBC-connection. Maybe I am not using the right one. To store data returned from select statements I have an ODBC-Connection (Driver: Oracle in XE), which works perfectly, e.g.:
'----------------------- Sub Extract_Data() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim db_name, UserName, Password As String cn.Open db_name, USerName, Password
[code]....
When debugging the connection string, I find the Provider=MSDASQL5.1.
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.
I have been using With for many queries for readability. This most recent query seemed to be taking a bit longer to run and I didn't think much of it until a colleague showed me their version (totally different sql statement) which ran faster, but seemed more complicated and had more lines of code. I noticed that the other version did not use with. I moved the sql of the with into the join and the query ran faster dropping from 30 seconds to 798 msecs. The question is am I sacrificing speed for readability by using With, or it really depends on the overall sql statement.
Here is the query using WITH:
with prev as ( select person_uid, id, name, academic_period, case when enrolled_ind = 'Y' and registered_ind = 'N' and student_status = 'AS' then 0 else 1 end as enreg_stat,
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
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.
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
select order_number, (select decode(hcp.contact_point_purpose,'ABC',hcp.email_address,'CDE',hcp.email_address,null) from hz_contact_points, hz_parties hz WHERE hz.party_id=hcp.owner_table_id) Email FROM oe_order_headers_all h WHERE h.order_number='102' .................... ..............
Actually the problem i am facing is the inner select query is returning multiple row , so my main query is erroring out, i need to capture the multiple row.
In the above example the inner decode statement returning two mail address, I need to capture that, but while executing the whole query it is erroring out as saying single query returns multiple values. capture multiple values
I am Having below query which is having total 664 records and for WHERE Clause (accountno ='13987135') it is having 3 records but when i am taking count it is returning 3 at first time and again returning 4 every time from then onwords.
Here is my req - There are 2 columns C1, C2 - both of which capture a different set of values in 2 seperate custom oracle apps forms.
I have a RDF query to get the result set of C1,C2.
When this query does not return any values I need to look in form 2 and handle each value of C1 separately. I do not know how to achieve this with formula column - since I can perform checks/validations on the data returned by the query using a formula column.
I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:
The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.
I have a separate query which returns a list of support resources.
select res.full_name, res.unique_name , dep.description from niku.srm_resources res, niku.pac_mnt_resources pac, niku.departments dep where res.unique_name = pac.resource_code and pac.departcode = dep.departcode and res.is_active = 1 and description like 'IMS%' and UPPER(dep.description) like '%SUP%'
What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.
(the res.unique_name field in the second query can be linked to the same in the first query)
Logically, the process would be: 1. Identify Support Project 2. Identify Resources allocated to the project team 3. Compare with List of Support Resources 4. If any Resources in that list do NOT appear on the project, then return project.
I am new to SQL and I am just wondering if there is a solution to a problem I am having.I am using the piece of code below.Essentially what I am doing is selecting a field from a table and ordering that field in descending order. Using the Row_Number feature I then specify which Row I want to return.
Every day the row I will want is the Count of field1 for that day divided by 100 minus 1. This returns a single value of field1 and a single value of R.
I perform this operation every day. The only fields I change every day are the dates and the value of R. I use a seperate piece of SQL code to calculate R each day.
My problem is I have to often populate historical tables with this data. I can only run the code once for each day and for each value of R. Is there anyway I can alter this code such that it can return multiple values of field1 over several dates?The only way I can think of is to repeat the code multiple times using UNION but I am hoping there is a more efficient way.
SELECT * FROM (SELECT Field1, ROW_NUMBER() OVER (ORDER BY field1 desc ) R FROM table WHERE date >= TO_DATE ('20110215', 'YYYYMMDD') AND date < TO_DATE ('20110216', 'YYYYMMDD') ) WHERE R = 1227 --Note: 1227 = (count(field1)/100)-1
I have this query that returns results that contain duplicates(somewhat). I only want either the FIRST or LAST (either one is fine). Here is the query:
select unique PLLA.attribute4, PLA.item_description from po_lines_all PLA, po_line_locations_all PLLA where PLLA.po_line_id = PLA.po_line_id and PLLA.attribute4 is not null
So my output is something like this:
RCE12 This is an item for AUL1 RCE13 This is an item for PWEILL RCE14 This is an item for AUL1
I just want either the RCE12 or RCE14 record and not both since they both have the same description.
I have a table with multiple rows for the KEY attribute(its not a primary key) and a Rank for each row.
I want a query which fetches one row per KEY attribute.The row with lesser Rank should be considered. But in-case if the value is null for any column the value for next Rank should be considered.
WITH TMP_TBL AS ( SELECT * FROM ( SELECT 'A' DUN,'1' RNK,'A21' col1,NULL col2,'A41' col3,NULL col4 FROM dual UNION ALL SELECT 'A','2','A122','A23',NULL,NULL FROM dual UNION ALL SELECT 'A','3','A32','A33',NULL,'A35' FROM dual [code].......
DUN is the KEY attribute . RNK is the Rank for each Row. COL1... COL4 are data attributes
I want this to be done with SQL only. So I tried various ways but none were successful.Finally I created a Multi Row function row_nvl and it worked.
SELECT DUN, row_nvl(rownvl_param_type(RNK,col1)), row_nvl(rownvl_param_type(RNK,col2)), row_nvl(rownvl_param_type(RNK,col3)), row_nvl(rownvl_param_type(RNK,col4)) FROM TMP_TBL GROUP BY DUN
But I don't think my manager will allow me to deploy a Multi Row function .
I have a need to query a real time production database to return a set of results that spans a three day period. When the three days are consecutive it's easy but sometimes there is a 1 or two day gap between the days. For example I'm querying results from a group of people that work between Tuesday and Saturday. On a Wednesday I need t produce a set of results that spans Tuesday of the current week, and Saturday and Friday of the previous week; on Thursday I need to produce a set of results that that spans Wednesday and Tuesday of the current week and Saturday of the previous week.I'm using SQL Developer to execute the code.
I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function
Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)
CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB; CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB ( P_LIST CLOB, P_DEL VARCHAR2 := ',' ) RETURN SPLIT_TBL_CLOB PIPELINED
[code]....
But here I am facing 2 problems.
1. The function is not accepting a large string & I am getting the error
I have an Image Type on a forum page. I want a default "not-found" image to display if the BLOB column value is null or if there is no data for that search value. The image is stored with the app: #APP_IMAGES#not-found.png
The below query takes more than 30 minutes to return data.All the objects used are views. There is no direct reference to any table.The views with _mnth_ have data for 7 distinct months. The base table for all the views have a composite PK on the columns AR_ID (or ACCT_AR_ID),MSRMNT_PRD_ID
I need the order by, as the query is part of informatica code, and the order by works in the further processing.
SELECT ac.ar_id AS acct_ar_id, m.msrmnt_prd_dt AS msrmnt_prd_dt --removed the rest of column list to reduce size of code. FROM edxf.ar_rsrv_mnth_v ac, edxf.crdt_acct_mnth_v c, edxf.crdt_acct_v ca, (SELECT msrmnt_prd_id, msrmnt_prd_dt FROM edxf.msrmnt_prd_v WHERE msrmnt_prd_id = [code]....
Also, the count of data in the views is as below.
ViewTotal countCount for 1 msrmnt_prd_id --------------------------------------------------------- ar_rsrv_mnth_v1841892281945 crdt_acct_mnth_v664941457087369 crdt_acct_v12258728NA
