Re: decode

Imran Ashraf wrote: > > Hi, > > I have this query... > select decode(length(sn),8),substr(sn,-7),sn )from . > > However i want to change it so that if the length of sn is >= 8 then set sn > to last 7 characters. > > How can i implement this? > > Regards > -- decode(sign(length(sn) - 8

RE: decode

decode(sign(8 - length(sn)), -1, substr(sn,-7), sn ) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Imran Ashraf Sent: Wednesday, September 03, 2003 1:00 PM To: Multiple recipients of list ORACLE-L Hi, I have this query... select decode(length(sn),8),substr(sn,-7),sn )from

RE: decode

Use greatest ... decode(greatest(sn,7),sn, substr(sn,-7),sn ) Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] -Original Message- Imran

RE: DECODE or not to DECODE

Thanks To all for the support! I managed to use a combination of thesuggestions which worked great! Denham -Original Message- Sent: Monday, August 04, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Hello Listers, I need some help please, I am trying to create a DECODE statement,

RE: DECODE or not to DECODE

d [mailto:[EMAIL PROTECTED] > Sent: 04 August 2003 15:39 > To: Multiple recipients of list ORACLE-L > Subject: RE: DECODE or not to DECODE > > > One trick is to use the sign() function... > > decode(sign(30 - sysdate + col_date), 1, '30_days', NULL), > &g

RE: DECODE or not to DECODE

Eva - I dredged this out of some ancient code that I wrote against a 7.2 database. The inner trunc functions were there to get rid of the time - I don't remember why the outer trunc function was there at this point (it was five years ago). This doesn't get you completely there, but will hopeful

Re: DECODE or not to DECODE

One possibility that comes to mind is to write a really, really long DECODE: DECODE((SYSDATE - COL_DATE), 1, '30_days', 2, '30_days', 3, '30_days', ... NULL) Days30 But there must be a better possibility. Perhaps use the SIGN function: DECODE ( SIGN((SYSDATE - COL_DATE)-30), -1, '30_DAYS',

RE: DECODE or not to DECODE

One trick is to use the sign() function... decode(sign(30 - sysdate + col_date), 1, '30_days', NULL), Regards David Lord > -Original Message- > From: Denham Eva [mailto:[EMAIL PROTECTED] > Sent: 04 August 2003 15:24 > To: Multiple recipients of list ORACLE-L > Subject: DECODE or not to

Re: DECODE AND SELECT

Oracle 8i (8.1.6+) PL/SQL can use execute immediate for SQL with searched CASE expressions. Have Fun :) Nuno Souto wrote: - Original Message - I am in urgent need of backporting oracle9i pl/sql to oracle8i.I have encountered some case satements like this CASE when x>0 then (select y

Re: DECODE AND SELECT

- Original Message - > I am in urgent need of backporting oracle9i pl/sql to > oracle8i.I have encountered some case satements like > this > > CASE > when x>0 then (select y from deptno) > else > (select Z from emp) > end > > i need to convert them to decode statements.Can > anybody te

RE: DECODE AND SELECT

One thing you could consider is to make functions out of each of the sql statements in the case. Then, in your decode statement you could call the correct function based on the value of X. Either that or put the entire logic into a function that has all the case statement broken down into indivi

Re: DECODE AND SELECT

You are lucky! My sinuses are getting better. Decode is a function which is to be called within a SQL statement, not the other way round. What do you translate a "CASE" statement into? That's elementary, my dear Hrishy: a sequence of "if ... then ... else ... end if" commands. On 2003.07.12 00:39,

RE: RE: Decode

PROTECTED]> ] Sent: Thursday, September 12, 2002 12:38 PM To: Multiple recipients of list ORACLE-L here's a decode that should *NEVER* be put into production! :) RE: Decode Raj,   I know SQR.  Bought it when it was being peddled by Sql Solutions.  Great tool!  Somehow, it never

RE: Decode

MAIL PROTECTED] Quad/Tech International, Sussex, WI USA > > > > -Original Message- > > From: Jared Still [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, September 11, 2002 7:33 PM > > To: Multiple recipients of list ORACLE-L > > Subject: Re:

RE: Decode

Title: RE: Decode Raj,   I know SQR.  Bought it when it was being peddled by Sql Solutions.  Great tool!  Somehow, it never got marketed correctly.  I had heard that at one point, Oracle "almost" bought it, and was going to position it as one of its products.  But that boat

RE: Decode

Title: RE: Decode Wait till you see some of the SQL that we run ... it is dynamic sql used within SQR (a reporting tool). Compared to that, this is production quality. Raj __ Rajendra Jamadagni      MIS, ESPN Inc. Rajendra dot

RE: Decode

here's a decode that should *NEVER* be put into production! :) -Original Message- Sent: Thursday, September 12, 2002 12:18 PM To: Multiple recipients of list ORACLE-L Beware...nasty sql below select decode((select deptno from dept where upper(loc) = upper('&vloc')), <-- expression

RE: Decode

Beware...nasty sql below select decode((select deptno from dept where upper(loc) = upper('&vloc')), <-- expression (select deptno from dept where loc = 'NEW YORK'), <-- value1 (select count(*) from emp where deptno = 10), <-- return1 (select deptno from d

Re: Decode

Good example! ;=) Thanks, Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 11, 2002 3:38 PM > select decode(new_job_loc,'Vermont','TAKE IT','LEAVE IT') from dual; > > Waleed > > -Original Message- > Sent: Wed

RE: Decode

EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: Jared Still [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 11, 2002 7:33 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Decode > > > > No, I di

Re: Decode

No, I didn't know that. Interesting: select decode ( (select to_char(sysdate, 'DAY') from dual), 'MONDAY','Groan...', 'TUESDAY','Another day, another dollar', 'WEDNESDAY','Hump Day', 'THURSDAY','Let me out of here!', 'FRIDAY',( select chr(84)||chr(104)||chr(97)

RE: Decode

Title: RE: Decode an example straight from 9i SQL Reference should do SELECT product_id,    DECODE (warehouse_id, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey',   

RE: Decode

select decode(new_job_loc,'Vermont','TAKE IT','LEAVE IT') from dual; Waleed -Original Message- Sent: Wednesday, September 11, 2002 2:59 PM To: Multiple recipients of list ORACLE-L Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give

Re: Decode

decode (column_name, value, replacement, value, replacement, default) where value is the column value replacement is value you want to replace the column value with default is the value to use if you get a column value not in your list decode (operating_system, 'PC','Windows','SUN','UNIX','

RE: Decode

DECODE(expression, value1, return1, value2, return2, default_return) BTW, did you know that expression, value, return and default_return can all be scalar subqueries? Dan -Original Message- Sent: Wednesday, September 11, 2002 12:59 PM To: Multiple recipi

RE: Decode

DECODE(value,if1,then1,if2,then2,if3,then3,...else) Have a great day Ruth!! -Original Message- Sent: Wednesday, September 11, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone

RE: Decode

Ruth - Google! Google! Decode Oracle's handy decode function works as follows: decode(expr, search, expr[, search, expr...] [, default]) To evaluate this expression, Oracle compares expr to each search value one by one. If expr is equal to a search, Oracle returns the corresponding result. If n

RE: Decode

decode(test_value,if_A,then_B,if_C,then_D,...,otherwise_E) decode(city,'San Francisco','CA','Dallas','TX','Unknown') If the city is 'San Francisco', return 'CA'. If the city is 'Dallas', return 'TX' If neither, return 'Unknown' -Original Message- Sent: Wednesday, September 11, 2002 1:

RE: Decode

Here you go, Ruth, select decode(field,'data value1','output1','data value2','output2',...'output if none others')  MEANINGFUL_ALIAS from   yadayada Rodd On Wed, 2002-09-11 at 14:28, Carle, William T (Bill), ALCAS wrote: Ruth, You can pull manuals off the web at this URL: h

Re: Decode

Ruth, The DECODE is the equivelent of IF,THEN,ELSE. select Decode(column, value IF, value THEN, value ELSE) from tablename; Ron ROR >>> [EMAIL PROTECTED] 09/11/02 02:59PM >>> Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint

RE: Decode

DECODE(,); -Original Message- Sent: Wednesday, September 11, 2002 2:59 PM To: Multiple recipients of list ORACLE-L Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I n

RE: Decode

Ruth, You can pull manuals off the web at this URL: http://download-east.oracle.com/otndoc/oracle9i/901_doc/nav/docindex.htm This is for 9i. If you have 8i, they have that too. Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: W

Re: DECODE HELP SOLVED

Thanks Lyuda and Ron for your help! --- Lyuda Hoska <[EMAIL PROTECTED]> wrote: > Something like this would work. > SELECT GREATEST(FIRST_DATE, > NVL(SEC_DATE,THIRD_DATE),NVL(THIRD_DATE,SEC_DATE)) > FROM ; > Just an idea, you can explore it further.. > > -Original Message- > Sent: Monda

Re: DECODE HELP

Why use decode. Look up the "greatest" sql function. select prim_key, greatest(first_date,sec_date,third_date) from your_table. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] "The problem with some people is that when they aren't drunk, they're sober." --William Butler Yeats.

Re: Decode in PL/Sql

Gyula, I understand, that you want to apply DECODE function to some local variables in your pl/sql block (and not to some columns in some table). The trick is: you can always run DECODE() against 'dual' table: select DECODE(base_expr, compare1, value1, compare2, value2,... default ) into local_

RE: Decode in PL/Sql

Feuerstein recommends replacing with IF statements. Page 517 Oracle PL/SLQ Programming. Simon Fox Room 205 Rail House Gresty Road CREWE Cheshire CW2 6EA England 01270 533997 -Original Message- Sent: 17 May 2001 10:26 To: Multiple recipients of list ORACLE-L Hi Gurus ! Is there any