I am using oracle database 11g.My use case is I do have a table with following valuesTable name -test
product id productsortdescription
H58098 ACETAMIDOHYDROXYPHENYLTHIAZOLE
043994 Alloy .MM.INTHICK My query is
select * from test order by productsortdescription; this query gives result as is like
product id productsortdescription
H58098 ACETA
it seems that oracle data reader on some client machine is case sensitive. We are using oracle 10g. 2 client machines connect to the same DB and run the same code - one works and another doesn't. they are both using odac 2.111.7.20.
this is my .NET code (which works fine on every environment it was installed in the last 2 years):
using (OracleConnection sqlConn = new OracleConnection()) { sqlConn.ConnectionString = connectionString; using (OracleCommand sqlCmd = new OracleCommand()) { sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.CommandText = "getValues"; [code].......
this is the oracle procedure:
PROCEDURE getValues( rc IN OUT sys_refcursor ) IS BEGIN OPEN rc FOR SELECT Name, Value FROM MySchema.MyTable; END;
The problem that we saw was that on one of the client machines dr["Name"] threw exception :
Error Source: Oracle.DataAccess.Client.OracleDataReader.GetOrdinal Error Message: System.IndexOutOfRangeException: Unable to find specified column in result set at Oracle.DataAccess.Client.OracleDataReader.GetOrdinal(String name) at Oracle.DataAccess.Client.OracleDataReader.get_Item(String columnName)
We modified the code and used NAME instead of Name and it worked!! (dr["NAME"] instead of dr["Name"])
How can it be? from oracle documentation: A case-sensitive search is made to locate the specified column by its name. If this fails, then a case-insensitive search is made.
My question is what can cause such a behavior? Some ODAC/ODP definitions, .NET configuration?
How can i sort data in the execute Query mode during runtime,i have a columns which has number, char and date field.
This below data is fetched in execute query from a table; x1 x2 x3 1343 adfa 11/14/2012 1353 adfa 11/11/2012 1333 adfa 11/10/2012 1333 adfa 11/12/2012 1353 adfa 11/09/2012
I have a query that will either return one record or zero records. When it returns zero records I want to replace my attributes with a sentinel, like 'N/A'. I tried the CASE statement but couldn't get anything to work
Sample (does not work): select (case when exists (select product from tbl_product where productid = '123') then product else 'N/A' end) product from tbl_product where productid= '123'; If one record exists it should produce: 'My Widget' (or whatever) If zero records exist it should produce: 'N/A'
The query has a case statement in the where clause so that results can be filtered. If I pass "ut" for sso_id then the query returns 21 rows. If I remove the case statement and hard code "a.sso_id like lower('ut'||'%')" then the query returns 41 rows. The query should be returning 41 rows all the time.
Problem:
When passing "ut" as an SSOID parameter to the Procedure the query returns 21 rows.Taking the query and hard coding "a.sso_id like lower('ut'||'%')" the query returns 41 rows.
Result: query should be returning 41 rows when "ut" is passed an an SSOID parameter.
Returns 21 rows
procedure SSO (SSOID in varchar2 default null, Name in varchar2 default null, Campus in varchar2 default null, Department in varchar2 default null,
[code]...
Returns 41 rows
open Results for select a.sso_id, (a.name_last||', '||a.name_first) as name, b.site,
[code]...
Test Data CREATE TABLE ID ( SSO_ID VARCHAR2(60 BYTE), NAME_FIRST VARCHAR2(100 BYTE), NAME_LAST VARCHAR2(100 BYTE),
[code]...
Test Data CREATE TABLE NT ( LOWER_NT_ID VARCHAR2(60 BYTE), DEPARTMENT VARCHAR2(100 BYTE),
update t_emp set TTL_FLG = CASE WHEN EXISTS (SELECT 1 from Schema1.T_STG_LW_EMP E WHERE E.Employee = Schema2.T_emp.EMPLOYEE_NUMBER AND E.JB_CODE like '%TP%' or E.JB_CODE like '%DGD%' or E.JB_CODE like '%PDD%' or E.JB_CODE like '%YND%' ) THEN 'Y' ELSE 'N' END;
I am using oracle 11G database,I have to check length of name column value from employee table and if length(name) > 39 then value should be substr(name,0,39) else value should be name only. i tried below code
select CASE when length(name) > 39,substr(name,0,39) else name END
from employee but its not working ..can I do this using decode too ? ,,which one would be better or this is not a right way ?
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production version
I am having the data in following table -
drop table stud_fact; create table stud_fact(stud_NM, LVL_CD,ST_DT_DIM_KEY,OVRNK) as select 'ABG Sundal','H','20110630','175' from dual union all select
I want to count the batch records using BATCH_ID with CASE statement ,for that i am using below query but its not working ,
SELECT COUNT(*) FROM <TABLENAME> WHERE VNBATCH_ID=CASE WHEN #SDC <10 AND #PERIOD >=10 THEN 0||#SDC||#PERIOD||#BATCH_ID WHEN #SDC <10 AND #PERIOD <10 THEN 0||#SDC||0||#PERIOD||#BATCH_ID WHEN #SDC >=10 AND #PERIOD <10 THEN #SDC||0||#PERIOD||#BATCH_ID ELSE #SDC||#PERIOD||#BATCH_ID END
below query is returning two rows.The thing now happening is the query is returning the output for both the case statements.But what is need only when the first case staement is NULL then it should go for second case.
SELECT DISTINCT CASE WHEN esc.x1 = Substr(inp.y, 0, 3) AND esc.x2 = Substr(inp.y, 4, 2)THEN esc.cc WHEN esc.mcc = Substr(inp.y, 0, 3) AND esc.mnc = Substr(inp.y, 4, 3)THEN esc.cc [code]....
I tried using rownum=1 but it filters out valid records.correcting the above query so that if the first case is null then only it should go for second case.
I'm trying to find out if I can create a context sensitive menu in Oracle Forms 10g. So far I haven't found much in the way of info. where I can find some sample code or a demo I can learn from?
The idea being that when a user right clicks an item they are then presented with relevent options for that item.
Can I use CASE statement Inside a Oracle Report Query. I'm using Oracle Reports Builder 10g.
My Report Query looks like this,
select invh_code, invh_number, invh_date, invh_cm_code, im_description from invoice_head, invoice_det, unit_of_measurement, item_master where invd_invh_code = invh_code and im_code = invd_item_code AND (case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4)) BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO) else 1 end) order by invh_number
:p_flag is a parameter that i pass from oracle form and based on that value (:p_flag=1) i need to include this specific condition else omit that condition.
But the CASE statement is throwing Error
ORA-00907 :Missing Right Paranthesis (case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4)) ==> BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
I'm getting this error while executing a package.But this is unpredictable because sometimes it's coming and sometimes it's not. Everytime I'm passing the value as 'ALERT' for the transaction name. Sometimes it's successful and sometimes it's throwing ORA-06592
CASE UPPER(IC_TRANSACTION_NAME) WHEN 'ALERT' THEN SELECT A.FACILITY_ID INTO VN_FACILITY_ID FROM ALERT A WHERE A.ALERT_ID = IN_PARENT_NODE_ID; INSERT INTO TRANSACTION_HISTORY (TXN_HISTORY_ID,
I am running this query but am not getting data that is correct.
SELECT a.prod_id, a.prod_name, a.artist_name, COUNT(*) FROM po_my_purchase_tb a, cm_track_tb b WHERE a.prod_id = b.prod_id and b.GNR_CD = 'GR000017' AND a.purchase_date > '10-FEB-10' AND ROWNUM<50 GROUP BY a.prod_id, a.prod_name, a.artist_name, a.buy_seq ORDER BY COUNT(*) desc
Suppose I have a table in which I have first_name, last_name, dob. Now I have to fetch on the basis of first_name=some_value, last_name=some_value and dob=some_date. I want to sort it on the basis of exactly fetched values. Let me take an example-
test table contains-
first_name last_name dob ---------- --------- ---- Manu Batham 02-Feb-1988 Manu Sharma 01-Jul-1987 Avinash Pandey 03-Feb-1988 Ankit Gupta 02-Feb-1988 Manu Aggrawal 02-Feb-1988 Manu Batham 20-Jan-1985 Sikha Batham 17-Apr-1988
first_name last_name dob ---------- --------- ---- Manu Batham 02-Feb-1988 Manu Aggrawal 02-Feb-1988 Manu Batham 20-Jan-1985 Manu Sharma 01-Jul-1987 Ankit Gupta 02-Feb-1988 Sikha Batham 17-Apr-1988
My result is based on the approach- if matched first_name, last_name, dob --> 1st prefrence in order if matched first_name, dob --> 2nd prefrence in order if matched first_name, last_name --> 3rd prefrence in order if matched last_name, dob --> 4th prefrence in order if matched first_name --> 5th prefrence in order if matched last_name --> 6th prefrence in order if matched dob --> 7th prefrence in order
I designed the following query for the same-
Select first_name,last_name,dob,1 "Order" from test Where first_name='Manu' and last_name='Batham' and dob=to_date('02/02/1988','dd/mm/yyyy') union Select a,b,c,2 from test Where [code]......
I know that this is not the best possible solution as the table is very big and doing so many hits on that table will certainly decrease the performance.
I want a trigger i have made a software abut school system i need a trigger to sort out the positions...if total number like 100,99,98 than in positions column 1,2,3 but if total marks same like 100,100 in position column shows 1,2..i need if the marks are same than in position column also same like if marks 100,100 in position column shows 1,1
I know the only way to guarantee a specific sort order result when querying table is by using the order by clause. However, I have an issue where I do not have access to the code for the web user interface of a very lightly used interface (has two users). In that user interface is a drop down box that is populated by a table - one table. The drop down box is populated by the query "select [column name] from [table name]". Right now there are 400+ rows in that table total, so it's small but not having the items ordered is a pain.
I would like to alter something on the db side so the result of "select [column name] from [table name]" is an ordering by the column descending. I don't want the sort order to be the same for all queries (including joins and all) just want to control the order for that one query. It is Oracle9i.
I have a master detail form that presents a list of 22 items that can be checked or unchecked depending on whether the test is needed.
The issue I have is trying to order or sequence the list in a specific order without a database column.
Here is the code I am trying to use in a post query trigger:
CASE :SAMPLE_TESTS.TESTCODE when 'L001' then :SAMPLE_TESTS.SEQ := '02' || :SAMPLE_TESTS.TESTCODE; when 'L002' then :SAMPLE_TESTS.SEQ := '03' || :SAMPLE_TESTS.TESTCODE; when 'L003' then :SAMPLE_TESTS.SEQ := '04' || :SAMPLE_TESTS.TESTCODE;
[Code]..
The non-database field is character with a length of 6.
When I try to use this field in the order by property of the data block it is unable to perform query.
