Hi (getmeidea?),
I assume your requirement is something like this:
If say a value 'a' has gone in for a filed ,then a value 'A' should not go in .
Then one of the methods is to create a unique index on this field
[code=sql]
create unique index <index_name> on <table>(lower(< field>));
[/code]
You can use either lower or upper, it doesnt matter....
chandu031
Last Activity: May 28 '08, 04:01 PM
Joined: Mar 29 '07
Location:
-
Hi bharadwajrv,
IF NOT exists does EXIST in DB2 and it has the same syntax as what you have mentioned. Your problem might be that of missing semicolons.
Check it out and let me know.
Cheers!...Leave a comment:
-
Hi Vijaya,
Try out this :
[code=sql]
select count(1) from (select distinct * from <table>)
[/code]
Cheers!...Leave a comment:
-
Hi rsrinivasan,
I guess you are comparing this formatted date with some other date field. If yes then it makes sense to format even that field before doing the actual comparison. For example if say a date variable v_date holds sysdate and you want to compare this then you can do:
[code=sql]
SELECT 1 FROM DUAL WHERE TO_CHAR(V,'DD-MON-YYYY') = TO_CHAR(SYSDATE ,'DD-MON-YYYY')
[/code]
If your...Leave a comment:
-
Hi rehlen,
Even I had the same opinion that you cant have Alphanumeric sequences. But guess what? I googled a little and found this interesting link:
Alphanumeric sequences?
Here the writer talks about creating alpha numeric sequences using DECODE logic. I found this article very interesting and hope you will find it useful. I am starting to feel nothing is impossible in a database:)
C...Leave a comment:
-
Hi,
You can still use it as a date field. Try inserting to_char(sysdate ,'dd-mon-yyyy')
into a date field in a table and you will come to know.
Anyways the other method of changing the date format is to alter the default format in sqlplus:
[code=sql]
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
[/code]
This changed format will hold good for the current session...Leave a comment:
-
Hi,
This is the query:
[code=sql]
SELECT SUBSTRING('SRID HAR''S',CHARIND EX('`','SRIDHAR `S',1)+1,LEN('S RIDHAR''S'))
[/code]
However this will not work if you are looking for the second or higher instances of the search character(apost ophe in your case).
Cheers!...Leave a comment:
-
Hi,
The query is a pretty straight forward join on the two tables unless
you want the result to be a comma seperated?
Like this:
abc chinese,thai
xyz italian,indian
Cheers!!...Leave a comment:
-
Hi,
It's a pretty straight forward query:
[code=sql]
SELECT TO_CHAR(SYSDATE ,'DD-MON-YYYY') FROM DUAL
[/code]
Cheers!...Leave a comment:
-
Hi rocpraveen,
We are more than happy to help you with your query. But the only problem is we are not good at guessing other people's requirements with absolutely no information to go with. I might decide upon my own set of tables and columns and arrive at a suitable query but I guess it would not serve your purpose. So either you have not given enough information or we are just not good enough.
P.S: It's absolutely...Leave a comment:
-
Hi Sandhya,
Could you be more clear. According to my understanding a cursor variable is nothing but a cursor opened in one PLSQL routine and fetched by another routine. So are you suggesting a cursor result set cannot be stored in a PL/SQL table? I would appreciate it if you were to give an example to state your point more clearly.
Cheers!...Leave a comment:
-
Hi,
Have you collected the statistics on this table?...Leave a comment:
-
Hi ,
Did you collect statistics on the table? Try it out and see if lessens the time.
Cheers!!...Leave a comment:
-
Hi blackflicker,
The following code will give you the result:
[code=sql]
select avg(b.entered - a.entered) from (select id r1,entered from dummy) a ,
(select id r2 , entered from dummy )b where r2 = r1 + 1
[/code]
I think you will have to use a different logic than just r2 = r1 + 1 as there might be no continuity in the Identity values.
I am sure you will...Leave a comment:
-
Hi Reshmi,
You have to give the "administer database trigger" privilege for that.
[code=sql]
grant administer database trigger to <user>
[/code]
Cheers!!...Leave a comment:
-
Hi Richardson,
Here's a sample query:
[code=sql]
SELECT job_name , AVG(
TO_NUMBER(subst r((END_TIME-START_TIME),ins tr((END_TIME-START_TIME),' ')+10,3)/ 1000) +
TO_NUMBER(subst r((END_TIME-START_TIME),ins tr((END_TIME-START_TIME),' ')+7,2) ) +
TO_NUMBER(subst r((END_TIME-START_TIME),ins tr((END_TIME-START_TIME),' ')+4,2)) *60 +...Leave a comment:
-
Hi,
One way of implementing that feature is to write a custom function which will may be accept one parameter as a delimited string containing all the keywords and the
other parameter as the source string and you can use INSTR function in a loop.
Ofcourse this will be fairly simple and ineffecient way of doing it.
Coming to the LIKE operator , it will work fine but you will have to consider three different...Leave a comment:
-
Hi Frozenmist,
A slight correction to your query:
[code=sql]
SELECT col1,col2,col3 FROM t1 WHERE (col1,col3,col2 ) IN (SELECT min(col1),max(c ol3),col2 FROM t1 GROUP BY col2)
[/code]
You had missed out the Min....
Cheers!...Leave a comment:
-
Hi ,
Try this out:
[code=sql]
DECLARE
v_stats varchar2(1000);
BEGIN
for st_row in (select owner, table_name from all_tables where owner='XYZ and table_name in(
'XX,
YY,
'ZZ',
'ZZ1',
'ZZ2')
)
loop
v_stats := 'begin dbms_stats.gath er_table_stats( ownname=>'||''' '||st_row.owner ||''''||','||'t abname=>'||'''' ||...Leave a comment:
-
Hi ,
I am no expert on this, but based on the documentation I went through, I can say that since you are not using tnsnames.ora for the connection , there is no way of letting the Oracle client know that there is a different server to connect to,other than registering that instance in the tnsnames.ora. Either you will have to connect via tnsnames.ora or you will have to use AT clause in your code(in which case you will have to recompile...Leave a comment:
No activity results to display
Leave a comment: