PL/SQL :: Not Comparing String In Case

Mar 21, 2013

i executed the below code and found that output should be 5 but it shows 7(always else part). it is not comparing string in case.

declare
check_in_date date;
var_day varchar2(11);
rate_id number;
[code]....

View 3 Replies


ADVERTISEMENT

Way To Display Proper Case Of A String

Jan 31, 2012

The Ename SMITH should appear as Smith.

View 1 Replies View Related

PL/SQL :: ORA-06592 / CASE Not Found While Executing CASE Statement

Oct 16, 2013

My DB version is

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,

[code]....

View 23 Replies View Related

SQL & PL/SQL :: Comparing One Row With Other Within Same Table

May 2, 2012

I have a requirement to get the count of those records whose department is not changed since they have joined the organization.
Script:

CREATE TABLE ddumps(orgid NUMBER, orgdate DATE, orgdept VARCHAR2(10))
/
INSERT INTO ddumps VALUES(1,'01-JAN-1999','ORG1')
/
INSERT INTO ddumps VALUES(1,'01-JAN-2000','ORG2')
/
INSERT INTO ddumps VALUES(1,'01-JAN-2001','ORG2')
/
INSERT INTO ddumps VALUES(2,'01-JAN-1999','ORG1')
/
INSERT INTO ddumps VALUES(2,'01-JAN-2000','ORG1')
/
INSERT INTO ddumps VALUES(2,'01-JAN-2001','ORG1')

ORGID ORGDATE ORGDEPT
1 1/1/1999 ORG1
1 1/1/2000 ORG2
1 1/1/2001 ORG2
2 1/1/1999 ORG1
2 1/1/2000 ORG1
2 1/1/2001 ORG1

since the orgid 1 has changed the dept from org1 to org2 I do not want this to be appeared in the final count. Results should only include the orgid 2 since it didn't changed any dept.

View 11 Replies View Related

Trimming Strings - Comparing?

Jul 26, 2007

I have 2 strings that I want to compare for example

string1 = 'ABC~AB/10/1234'
string2 = 'ABC~AB/10/1234~.....'

There could be anything after the 2nd ~ in string 2 is there a easy way of trimming string2 to the first 14 Characters? Or do I have to find the 2nd instance of ~ and then remove everything after (and including) that?

View 2 Replies View Related

SQL & PL/SQL :: Comparing Data In Two Tables Row By Row

Sep 23, 2011

I have two databases.

one is "ora" it is a 8i version
2nd is "orcl" it is a 11g version

"Oracle" is the my local database. i wrote following program for comparing the row by row data in both the tables.
Q)Is it BEST practice? If not let me know the best practice to compare data in tables?
Q) If am not using the order by clause its giving me wrong output even though both the data tables has same data. WHY?

DECLARE
v_emptest1 EMP_TEST1%ROWTYPE;
v_emptest2 EMP_TEST1@ora%ROWTYPE;
v_suc_flg NUMBER := 0;
v_cnt1 NUMBER:=0;
[code].......

View 9 Replies View Related

Comparing Timestamps In ORACLE

Mar 5, 2008

How to select the transactions out of the database that occurred within 70 seconds of each other. The toll_date field is a TIMESTAMP field.

Problem is, I seem to only get transactions that occurred within 70 minutes of each other. On the timestamp field I break the math down into the seconds in a day and I add 70. I then subtract that value and add that value to the timestamp and I should get anything between those values right?

SELECT Acct_ID, Ln, Tag_Rd, COUNT(*)
FROM (
SELECT T1.Account_ID Acct_ID, T1.Tag_Read Tag_Rd, T1.Revenue_Date Rev_Date, T1.Toll_Date, T1.Lane_ID, T1.Plaza, T1.Lane Ln, T2.Toll_Date, T2.Plaza, T2.Lane
FROM Toll T1
JOIN Toll T2
[code]......

View 6 Replies View Related

SQL & PL/SQL :: Comparing Two Decimal Values

Nov 5, 2011

Recently i have started working on PLSQL coding. I have a requirement. Either error or un-processed record count is 90% of to be processed records then the script has to fail. Currently I am having a situation where error count is 1 and total to be processed is also 1.

in the below
V_ERR is error count
V_UPS is un processed count
V_PROCESSED_COUNT is total to be processed.

I am expecting PASS result but it is giving FAIL.

DECLARE
V_ERR NUMBER:=0;
V_UPS NUMBER:=0;
V_PROCESSED_COUNT NUMBER:=0;
NIN NUMBER;
BEGIN
V_PROCESSED_COUNT:=1;
[Code] .......

View 1 Replies View Related

SQL & PL/SQL :: Comparing Two Tables Data

Mar 16, 2010

I want to do a comparision for the missing rows between two diffrent tables

TBL1 and TBL2 both with the same structure but with diffrent data some data is identical. though my data is huge i wanted to make sure the technique i am using

View 8 Replies View Related

SQL & PL/SQL :: Comparing Tables In Two Different Databases

Apr 13, 2011

As part of our project, we need to perform table comparisons in two different databases. I am currently looking for various options to accomplish this.

One of them is doing minus operation between these two tables. Also, i have looked at the data compare option in toad utility.

View 1 Replies View Related

Forms :: Comparing Date In 6i

Aug 3, 2011

I am working in form 6i, database 9i. I have datablock on table t1.

table t1: name(varchar2), date(varchar2)

datablock: name(varchar2), date(varchar2)[i have insert date with time stamp]

for date column, i am inserting date with time stamp.While querying data, user just enters only date(no time stamp), i should be able to query data. I tried in data block where condition

WHERE: substr(date,1,11)=nvl(:block.date,substr(date,1,11)

i am not able to retrieve data through date, through name it's working fine.how to retrieve data by only entering date (without time stamp)

View 13 Replies View Related

PL/SQL :: Comparing Two Counts Using Subquery?

Feb 10, 2013

Managed to confuse myself significantly. I essentially want to write a query to determine when two counts are the same using a subquery.

Eg:

R(x,y)
Select count(x)
from R
group by x;

Then I want to run another query to determine which x's have the same count value and output these corresponding x's.

View 6 Replies View Related

SQL Performance When Comparing To NULL In Where Clause

Oct 29, 2008

I have a SQL query which joins several large tables (so indexes matter here) from Oracle database. In the where condition I use IS NULL with one of the date field values. Query takes 40 sec to run and if I comment this one line...it takes 1 sec to run. This date field is an index on the table and I learn that --

1. IS NOT NULL in where clause uses an index
2. IS NULL in where clause does not use an index

Is there any work around to make the query faster...other than changing all the NULL date values in the table to some string. In other words can I force it to use the index.

View 16 Replies View Related

SQL & PL/SQL :: Comparing Nested Tables For Equality?

Jul 1, 2013

SF at oracle.com/technetwork/issue-archive/o53plsql-083350.html states that you can compare two database tables (of the same structure) by defining a nested table type (using %ROWTYPE) and two NT variables of that type, and loading the contents of each table into its respective NT variable, before comparing them using the = operator. Having read the Oracle documentation which states that you can only compare NTs for equality if they don't contain record types, I was surprised to read this, but figured I would try it because I must be misunderstanding SF, but it didn't work.

SCOTT@ORCL> create table empcopy3 as select * from emp;

Table created.

declare
type emp_ntt is table of emp%rowtype;
emp_nt1 emp_ntt;

[Code]....

But SF goes on to say he timed the execution of his NT equality method, comparing it with a SQL-only equivalent, and so I must be missing something. My understanding is that using %ROWTYPE declares a record type.

View 3 Replies View Related

SQL & PL/SQL :: Comparing Strings Entered In A Form?

May 27, 2011

I have to compare a string entered in a form with a series of English strings in back-end PL/SQL( using LIKE operator )

But it so happens, the string entered in the form is specific to the language used in the country. for ex. in Dutch, it is entered in Dutch language.

So on comparing, it fails as the PL/SQL compares it with English Strings.

View 4 Replies View Related

SQL & PL/SQL :: How To Fetch Column By Comparing Date

Oct 16, 2012

i am fetching one record based on date but not able to fetch the data.

in my table pdate column is there with date datatype and value is in that is:15-OCT-12 so i am fetching empid based on dates.

select empid from masterprocessdailydata where pdate=to_date('15/OCT/12','dd/MM/yy'); but not getting the empid.

View 11 Replies View Related

SQL & PL/SQL :: Comparing 2 Char Columns Using Trim?

Aug 18, 2011

I am trying to compare 2 char columns using trim to avoid space padding

CREATE TABLE TRIAL_A
(ABC CHAR(6));
INSERT ALL

[Code]....

View 11 Replies View Related

Forms :: Getting Values Of Some Column And Comparing It

Aug 6, 2010

I have got a table having column a, b , c, d, e and some values are stored in it. e.g. a, c and e have 'Y' and b and d have 'N' value in it.

How can i count with one query , how many columns have 'Y' and how many have 'N'.

cursor is one solution.

View 2 Replies View Related

SQL & PL/SQL :: Comparing Variables In Trigger Block?

Jun 29, 2010

I am having some issues in creating the following trigger. It gets created but with compilation errors.

create or replace trigger min_max_update_trg
after insert on tidlrrep
referencing new as n
for each row
declare
bltMaxSize number;
repMaxSize number;

[code].....

when i tried without the comparison if repMaxSize < bltMaxSize then it got created successfully.

View 12 Replies View Related

SQL & PL/SQL :: Utl_match Comparing Million Records?

Nov 22, 2010

the problem is: 2 tables - one with 2 million records, and the other with 8000 records.

i need to compare for each record in a table if there's a similar string on the other table.

i've created a procedure that does the following:

opens the first cursor (select col1,col2,col3,col4... from table 1)
loop
opens second cursor (select col1 from table 2)
loop
if utl_match(col1, table2.col1) > 80 then
insert col1,col2,col3,col4... into tableX
end if
close second cursor
close first cursor

the thing is that this procedure takes forever to end...about 8 days.

is it because im using the utl_match function? is there a way to speed this up?

View 13 Replies View Related

SQL & PL/SQL :: Grouping To Be Done By Comparing 2 Rows In A Table?

Feb 15, 2011

I'm using oracle 10g.I have a table with 4 columns

main_group-----id--------start_date------------end_date
M1-------------1---------07FEB11---------------10FEB11
M1-------------2---------09FEB11---------------11FEB11
M1-------------3---------10FEB11---------------12FEB11
M1-------------4---------13FEB11---------------16FEB11
M2-------------5---------18FEB11---------------21FEB11
M2-------------6---------19FEB11---------------24FEB11
M2-------------7---------26FEB11---------------27FEB11

i need to group the id's which are having overlapping dates and the output should be

main_group-----id--------start_date------------end_date
M1-------------1---------07FEB11---------------10FEB11------G1
M1-------------2---------09FEB11---------------11FEB11------G1
M1-------------3---------10FEB11---------------12FEB11------G1
M1-------------4---------13FEB11---------------16FEB11------G2
M2-------------5---------18FEB11---------------21FEB11------G3
M2-------------6---------19FEB11---------------24FEB11------G3
M2-------------7---------26FEB11---------------27FEB11------G4

I can give you the logic first i'll sort the start_date(already sorted in given example), then i'll compare the 2'nd id start date with 1'st id end date if it is less than the 1'st id end date, which means overlapping is there, then i'll group those 2 id's in to same group if not group them into 2 different groups.

View 7 Replies View Related

Comparing Index And PK Of A Table Between Instances?

Dec 2, 2012

I am about to compare Indexes and PK's(Constraint) of 3 instances.

What DBA table/s should i use best? The comparison is done to a specific schema only.

PS:Which is better dba_ind_columns or dba_objects?

View 10 Replies View Related

SQL & PL/SQL :: Comparing Multiple Columns According To Rows From Same Table

Aug 25, 2011

I am new to oracle, I have request to build a query,

we have table that generates data from 7am to 20pm for eavery hour it generates 4 rows and has 43 session values as 43 columns.

Now i want to find for every hour which is the hights session value at what time. in one hour it runs four times like 7, 7:15, 7:30 and 7:45 and each row has date, time and 43 session columns in table...

View 12 Replies View Related

SQL & PL/SQL :: Comparing Date Ranges Against All Rows In Same Table

Oct 30, 2011

I'm looking to see if there's a solution to my problem that I can use within the context of my business application interface into an Oracle RDMS. I have access to write custom SQL statements and functions, but I am NOT able to create stored procedures using the interface I have.

The challenge I am having is comparing date ranges. I have a table containing two columns labelled START TS TIME and END TS TIME, both of type 'Date'. I have figured out how to query each row against a given Next Session Start and Next Session End and determine if each row overlaps that row.

I need a procedure that will be recursive: that is, set Next Session Start and Next Session End to START TS TIME and END TS TIME of the first row, compare all rows against it, then set Next Session Start and Next Session End to the next row, compare all rows, ... for all rows in the table. I want to know what the maximum number of matches is (i.e. the most time periods that overlap).

If I could use a stored procedure I could complete this query easily. Is there other techniques (i.e. functions) available to leverage in order compare each row of date ranges against ALL rows in the same table?

View 4 Replies View Related

Forms :: How To Update Current Row Comparing It With Existing Value

Jul 14, 2013

I have master-detail form I would like to update the emp1 table on the base of current value of dept table deptno and emp table ename .

update emp1
set ename = :emp.ename
where ename =
(select ename from emp e
where e.ename=:emp.ename
and e.deptno=:dept.deptno);

I try the above query but it did not able to update emp1 table.

View 12 Replies View Related

Server Administration :: Schema Comparing Tool

Apr 8, 2012

we have a two different databases at different locations and on different servers, like one in our company with SID='A' and remote database with SID='B', We have recently implemented new module in database 'A' by creating lots of tables, functions, indexes, sequences, synonyms etc and now we need to install this on 'B' but the problem is we have not documented which tables we created, first we need to create a DB link between these two and then we need a tool to compare what are all the tables that we need to create in database 'B' , is there a tool for doing all this.

View 7 Replies View Related

SQL & PL/SQL :: Comparing Schemas - Content (data) Identical

Jan 20, 2011

I have two schemas with 149 tables in each schema, what I need to do is to prove that the content(data) between the two schemas is identical. I know that all the table names between the two schemas are the same, just need to prove that there is no difference in data.

So the query needs to prove that Schema A content = Schema B content

I know I cant do a simple select from Schema A.tab1 minus select Schema B.tab1 but since there are 159 tables, I am not sure if this is an efficient way of doing it.

View 14 Replies View Related

PL/SQL :: Outer Join When Comparing Trimmed Columns

Jul 29, 2013

Oracle v11.1 Standard edition. I am trying to use TRIM function around the columns which are used for outer join and it's not working. However, if I use ANSI syntax, it works. Please check the SQL below.Is there any way, I can use old syntax and TRIM and get the outer join to work?I know, I can fix/trim the data in the tables, but for some reason (need to keep the data exactly same as we received it), that is not possible here.

SQL>SQL> desc tabaName Null?   
Type----------------------------------------- -------- ----------------------------COL1                                              
VARCHAR2(20)SQL> desc tabbName                                      Null?   
Type----------------------------------------- -------- ----------------------------COL1                                              
VARCHAR2(20)SQL> select a.col1, b.col1 from taba a, tabb b where a.col1 = b.col1 ;
no rows selectedSQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1) ;

COL1                 COL1
-------------------- --------------------
A                    AC                    CD                   

DSQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) (+)= trim(b.col1) ;
select a.col1, b.col1 from taba a, tabb b where trim(a.col1) (+)= trim(b.col1)                                                            

*ERROR at line 1:ORA-00920: invalid relational operator

SQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1) (+);
select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1)                                                                            

*ERROR at line 1:ORA-00933: SQL command not properly ended

SQL> select a.col1, b.col1 from taba a left outer join tabb b on trim(a.col1) = trim(b.col1) ;

COL1                 COL1
-------------------- --------------------
A                    AC                    CD                    DB

SQL> select a.col1, b.col1 from taba a right outer join tabb b on trim(a.col1) = trim(b.col1) ;

COL1                 COL1
-------------------- --------------------
A                    AC                    CD                    D                     E 

View 3 Replies View Related

Oracle SP For Comparing Column Values In Table Pairs

Aug 16, 2013

I have a SP for comparing 80 diff column values in 8 table pairs and it is taking a huge lot of time to process as I have to process around 10k records.

View 5 Replies View Related

Comparing External File Data With Sql Query Result

Mar 16, 2009

I need to compare data of .csv file with the SELECT query result or with any table data.

Is there any possibilities in ORACLE to fulfill this requirement?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved