subject:"RE\: Sql Question"

Jared

Jared Still wrote:

Though not a dramatic difference, the CONCAT was faster
and less resource intensive than the inline view with GROUP BY.

:)


Ok, let it be like that, but your test does not check some
other things, like common sense, logic, and session memory.
Performance can vary as I mentioned sometimes can be
neglected, however let's consider the tricks you made before
your test:

1. create index emp_idx on emp(ename, job, mydate); -- what for do
you need it? It was not in the original problem definition. It's
not used, however you created it, what's that for?

2. Both queries give different results, that's what I mentioned --
you just proved my words :) One must be very careful with that.
Even DISTINCT can lose sometimes like in your example, but it does
not mean that the logic of the application works correctly in
case of || = CONCAT is used. Think also about an artificial limit
your create -- each and every varchar has to be padded to it's
maximum length (become CHAR) -- that optional and case dependent,
however; all date and numeric columns have to be formatted
otherwise you can face the same case like your your example.

SQL SELECT COUNT(*) FROM emp;

   COUNT(*)
--
  64000

SQL select count(distinct(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
2000

SQL SELECT COUNT(*)
   2   FROM (
   3SELECT DISTINCT
   4 ename, job, mydate
   5FROM emp
   6   );

   COUNT(*)
--
   7000

--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
 INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question

2003-01-30 Thread Khedr, Waleed

What about:

select count(count(*))
from emp
group by ename, job



Have fun :)

Waleed

-Original Message-
Sent: Thursday, January 30, 2003 1:55 PM
To: Multiple recipients of list ORACLE-L


Jared

Jared Still wrote:
 Though not a dramatic difference, the CONCAT was faster
 and less resource intensive than the inline view with GROUP BY.
 
 :)

Ok, let it be like that, but your test does not check some
other things, like common sense, logic, and session memory.
Performance can vary as I mentioned sometimes can be
neglected, however let's consider the tricks you made before
your test:

1. create index emp_idx on emp(ename, job, mydate); -- what for do
you need it? It was not in the original problem definition. It's
not used, however you created it, what's that for?

2. Both queries give different results, that's what I mentioned --
you just proved my words :) One must be very careful with that.
Even DISTINCT can lose sometimes like in your example, but it does
not mean that the logic of the application works correctly in
case of || = CONCAT is used. Think also about an artificial limit
your create -- each and every varchar has to be padded to it's
maximum length (become CHAR) -- that optional and case dependent,
however; all date and numeric columns have to be formatted
otherwise you can face the same case like your your example.

SQL SELECT COUNT(*) FROM emp;

COUNT(*)
--
   64000

SQL select count(distinct(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
 2000

SQL SELECT COUNT(*)
2   FROM (
3SELECT DISTINCT
4 ename, job, mydate
5FROM emp
6   );

COUNT(*)
--
7000

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Begun
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL question

Jared

Windows 2k 9.2.0.1

534 hsecs
214 hsecs

Query I've used:

SELECT COUNT(
 DISTINCT(
RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1))
 || RPAD(NVL(  job, ' '), 30 + NVL2(  job, 0, 1))
 || NVL(TO_CHAR(mydate, 'DDMMHH24MISS'), '*NULL*')
 )
   ) AS l
  FROM emp
/

As you can see it's tightly bound to table definition one
has to handle nulls for varchars/chars.

L
-
 7000

Check the resources -- I have doubts that this query is a
winner :)

So, the moral of this story:
. never trust Vladimir Begun, check everything what he's saying :)
. never use the sql that looks cool but does not work properly
. never tune a query that returns wrong result and compare its
  performance with that one that works correctly but slowly.

Thanks!

Regards,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

Vladimir Begun wrote:

Jared

Jared Still wrote:


Though not a dramatic difference, the CONCAT was faster
and less resource intensive than the inline view with GROUP BY.

:)



Ok, let it be like that, but your test does not check some
other things, like common sense, logic, and session memory.
Performance can vary as I mentioned sometimes can be
neglected, however let's consider the tricks you made before
your test:

1. create index emp_idx on emp(ename, job, mydate); -- what for do
you need it? It was not in the original problem definition. It's
not used, however you created it, what's that for?

2. Both queries give different results, that's what I mentioned --
you just proved my words :) One must be very careful with that.
Even DISTINCT can lose sometimes like in your example, but it does
not mean that the logic of the application works correctly in
case of || = CONCAT is used. Think also about an artificial limit
your create -- each and every varchar has to be padded to it's
maximum length (become CHAR) -- that optional and case dependent,
however; all date and numeric columns have to be formatted
otherwise you can face the same case like your your example.

SQL SELECT COUNT(*) FROM emp;

   COUNT(*)
--
  64000

SQL select count(distinct(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
2000

SQL SELECT COUNT(*)
   2   FROM (
   3SELECT DISTINCT
   4 ename, job, mydate
   5FROM emp
   6   );

   COUNT(*)
--
   7000



--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
 INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question

Nah, I'm tired of it now.

It was an interesting diversion.

Jared






Khedr, Waleed [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 01/30/2003 11:34 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:RE: SQL question


What about:

select count(count(*))
from emp
group by ename, job



Have fun :)

Waleed

-Original Message-
Sent: Thursday, January 30, 2003 1:55 PM
To: Multiple recipients of list ORACLE-L


Jared

Jared Still wrote:
 Though not a dramatic difference, the CONCAT was faster
 and less resource intensive than the inline view with GROUP BY.
 
 :)

Ok, let it be like that, but your test does not check some
other things, like common sense, logic, and session memory.
Performance can vary as I mentioned sometimes can be
neglected, however let's consider the tricks you made before
your test:

1. create index emp_idx on emp(ename, job, mydate); -- what for do
you need it? It was not in the original problem definition. It's
not used, however you created it, what's that for?

2. Both queries give different results, that's what I mentioned --
you just proved my words :) One must be very careful with that.
Even DISTINCT can lose sometimes like in your example, but it does
not mean that the logic of the application works correctly in
case of || = CONCAT is used. Think also about an artificial limit
your create -- each and every varchar has to be padded to it's
maximum length (become CHAR) -- that optional and case dependent,
however; all date and numeric columns have to be formatted
otherwise you can face the same case like your your example.

SQL SELECT COUNT(*) FROM emp;

COUNT(*)
--
   64000

SQL select count(distinct(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
 2000

SQL SELECT COUNT(*)
2   FROM (
3SELECT DISTINCT
4 ename, job, mydate
5FROM emp
6   );

COUNT(*)
--
7000

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Begun
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL question

 Ok, let it be like that, but your test does not check some
 other things, like common sense, logic, and session memory.
 Performance can vary as I mentioned sometimes can be
 neglected, however let's consider the tricks you made before
 your test:

Well, I've never claimed to be common.

And I didn't ignore session memory.  If the PGA and UGA memory
numbers are the same between runs, they don't appear in the
run_stats.sql query.

 1. create index emp_idx on emp(ename, job, mydate); -- what for do
 you need it? It was not in the original problem definition. It's
 not used, however you created it, what's that for?

An index seemed reasonable to me.

But in the cause of fairness, I dropped the index and reran it
a couple times to allow for re-caching the blocks.  Similar results.

13:17:17 SQL@th
389 hsecs
257 hsecs

PL/SQL procedure successfully completed.
13:17:25 SQL@run_stats

NAME   RUN1   RUN2   DIFF
 -- -- --
LATCH.active checkpoint queue latch   2  1 -1
LATCH.redo writing6  5 -1
STAT...calls to get snapshot scn: kcmgss 12 11 -1
STAT...redo entries   8  7 -1
STAT...messages sent  1  0 -1
STAT...deferred (CURRENT) block cleanout  3  2 -1
 applications

LATCH.library cache  74 71 -3
STAT...db block changes  17 14 -3
STAT...consistent gets 3346   3343 -3
LATCH.redo allocation 9 12  3
STAT...session logical reads   3458   3462  4
LATCH.messages   11  6 -5
STAT...db block gets112119  7
STAT...enqueue requests  41 50  9
LATCH.sort extent pool   90100 10
STAT...enqueue releases  40 50 10
LATCH.enqueue hash chains80100 20
LATCH.cache buffers lru chain63 23-40
LATCH.loader state object freelist   20 60 40
LATCH.enqueues  160200 40
LATCH.checkpoint queue latch 90  7-83
LATCH.cache buffers chains 7662   7783121
STAT...redo size  20888  20756   -132
STAT...recursive cpu usage  391258   -133
STAT...physical reads   552792240
STAT...physical reads direct550790240
STAT...physical writes  550790240
STAT...physical writes direct   550790240
STAT...physical writes non checkpoint   550790240
STAT...session pga memory 0  15848  15848
STAT...session pga memory max 0  15848  15848

31 rows selected.


 2. Both queries give different results, that's what I mentioned --
 you just proved my words :) One must be very careful with that.
 Even DISTINCT can lose sometimes like in your example, but it does
 not mean that the logic of the application works correctly in
 case of || = CONCAT is used. Think also about an artificial limit
 your create -- each and every varchar has to be padded to it's
 maximum length (become CHAR) -- that optional and case dependent,
 however; all date and numeric columns have to be formatted
 otherwise you can face the same case like your your example.

 SQL SELECT COUNT(*) FROM emp;

COUNT(*)
--
   64000

SQL select count(distinct(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
 2000
SQL SELECT COUNT(*)
2   FROM (
3SELECT DISTINCT
4 ename, job, mydate
5FROM emp
6   );

COUNT(*)
--
7000

Interesting. My results correspond, I don't know why the difference.

13:11:04 SQLset echo on
13:11:08 SQL@q1
13:11:09 SQLselect count(distinct(ename||job||mydate))
13:11:09   2  from emp
13:11:09   3  /

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
   7000

1 row selected.

13:11:09 SQL@q2
13:11:12 SQL
13:11:12 SQL
13:11:12 SQLSELECT COUNT(*)
13:11:12   2  FROM (
13:11:12   3  SELECT DISTINCT
13:11:12   4  ename, job, mydate
13:11:12   5  FROM emp
13:11:12   6  )
13:11:12   7  /

  COUNT(*)
--
  7000

1 row selected.

13:11:12 SQL




--

Re: SQL question

. never trust Vladimir Begun, check everything what he's saying :)

Trust?

I don't know you well enough to not trust you.

. never use the sql that looks cool but does not work properly
. never tune a query that returns wrong result and compare its
   performance with that one that works correctly but slowly.


As you will see in another post, both queries
return identical results for me on 8.1.7.

Jared






Vladimir Begun [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 01/30/2003 12:16 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:Re: SQL question


Jared

Windows 2k 9.2.0.1

534 hsecs
214 hsecs

Query I've used:

SELECT COUNT(
  DISTINCT(
 RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1))
  || RPAD(NVL(  job, ' '), 30 + NVL2(  job, 0, 1))
  || NVL(TO_CHAR(mydate, 'DDMMHH24MISS'), '*NULL*')
  )
) AS l
   FROM emp
/

As you can see it's tightly bound to table definition one
has to handle nulls for varchars/chars.

 L
-
  7000

Check the resources -- I have doubts that this query is a
winner :)

So, the moral of this story:
. never trust Vladimir Begun, check everything what he's saying :)
. never use the sql that looks cool but does not work properly
. never tune a query that returns wrong result and compare its
   performance with that one that works correctly but slowly.

Thanks!

Regards,
-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

Vladimir Begun wrote:
 Jared
 
 Jared Still wrote:
 
 Though not a dramatic difference, the CONCAT was faster
 and less resource intensive than the inline view with GROUP BY.

 :)
 
 
 Ok, let it be like that, but your test does not check some
 other things, like common sense, logic, and session memory.
 Performance can vary as I mentioned sometimes can be
 neglected, however let's consider the tricks you made before
 your test:
 
 1. create index emp_idx on emp(ename, job, mydate); -- what for do
 you need it? It was not in the original problem definition. It's
 not used, however you created it, what's that for?
 
 2. Both queries give different results, that's what I mentioned --
 you just proved my words :) One must be very careful with that.
 Even DISTINCT can lose sometimes like in your example, but it does
 not mean that the logic of the application works correctly in
 case of || = CONCAT is used. Think also about an artificial limit
 your create -- each and every varchar has to be padded to it's
 maximum length (become CHAR) -- that optional and case dependent,
 however; all date and numeric columns have to be formatted
 otherwise you can face the same case like your your example.
 
 SQL SELECT COUNT(*) FROM emp;
 
COUNT(*)
 --
   64000
 
 SQL select count(distinct(ename||job||mydate)) FROM emp;
 
 COUNT(DISTINCT(ENAME||JOB||MYDATE))
 ---
 2000
 
 SQL SELECT COUNT(*)
2   FROM (
3SELECT DISTINCT
4 ename, job, mydate
5FROM emp
6   );
 
COUNT(*)
 --
7000
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Begun
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL question

Jared

[EMAIL PROTECTED] wrote:

. never trust Vladimir Begun, check everything what he's saying :)

Trust?

I don't know you well enough to not trust you.


May be 'trust' is not a right word here :) Sorry.


. never use the sql that looks cool but does not work properly
. never tune a query that returns wrong result and compare its
   performance with that one that works correctly but slowly.

As you will see in another post, both queries
return identical results for me on 8.1.7.


Check default NLS_DATE_FORMAT parameters of your session.

To summarize, agregation can be done using:

1. (for this particular case)
SELECT COUNT(
  DISTINCT(
 RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1))
  || RPAD(NVL(  job, ' '), 30 + NVL2(  job, 0, 1))
  || NVL(TO_CHAR(mydate, 'DDMMHH24MISS'), '*NULL*')
  )
) AS l
   FROM emp
/
2.
SELECT COUNT(*)
  FROM (
   SELECT DISTINCT
  ename
, job
, mydate
 FROM emp
   )
/
3.
SELECT COUNT(COUNT(*))
  FROM emp
 GROUP BY
   ename
 , job
 , mydate
/
4. SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp;
unreliable solution (does not handle nulls and dates
properly)

SQL SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
   2000

SQL ALTER SESSION SET NLS_DATE_FORMAT='DD.MM. HH24:MI:SS';

Session altered

SQL SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))
---
   6000

Regards,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
 INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL question

Looks like you are a master of telepathy too... :)

Khedr, Waleed wrote:

What about:

select count(count(*))
from emp
group by ename, job

Have fun :)


We do... :)
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
 INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL question

[EMAIL PROTECTED] wrote:
 . never trust Vladimir Begun, check everything what he's saying :)
 
 Trust?
 
 I don't know you well enough to not trust you.

May be 'trust' is not a right word here :) Sorry.

Mine was supposed to have a :).  Sorry.

 . never use the sql that looks cool but does not work properly
 . never tune a query that returns wrong result and compare its
performance with that one that works correctly but slowly.
 
 As you will see in another post, both queries
 return identical results for me on 8.1.7.

Check default NLS_DATE_FORMAT parameters of your session.


Yup, that was it.

Though I tend to always use mm/dd/ hh24:mi:ss unless
there's some particular reason to do otherwise.

Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question

2003-01-29 Thread Fink, Dan

Charu,
The COUNT() function requires a single expression. ename, job is
not a valid expression. ename||job is a valid expression since it will
return a single value.
Another alternative would be
select count(*)
from (select distinct ename, job from emp);

Dan Fink

-Original Message-
Sent: Wednesday, January 29, 2003 11:19 AM
To: Multiple recipients of list ORACLE-L


Hello Listers,

How to find out the COUNT of DISTINCT values of multiple columns?

For eg.

SQL SELECT DISTINCT ename FROM emp;
-- This works.

SQL SELECT COUNT(DISTINCT ename) FROM emp;
-- So does this.

SQL SELECT DISTINCT ename, job FROM emp;
-- And this too.

SQL SELECT COUNT(DISTINCT ename, job) FROM emp;
-- So why does this fail?

I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant
way of doing it.

I have a feeling I might be missing some fairly basic syntax, but feeling
dumb is better than suspense.

Thanks  regards,
Charu.

*
Disclaimer

This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*
Visit us at http://www.mahindrabt.com



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Charu Joshi
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Fink, Dan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question

2003-01-29 Thread Whittle Jerome Contr NCI

Title: RE: SQL question






Joshi,


SELECT count(*) 

FROM (SELECT count(*) 

 FROM flight_legs 

 GROUP BY d_actual_time, event_type);


SELECT count(*) 

FROM (SELECT DISTINCT d_actual_time, event_type

 FROM flight_legs );


The first one took about 37 seconds in returning a count of 357331. The second statement was about 10 seconds quicker.


Jerry Whittle

ASIFICS DBA

NCI Information Systems Inc.

[EMAIL PROTECTED]

618-622-4145


-Original Message-

From: Charu Joshi [SMTP:[EMAIL PROTECTED]]


Hello Listers,


How to find out the COUNT of DISTINCT values of multiple columns?


For eg.


SQL SELECT DISTINCT ename FROM emp;

-- This works.


SQL SELECT COUNT(DISTINCT ename) FROM emp;

-- So does this.


SQL SELECT DISTINCT ename, job FROM emp;

-- And this too.


SQL SELECT COUNT(DISTINCT ename, job) FROM emp;

-- So why does this fail?


I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant

way of doing it.


I have a feeling I might be missing some fairly basic syntax, but feeling

dumb is better than suspense.


Thanks  regards,

Charu.

Re: SQL question

2003-01-29 Thread Rachna Vaidya

And, can you have two columns as arguements for COUNT?
I guess its either one column or rows

+Rachna

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, January 29, 2003 1:19 PM


 Hello Listers,

 How to find out the COUNT of DISTINCT values of multiple columns?

 For eg.

 SQL SELECT DISTINCT ename FROM emp;
 -- This works.

 SQL SELECT COUNT(DISTINCT ename) FROM emp;
 -- So does this.

 SQL SELECT DISTINCT ename, job FROM emp;
 -- And this too.

 SQL SELECT COUNT(DISTINCT ename, job) FROM emp;
 -- So why does this fail?

 I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be
elegant
 way of doing it.

 I have a feeling I might be missing some fairly basic syntax, but feeling
 dumb is better than suspense.

 Thanks  regards,
 Charu.

 *
 Disclaimer

 This message (including any attachments) contains
 confidential information intended for a specific
 individual and purpose, and is protected by law.
 If you are not the intended recipient, you should
 delete this message and are hereby notified that
 any disclosure, copying, or distribution of this
 message, or the taking of any action based on it,
 is strictly prohibited.

 *
 Visit us at http://www.mahindrabt.com



 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Charu Joshi
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachna Vaidya
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question

2003-01-29 Thread Koivu, Lisa

Title: RE: SQL question





Elegant or not, here's how I'd do it


select count(*) from 
(select distinct ename, job from emp);



-Original Message-
From: Charu Joshi [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 29, 2003 1:19 PM
To: Multiple recipients of list ORACLE-L
Subject: SQL question



Hello Listers,


How to find out the COUNT of DISTINCT values of multiple columns?


For eg.


SQL SELECT DISTINCT ename FROM emp;
-- This works.


SQL SELECT COUNT(DISTINCT ename) FROM emp;
-- So does this.


SQL SELECT DISTINCT ename, job FROM emp;
-- And this too.


SQL SELECT COUNT(DISTINCT ename, job) FROM emp;
-- So why does this fail?


I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant
way of doing it.


I have a feeling I might be missing some fairly basic syntax, but feeling
dumb is better than suspense.


Thanks  regards,
Charu.


*
Disclaimer


This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.


*
Visit us at http://www.mahindrabt.com




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Charu Joshi
 INET: [EMAIL PROTECTED]


Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

RE: SQL question

2003-01-29 Thread Jamadagni, Rajendra

Title: RE: SQL question





The non-working code in your example should be 


select count(*)
from (select distinct ename,job from emp)
/


It appears that cound takes only one parameter ... not two.
Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-
From: Charu Joshi [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 29, 2003 1:19 PM
To: Multiple recipients of list ORACLE-L
Subject: SQL question



Hello Listers,


How to find out the COUNT of DISTINCT values of multiple columns?


For eg.


SQL SELECT DISTINCT ename FROM emp;
-- This works.


SQL SELECT COUNT(DISTINCT ename) FROM emp;
-- So does this.


SQL SELECT DISTINCT ename, job FROM emp;
-- And this too.


SQL SELECT COUNT(DISTINCT ename, job) FROM emp;
-- So why does this fail?


I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be elegant
way of doing it.


I have a feeling I might be missing some fairly basic syntax, but feeling
dumb is better than suspense.


Thanks  regards,
Charu.


*
Disclaimer


This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.


*
Visit us at http://www.mahindrabt.com




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Charu Joshi
 INET: [EMAIL PROTECTED]


Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).



*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

Re: SQL question

2003-01-29 Thread Jared . Still

 I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be 
elegant
 way of doing it.

elegant = simple, concise, easy to understand.

Looks elegant to me.

Jared






Charu Joshi [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 01/29/2003 10:19 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Subject:SQL question


Hello Listers,

How to find out the COUNT of DISTINCT values of multiple columns?

For eg.

SQL SELECT DISTINCT ename FROM emp;
-- This works.

SQL SELECT COUNT(DISTINCT ename) FROM emp;
-- So does this.

SQL SELECT DISTINCT ename, job FROM emp;
-- And this too.

SQL SELECT COUNT(DISTINCT ename, job) FROM emp;
-- So why does this fail?

I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be 
elegant
way of doing it.

I have a feeling I might be missing some fairly basic syntax, but feeling
dumb is better than suspense.

Thanks  regards,
Charu.

*


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL question

2003-01-29 Thread Vladimir Begun

[EMAIL PROTECTED] wrote:

I can do a COUNT(DISTINCT ename || job), but that doesn't seem to be 

elegant


way of doing it.


elegant = simple, concise, easy to understand.

Looks elegant to me.


Jared, it just looks that that...

CONCAT = || yet another function call, yet another piece of
code, yet another byte of memory... If you have more than
two columns? If some of those are numeric, date? If ename
is Smith and job is Smith and both can be nullable? :)
NVLs? NVL2s? I think this approach is only valid when one
really understands what she/he is looking for. Could be
good for FBI, CHECK constraints but it's very risky and
resource consuming (depends, can be neglected) for
queries.

It's better to write something that just looks ugly but
works faster and reliably. Simple, fast, and covers all
'strange' cases:

SELECT COUNT(*)
  FROM (
   SELECT DISTINCT
  ename
, job
 FROM emp
   )
/

Regards,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
 INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question

2003-01-28 Thread Naveen Nahata




Try
select 'insert into XXX 
(IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||(IDU + 
1)||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from 
app_users
where pen_id in 
(44541,41402,41813);
-Original Message-From: Henrik Ekenberg 
[EMAIL PROTECTED][mailto:[EMAIL PROTECTED]]Sent: Tuesday, 
January 28, 2003 12:14 PMTo: Multiple recipients of list 
ORACLE-LSubject: SQL QuestionHi,My brain is slow 
today Can someone help me ?I can do :select idu+1 from 
user_group_members where fk_userin(44541,41402,41813) 
; 
IDU+1-- 
41411 41821 
44546But I can't do :select 'insert into XXX 
(IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU + 1 
||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_userswhere pen_id 
in (44541,41402,41813) ;I've got on IDU+1 :ERROR at line 
1:ORA-01722: invalid numberBest 
RegardsHenrik-There's 
fun in being serious.-- Wynton MarsalisHenrik 
Ekenberg 
Anoto AB--Please see the official ORACLE-L FAQ: http://www.orafaq.net--Author: Henrik 
Ekenber INET: [EMAIL PROTECTED]" 
[EMAIL PROTECTED]Fat City Network Services -- 
858-538-5051 http://www.fatcity.comSan Diego, 
California -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from). You mayalso send the HELP command for 
other information (like subscribing).DISCLAIMER:This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return  e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited.  Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission.

RE: SQL Question

2003-01-28 Thread Nirmal Kumar Muthu Kumaran

select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
('||(IDU + 1 )||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from
app_users
where pen_id in (44541,41402,41813) ;

IDU + 1 must be replaced by (IDU + 1).

HTH.
Nirmal.,

-Original Message-
Sent: Tuesday, January 28, 2003 9:44 AM
To: Multiple recipients of list ORACLE-L


Hi,

My brain is slow today Can someone help me ?

I can do :

select idu+1 from user_group_members where fk_user
in(44541,41402,41813) ;

 IDU+1
--
 41411
 41821
 44546

But I can't do :
select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
('||IDU + 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
where pen_id in (44541,41402,41813) ;

I've got on IDU+1 :

ERROR at line 1:
ORA-01722: invalid number


Best Regards
Henrik

-- 
---
There's fun in being serious.

-- Wynton Marsalis

Henrik EkenbergAnoto AB


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Henrik Ekenber
  INET: [EMAIL PROTECTED] [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nirmal Kumar  Muthu Kumaran
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL Question

2003-01-28 Thread Dmitrii CRETU

try this ((IDU + 1)):

select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
(' || (IDU + 1)
||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
where pen_id in (44541,41402,41813) ;

HEheac Hi,

HEheac My brain is slow today Can someone help me ?

HEheac I can do :

HEheac select idu+1 from user_group_members where fk_user
HEheac in(44541,41402,41813) ;

HEheac  IDU+1
HEheac --
HEheac  41411
HEheac  41821
HEheac  44546

HEheac But I can't do :
HEheac select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values ('||IDU 
+ 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
HEheac where pen_id in (44541,41402,41813) ;

HEheac I've got on IDU+1 :

HEheac ERROR at line 1:
HEheac ORA-01722: invalid number


HEheac Best Regards
HEheac Henrik

HEheac -- 
HEheac ---
HEheac There's fun in being serious.

HEheac -- Wynton Marsalis

HEheac Henrik EkenbergAnoto AB


HEheac -- 
HEheac Please see the official ORACLE-L FAQ: http://www.orafaq.net


Best regards,
 Dmitrii

[EMAIL PROTECTED]


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Dmitrii CRETU
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL Question

2003-01-28 Thread Tim Gorman

The first query says where FK_USER in (44541,41402,41813) and the second
query says where PEN_ID in (44541,41402,41813)...

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 27, 2003 11:43 PM


 Hi,

 My brain is slow today Can someone help me ?

 I can do :

 select idu+1 from user_group_members where fk_user
 in(44541,41402,41813) ;

  IDU+1
 --
  41411
  41821
  44546

 But I can't do :
 select 'insert into XXX (IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
('||IDU + 1 ||','||PEN_ID||',sysdate,'||FK_APPLICATION||');' from app_users
 where pen_id in (44541,41402,41813) ;

 I've got on IDU+1 :

 ERROR at line 1:
 ORA-01722: invalid number


 Best Regards
 Henrik

 --
 --
-
 There's fun in being serious.

 -- Wynton Marsalis

 Henrik EkenbergAnoto AB


 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Henrik Ekenber
   INET: [EMAIL PROTECTED] [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Re: SQL Question

2003-01-28 Thread Stephane Faroult

The first query also says 'from user_group_members' and the second one 'from 
app_users' ... I am not sure that the comparison is anything but confusing ...

Looks like the implicitly converted varchar2() column which contains '***', 'N/A' or 
the like ...


The first query says where FK_USER in
(44541,41402,41813) and the second
query says where PEN_ID in (44541,41402,41813)...


- Original Message -
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Monday, January 27, 2003 11:43 PM


 Hi,

 My brain is slow today Can someone help me ?

 I can do :

 select idu+1 from user_group_members where
fk_user
 in(44541,41402,41813) ;

  IDU+1
 --
  41411
  41821
  44546

 But I can't do :
 select 'insert into XXX
(IDU,PEN_ID,LAST_ACCESS,FK_APPLICATION) values
('||IDU + 1
||','||PEN_ID||',sysdate,'||FK_APPLICATION||');'
from app_users
 where pen_id in (44541,41402,41813) ;

 I've got on IDU+1 :

 ERROR at line 1:
 ORA-01722: invalid number


 Best Regards
 Henrik

 --

-
 There's fun in being serious.

 -- Wynton Marsalis

 Henrik Ekenberg   
Anoto AB


 --
 Please see the official ORACLE-L FAQ:
http://www.orafaq.net
 --
 Author: Henrik Ekenber
   INET: [EMAIL PROTECTED]
[EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051
http://www.fatcity.com
 San Diego, California-- Mailing list and
web hosting services

 To REMOVE yourself from this mailing list, send
an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
 the message BODY, include a line containing:
UNSUB ORACLE-L
 (or the name of mailing list you want to be
removed from).  You may
 also send the HELP command for other information
(like subscribing).


-- 
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051
http://www.fatcity.com
San Diego, California-- Mailing list and
web hosting services
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from).  You may
also send the HELP command for other information
(like subscribing).
---
---
---
--
---
--


Regards,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroul
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question avoiding 2 views and not in

2002-12-16 Thread Stephane Paquette

Thanks for the where clause and to all who respond,
I'll check into fine grained access control
(dbms_rls). 

 --- Khedr, Waleed [EMAIL PROTECTED] a écrit :
 Add this to where clause:
 
  group  decode(user,'typical',380,-100)
 
 Instead of -100 use any number not used by the
 groups.
 
 Also read about contexts and grain level security.
 
 Waleed
 
 -Original Message-
 Sent: Friday, December 13, 2002 2:59 PM
 To: Multiple recipients of list ORACLE-L
 
 
 Hi,
 
 We have a lot of views. Now the users have a new
 requirement, only the user 'admin' can see all the
 data from the views. The user 'typical' must see all
 data except the one from group 380.
 
 A basic solution is to create 2 sets of views with
 one
 set having a group number  380.
 
 I'm looking for a solution to have only one set of
 views. It's friday afternoon and I have no
 inspiration
 :-(
 
 TIA
 
 
 =
 Stéphane Paquette
 DBA Oracle et DB2, consultant entrepôt de données
 Oracle and DB2 DBA, datawarehouse consultant
 [EMAIL PROTECTED]
 

__
 Lèche-vitrine ou lèche-écran ?
 magasinage.yahoo.ca
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.com
 -- 
 Author: =?iso-8859-1?q?Stephane=20Paquette?=
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com
 San Diego, California-- Mailing list and web
 hosting services

-
 To REMOVE yourself from this mailing list, send an
 E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of
 'ListGuru') and in
 the message BODY, include a line containing: UNSUB
 ORACLE-L
 (or the name of mailing list you want to be removed
 from).  You may
 also send the HELP command for other information
 (like subscribing).
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.com
 -- 
 Author: Khedr, Waleed
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com
 San Diego, California-- Mailing list and web
 hosting services

-
 To REMOVE yourself from this mailing list, send an
 E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of
 'ListGuru') and in
 the message BODY, include a line containing: UNSUB
 ORACLE-L
 (or the name of mailing list you want to be removed
 from).  You may
 also send the HELP command for other information
 (like subscribing).
  

=
Stéphane Paquette
DBA Oracle et DB2, consultant entrepôt de données
Oracle and DB2 DBA, datawarehouse consultant
[EMAIL PROTECTED]

__
Lèche-vitrine ou lèche-écran ?
magasinage.yahoo.ca
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Stephane=20Paquette?=
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question avoiding 2 views and not in

2002-12-15 Thread Mark Richard

I have also been on a project which used fine-grained access control.  The
project was a reporting application for a large organisation and one of the
problems we found we dealing with the various levels of security.  The
organisation has ~10,000 cost centres and some users could see only a very
small branch, whilst others could see the entire structure, whilst others
could see almost everything except for a few very high level branches.
There was also a different way of providing security (ie: not cost centre
related) and many users had a combination of both to be applied.

One of the biggest gotchas was performance tuning the application.
Because of the complexity of our security implementation we initially fell
in the trap of tuning the query with no security added (a couple of users
had the clause and 1 = 1).  Naturally when other users connected the
explain plan changed and performance was pretty average.  It took a while
to index all tables in such a way that it worked well for every type of
security clause.  So, yes there can be a performance hit and that hit can
vary dramatically depending on what type of clause you are adding.

Having said that, it did work and was transparent to the front end,
allowing users to create their own queries / reports and still be bound by
the security model.

Cheers,
 Mark.



   

[EMAIL PROTECTED] 

.tenet.edu   To: Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]   
Sent by: cc:   

[EMAIL PROTECTED] Subject: RE: SQL question 
avoiding 2 views and not in 
   

   

14/12/2002 10:03   

Please respond to  

ORACLE-L   

   

   






Lisa,

A couple of years ago, when I was a consultant, I implemented Application
Context and Fine-Grained Access Control, AKA Row Level Security for a
client.

Since it causes a predicate to be appended to the Where clause of every SQL
statement issued against the tables having a Security Policy, I guess
performance could be impacted if you didn't index the columns referenced by
the appended predicates.  We never noticed a bit of degradation in our
testing, but we were careful about the indexing.

I left that project before it went into production, so I don't know the
ultimate outcome.  However, I'd sure use FGAC again, if the need arises, it
works very well.  Actually, I probably *will* use it on a couple of our 3rd
Party apps here which don't enforce security to the degree that we require.
I'll let y'all know how it performs.

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]




  Koivu, Lisa

  Lisa.Koivu@efairTo:   Multiple
recipients of list ORACLE-L
  field.com[EMAIL PROTECTED]

  Sent by: cc:

  [EMAIL PROTECTED] Subject:  RE: SQL question
avoiding 2 views and not in


  12/13/2002 03:38

  PM

  Please respond to

  ORACLE-L






Has anyone used context and fine-grained security?  I seem to remember the
performance hit was not minimal when using this functionality.





--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list

RE: SQL question avoiding 2 views and not in

2002-12-13 Thread Nick Wagner

Title: RE: SQL question avoiding 2 views and not in 





OLS -- Oracle Label Security... I think that's the key you are looking for. 


-Original Message-
From: Stephane Paquette [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 13, 2002 11:59 AM
To: Multiple recipients of list ORACLE-L
Subject: SQL question avoiding 2 views and not in 



Hi,


We have a lot of views. Now the users have a new
requirement, only the user 'admin' can see all the
data from the views. The user 'typical' must see all
data except the one from group 380.


A basic solution is to create 2 sets of views with one
set having a group number  380.


I'm looking for a solution to have only one set of
views. It's friday afternoon and I have no inspiration
:-(


TIA



=
Stéphane Paquette
DBA Oracle et DB2, consultant entrepôt de données
Oracle and DB2 DBA, datawarehouse consultant
[EMAIL PROTECTED]


__
Lèche-vitrine ou lèche-écran ?
magasinage.yahoo.ca
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Stephane=20Paquette?=
 INET: [EMAIL PROTECTED]


Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

RE: SQL question avoiding 2 views and not in

2002-12-13 Thread Khedr, Waleed

Add this to where clause:

 group  decode(user,'typical',380,-100)

Instead of -100 use any number not used by the groups.

Also read about contexts and grain level security.

Waleed

-Original Message-
Sent: Friday, December 13, 2002 2:59 PM
To: Multiple recipients of list ORACLE-L


Hi,

We have a lot of views. Now the users have a new
requirement, only the user 'admin' can see all the
data from the views. The user 'typical' must see all
data except the one from group 380.

A basic solution is to create 2 sets of views with one
set having a group number  380.

I'm looking for a solution to have only one set of
views. It's friday afternoon and I have no inspiration
:-(

TIA


=
Stéphane Paquette
DBA Oracle et DB2, consultant entrepôt de données
Oracle and DB2 DBA, datawarehouse consultant
[EMAIL PROTECTED]

__
Lèche-vitrine ou lèche-écran ?
magasinage.yahoo.ca
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Stephane=20Paquette?=
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question avoiding 2 views and not in

2002-12-13 Thread Koivu, Lisa

Title: RE: SQL question avoiding 2 views and not in 






Hi Stephane, 


This may be more effort but have you considered having a security table to join to in the one view, instead of two views? Multiple views can really hose the optimizer, as I am sure you know. However adding a table then creates a task for someone, because the data must be maintained. And it would have to be a complete set of data if you are avoiding the not in clause. 

by the way, I wanted to say this earlier but resisted the urge... how do you answer the interview question about being in a stressful situation like a recovery when your last name is Panicker? 

Have a great weekend everyone!


Lisa Koivu

Oracle Datababy Administratikiss and Wild Bamboo Stick Wielder. 

Fairfield Resorts, Inc.

5259 Coconut Creek Parkway

Ft. Lauderdale, FL, USA 33063




-Original Message-

From: Stephane Paquette [SMTP:[EMAIL PROTECTED]]

Sent: Friday, December 13, 2002 2:59 PM

To: Multiple recipients of list ORACLE-L

Subject: SQL question avoiding 2 views and not in 


Hi,


We have a lot of views. Now the users have a new

requirement, only the user 'admin' can see all the

data from the views. The user 'typical' must see all

data except the one from group 380.


A basic solution is to create 2 sets of views with one

set having a group number  380.


I'm looking for a solution to have only one set of

views. It's friday afternoon and I have no inspiration

:-(


TIA



=

Stéphane Paquette

DBA Oracle et DB2, consultant entrepôt de données

Oracle and DB2 DBA, datawarehouse consultant

[EMAIL PROTECTED]


__

Lèche-vitrine ou lèche-écran ?

magasinage.yahoo.ca

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: =?iso-8859-1?q?Stephane=20Paquette?=

 INET: [EMAIL PROTECTED]


Fat City Network Services -- 858-538-5051 http://www.fatcity.com

San Diego, California -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may

also send the HELP command for other information (like subscribing).

RE: SQL question avoiding 2 views and not in

2002-12-13 Thread Jamadagni, Rajendra

Title: RE: SQL question avoiding 2 views and not in

dbms_rls is cheaper to use ...

Raj
__
Rajendra
Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot
com
Any opinion expressed here is
personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but
having an opinion is an art!

-Original Message-From: Nick Wagner
[mailto:[EMAIL PROTECTED]]Sent: Friday, December 13, 2002 4:10
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
SQL question avoiding 2 views and not in
OLS -- Oracle Label Security... I think that's the key
you are looking for.
-Original Message- From:
Stephane Paquette [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 13, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Subject: SQL question avoiding 2 views and not in
Hi,
We have a lot of views. Now the users have a new
requirement, only the user 'admin' can see all the
data from the views. The user 'typical' must see all
data except the one from group 380.
A basic solution is to create 2 sets of views with one
set having a group number 380.
I'm looking for a solution to have only one set of
views. It's friday afternoon and I have no inspiration
:-(
TIA
= Stéphane Paquette
DBA Oracle et DB2, consultant entrepôt de données
Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED]
__
Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author:
=?iso-8859-1?q?Stephane=20Paquette?= INET:
[EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego,
California -- Mailing list and web
hosting services -
To REMOVE yourself from this mailing list, send an E-Mail
message to: [EMAIL PROTECTED] (note EXACT spelling
of 'ListGuru') and in the message BODY, include a line
containing: UNSUB ORACLE-L (or the name of mailing
list you want to be removed from). You may also
send the HELP command for other information (like subscribing).

*This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*1

RE: SQL question avoiding 2 views and not in

2002-12-13 Thread Koivu, Lisa

Title: RE: SQL question avoiding 2 views and not in 






Has anyone used context and fine-grained security? I seem to remember the performance hit was not minimal when using this functionality. 

-Original Message-

From: Khedr, Waleed [SMTP:[EMAIL PROTECTED]]

Sent: Friday, December 13, 2002 4:14 PM

To: Multiple recipients of list ORACLE-L

Subject: RE: SQL question avoiding 2 views and not in 


Add this to where clause:


group  decode(user,'typical',380,-100)


Instead of -100 use any number not used by the groups.


Also read about contexts and grain level security.


Waleed


-Original Message-

Sent: Friday, December 13, 2002 2:59 PM

To: Multiple recipients of list ORACLE-L



Hi,


We have a lot of views. Now the users have a new

requirement, only the user 'admin' can see all the

data from the views. The user 'typical' must see all

data except the one from group 380.


A basic solution is to create 2 sets of views with one

set having a group number  380.


I'm looking for a solution to have only one set of

views. It's friday afternoon and I have no inspiration

:-(


TIA



=

Stéphane Paquette

DBA Oracle et DB2, consultant entrepôt de données

Oracle and DB2 DBA, datawarehouse consultant

[EMAIL PROTECTED]


__

Lèche-vitrine ou lèche-écran ?

magasinage.yahoo.ca

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: =?iso-8859-1?q?Stephane=20Paquette?=

 INET: [EMAIL PROTECTED]


Fat City Network Services -- 858-538-5051 http://www.fatcity.com

San Diego, California -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may

also send the HELP command for other information (like subscribing).

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Khedr, Waleed

 INET: [EMAIL PROTECTED]


Fat City Network Services -- 858-538-5051 http://www.fatcity.com

San Diego, California -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may

also send the HELP command for other information (like subscribing).

RE: SQL question avoiding 2 views and not in

2002-12-13 Thread JApplewhite


Lisa,

A couple of years ago, when I was a consultant, I implemented Application
Context and Fine-Grained Access Control, AKA Row Level Security for a
client.

Since it causes a predicate to be appended to the Where clause of every SQL
statement issued against the tables having a Security Policy, I guess
performance could be impacted if you didn't index the columns referenced by
the appended predicates.  We never noticed a bit of degradation in our
testing, but we were careful about the indexing.

I left that project before it went into production, so I don't know the
ultimate outcome.  However, I'd sure use FGAC again, if the need arises, it
works very well.  Actually, I probably *will* use it on a couple of our 3rd
Party apps here which don't enforce security to the degree that we require.
I'll let y'all know how it performs.

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]



   

  Koivu, Lisa

  Lisa.Koivu@efairTo:   Multiple recipients of list 
ORACLE-L  
  field.com[EMAIL PROTECTED] 

  Sent by: cc: 

  [EMAIL PROTECTED] Subject:  RE: SQL question avoiding 2 
views and not in  
   

   

  12/13/2002 03:38 

  PM   

  Please respond to

  ORACLE-L 

   

   




Has anyone used context and fine-grained security?  I seem to remember the
performance hit was not minimal when using this functionality.





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Sql question : use of SUBSTR/INSTR functions

2002-10-16 Thread Fink, Dan


Try this. It uses the INSTR function to determine the start and end of the
SUBSTR.

  1  select substr('333.22.1.000',1,instr('333.22.1.000','.')-1) octet1,
  2 substr('333.22.1.000',
  3(instr('333.22.1.000','.',1,1) + 1),
  4(instr('333.22.1.000','.',1,2) -
instr('333.22.1.000','.',1,1))-1) octect2,
  5 substr('333.22.1.000',
  6(instr('333.22.1.000','.',1,2) + 1),
  7(instr('333.22.1.000','.',1,3) -
instr('333.22.1.000','.',1,2))-1) octect3,
  8 substr('333.22.1.000',(instr('333.22.1.000','.',1,3) + 1))
octect3
  9* from dual
SQL /

OCT OC O OCT
--- -- - ---
333 22 1 000


Dan Fink

-Original Message-
Sent: Wednesday, October 16, 2002 2:32 PM
To: Multiple recipients of list ORACLE-L


Help!

Anybody have a quick and dirty to parse the 4 octets of a typical IP address
into 4 separate values. I will insert these into a table where  database
checks may  verify that the data is in fact a number and also part of a
valid ip range (the second thru fourth octets cannot be higher than 255. The
source data is very dirty and often fat-fingered, hence the painful
solution):

e.g.: 127.0.0.1 into 127 (val 1), 0 (val 2), 0 (val 3) and 1 (val 4).

I have used various flavors of substr/instr to unravel this, but the varying
length of the octets (up to 3 bytes) defeats my rudimentary sql coding
skills. I probably have to attack the IP with decode, and any input will be
very welcome.

Running V 8.1.6.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johan Muller
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Sql question : use of SUBSTR/INSTR functions

2002-10-16 Thread Alan Davey


Hi Johan,

Try this:
SELECT SUBSTR('127.0.0.1',1,INSTR('127.0.0.1','.')-1)
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.')+1,INSTR('127.0.0.1','.',1,2)-(INSTR('127.0.0.1','.')+1))
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.',1,2)+1,INSTR('127.0.0.1','.',1,3)-(INSTR('127.0.0.1','.',1,2)+1))
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.',1,3)+1,LENGTH('127.0.0.1')-INSTR('127.0.0.1','.',1,3)+1)
FROM DUAL


There may be a more elegant solution, but this was the quickest I could come up with.
-- 

Alan Davey
[EMAIL PROTECTED]
212-604-0200  x106


On 10/16/2002 4:32 PM, Johan Muller [EMAIL PROTECTED] wrote:
Help!

Anybody have a quick and dirty to parse the 4 octets of a typical 
IP address
into 4 separate values. I will insert these into a table where  database
checks may  verify that the data is in fact a number and also part 
of a
valid ip range (the second thru fourth octets cannot be higher than 
255. The
source data is very dirty and often fat-fingered, hence the painful
solution):

e.g.: 127.0.0.1 into 127 (val 1), 0 (val 2), 0 (val 3) and 1 (val 
4).

I have used various flavors of substr/instr to unravel this, but 
the varying
length of the octets (up to 3 bytes) defeats my rudimentary sql coding
skills. I probably have to attack the IP with decode, and any input 
will be
very welcome.

Running V 8.1.6.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johan Muller
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and 
in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alan Davey
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Sql question : use of SUBSTR/INSTR functions

2002-10-16 Thread Sherman, Paul R.


Hello,

Try this (take a hard look first, as I cranked this out quickly while doing
other things):

substr(ip_addr,1,instr(ip_addr,'.',1,1)-1
get 1st octet
substr(ip_addr,1,instr(ip_addr,'.',instr(ip_addr,'.',1,1)+1,1)-1
get 2nd octet
substr(ip_addr,1,instr(ip_addr,'.',instr(ip_addr,'.',1,2)+1,1)-1
get 3rd octet
substr(ip_addr,1,instr(ip_addr,'.',instr(ip_addr,'.',1,3)+1,1)  get
4th octet

Thank you,

Paul Sherman
DBAElcom, Inc.
voice -  781-501-4143 (direct #)
fax-  781-278-8341 (secure)
email - [EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, October 16, 2002 4:32 PM
To: Multiple recipients of list ORACLE-L


Help!

Anybody have a quick and dirty to parse the 4 octets of a typical IP address
into 4 separate values. I will insert these into a table where  database
checks may  verify that the data is in fact a number and also part of a
valid ip range (the second thru fourth octets cannot be higher than 255. The
source data is very dirty and often fat-fingered, hence the painful
solution):

e.g.: 127.0.0.1 into 127 (val 1), 0 (val 2), 0 (val 3) and 1 (val 4).

I have used various flavors of substr/instr to unravel this, but the varying
length of the octets (up to 3 bytes) defeats my rudimentary sql coding
skills. I probably have to attack the IP with decode, and any input will be
very welcome.

Running V 8.1.6.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johan Muller
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sherman, Paul R.
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Sql question : use of SUBSTR/INSTR functions

2002-10-16 Thread Mirsky, Greg


Anybody have a quick and dirty to parse the 4 octets of a typical IP
address

How about this...

FUNCTION f_ip_to_number (
   p_ipNVARCHAR2
)
   RETURN NUMBER
IS
   v_ip_segment1 NUMBER
 := SUBSTR (p_ip, 1, INSTR (p_ip, '.') -
1);
   v_ip_segment2 NUMBER
   := SUBSTR (
 p_ip
   , INSTR (p_ip, '.', 1, 1) + 1
   , INSTR (p_ip, '.', 1, 2) - INSTR (p_ip, '.', 1, 1) - 1
  );
   v_ip_segment3 NUMBER
   := SUBSTR (
 p_ip
   , INSTR (p_ip, '.', 1, 2) + 1
   , INSTR (p_ip, '.', 1, 3) - INSTR (p_ip, '.', 1, 2) - 1
  );
   v_ip_segment4 NUMBER
 := SUBSTR (p_ip, INSTR (p_ip, '.', -1)
+ 1);
BEGIN
   RETURN (  (  (v_ip_segment1 * 256 + v_ip_segment2)
  * 256
 )
   + v_ip_segment3
  )
* 256
  + v_ip_segment4;
END f_ip_to_number;
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mirsky, Greg
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question

2002-09-23 Thread Fink, Dan


It is a little awkward, but a union in an inline query may do the trick:

  1  select a.code
  2  from (select '10' code from dual union
  3select '20' code from dual union
  4select '30' code from dual union
  5select '40' code from dual union
  6select '50' code from dual ) a
  7* where a.code not in (select to_char(deptno) from emp)
SQL /

CO
--
40
50

Dan Fink

-Original Message-
Sent: Monday, September 23, 2002 10:28 AM
To: Multiple recipients of list ORACLE-L


Good morning list,

Environment HP-UX 11.0 Oracle 8.1.6

Can anyone help with this SQL.

I can get a result set of values from a table
that match a given list of values -

select code
from table
where code in ('A','B','C','D','E')

I can get a result set of values from a table
that do not match a given list of values -

select code
from table
where code not in ('A','B','C','D','E')

So far so good.

Now, how do I get the set of values from the list
that do NOT have a matching value in the table?

I cannot create any objects in the schema I am
working in otherwise I would create a table with
the values and do a minus, but I can't figure out
how to do it in SQL only.

Thanks in advance, folks.

Steve
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Haas
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question

2002-09-23 Thread Fink, Dan


1700 values? I sure hope you like to type...

Could you create a text file of the table values and compare those against a
text files of the possible values? This would require O/S level privs.


-Original Message-
Sent: Monday, September 23, 2002 11:38 AM
To: Multiple recipients of list ORACLE-L


Dan (and Charlie),

Thanks.
Good suggestions, but the IN clause contains just
over 1700 values.

Puzzling, huh?

steve


--- Fink, Dan [EMAIL PROTECTED] wrote:
 It is a little awkward, but a union in an
 inline query may do the trick:
 
   1  select a.code
   2  from (select '10' code from dual union
   3select '20' code from dual union
   4select '30' code from dual union
   5select '40' code from dual union
   6select '50' code from dual ) a
   7* where a.code not in (select
 to_char(deptno) from emp)
 SQL /
 
 CO
 --
 40
 50
 
 Dan Fink
 
 -Original Message-
 From: Steven Haas [mailto:[EMAIL PROTECTED]]
 Sent: Monday, September 23, 2002 10:28 AM
 To: Multiple recipients of list ORACLE-L
 Subject: SQL question
 
 
 Good morning list,
 
 Environment HP-UX 11.0 Oracle 8.1.6
 
 Can anyone help with this SQL.
 
 I can get a result set of values from a table
 that match a given list of values -
 
 select code
 from table
 where code in ('A','B','C','D','E')
 
 I can get a result set of values from a table
 that do not match a given list of values -
 
 select code
 from table
 where code not in ('A','B','C','D','E')
 
 So far so good.
 
 Now, how do I get the set of values from the
 list
 that do NOT have a matching value in the table?
 
 I cannot create any objects in the schema I am
 working in otherwise I would create a table
 with
 the values and do a minus, but I can't figure
 out
 how to do it in SQL only.
 
 Thanks in advance, folks.
 
 Steve
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.com
 -- 
 Author: Steven Haas
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com
 San Diego, California-- Mailing list
 and web hosting services

-
 To REMOVE yourself from this mailing list, send
 an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling
 of 'ListGuru') and in
 the message BODY, include a line containing:
 UNSUB ORACLE-L
 (or the name of mailing list you want to be
 removed from).  You may
 also send the HELP command for other
 information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Haas
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question

2002-09-23 Thread Nicoll, Iain \(Calanais\)


If the set of values is not too big and fixed you could do the minus using
dual


e.g.

(select 'A'
 from dual
 union
 select 'B'
 from dual
 union
 ...
 select 'Z'
 from dual)
minus
select code
from table


-Original Message-
Sent: Monday, September 23, 2002 5:28 PM
To: Multiple recipients of list ORACLE-L


Good morning list,

Environment HP-UX 11.0 Oracle 8.1.6

Can anyone help with this SQL.

I can get a result set of values from a table
that match a given list of values -

select code
from table
where code in ('A','B','C','D','E')

I can get a result set of values from a table
that do not match a given list of values -

select code
from table
where code not in ('A','B','C','D','E')

So far so good.

Now, how do I get the set of values from the list
that do NOT have a matching value in the table?

I cannot create any objects in the schema I am
working in otherwise I would create a table with
the values and do a minus, but I can't figure out
how to do it in SQL only.

Thanks in advance, folks.

Steve
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Haas
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain \(Calanais\)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question

2002-09-23 Thread Steven Haas


Dan (and Charlie),

Thanks.
Good suggestions, but the IN clause contains just
over 1700 values.

Puzzling, huh?

steve


--- Fink, Dan [EMAIL PROTECTED] wrote:
 It is a little awkward, but a union in an
 inline query may do the trick:
 
   1  select a.code
   2  from (select '10' code from dual union
   3select '20' code from dual union
   4select '30' code from dual union
   5select '40' code from dual union
   6select '50' code from dual ) a
   7* where a.code not in (select
 to_char(deptno) from emp)
 SQL /
 
 CO
 --
 40
 50
 
 Dan Fink
 
 -Original Message-
 From: Steven Haas [mailto:[EMAIL PROTECTED]]
 Sent: Monday, September 23, 2002 10:28 AM
 To: Multiple recipients of list ORACLE-L
 Subject: SQL question
 
 
 Good morning list,
 
 Environment HP-UX 11.0 Oracle 8.1.6
 
 Can anyone help with this SQL.
 
 I can get a result set of values from a table
 that match a given list of values -
 
 select code
 from table
 where code in ('A','B','C','D','E')
 
 I can get a result set of values from a table
 that do not match a given list of values -
 
 select code
 from table
 where code not in ('A','B','C','D','E')
 
 So far so good.
 
 Now, how do I get the set of values from the
 list
 that do NOT have a matching value in the table?
 
 I cannot create any objects in the schema I am
 working in otherwise I would create a table
 with
 the values and do a minus, but I can't figure
 out
 how to do it in SQL only.
 
 Thanks in advance, folks.
 
 Steve
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.com
 -- 
 Author: Steven Haas
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com
 San Diego, California-- Mailing list
 and web hosting services

-
 To REMOVE yourself from this mailing list, send
 an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling
 of 'ListGuru') and in
 the message BODY, include a line containing:
 UNSUB ORACLE-L
 (or the name of mailing list you want to be
 removed from).  You may
 also send the HELP command for other
 information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Haas
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question

2002-09-23 Thread Steven Haas


Dan et al,

I guess that is where I may head with it now.  I
couldn't come up with an easier way.

Thanks all...

steve



--- Fink, Dan [EMAIL PROTECTED] wrote:
 1700 values? I sure hope you like to type...
 
 Could you create a text file of the table
 values and compare those against a
 text files of the possible values? This would
 require O/S level privs.
 
 
 -Original Message-
 From: Steven Haas [mailto:[EMAIL PROTECTED]]
 Sent: Monday, September 23, 2002 11:38 AM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: SQL question
 
 
 Dan (and Charlie),
 
 Thanks.
 Good suggestions, but the IN clause contains
 just
 over 1700 values.
 
 Puzzling, huh?
 
 steve
 
 
 --- Fink, Dan [EMAIL PROTECTED] wrote:
  It is a little awkward, but a union in an
  inline query may do the trick:
  
1  select a.code
2  from (select '10' code from dual union
3select '20' code from dual union
4select '30' code from dual union
5select '40' code from dual union
6select '50' code from dual ) a
7* where a.code not in (select
  to_char(deptno) from emp)
  SQL /
  
  CO
  --
  40
  50
  
  Dan Fink
  
  -Original Message-
  From: Steven Haas
 [mailto:[EMAIL PROTECTED]]
  Sent: Monday, September 23, 2002 10:28 AM
  To: Multiple recipients of list ORACLE-L
  Subject: SQL question
  
  
  Good morning list,
  
  Environment HP-UX 11.0 Oracle 8.1.6
  
  Can anyone help with this SQL.
  
  I can get a result set of values from a table
  that match a given list of values -
  
  select code
  from table
  where code in ('A','B','C','D','E')
  
  I can get a result set of values from a table
  that do not match a given list of values -
  
  select code
  from table
  where code not in ('A','B','C','D','E')
  
  So far so good.
  
  Now, how do I get the set of values from the
  list
  that do NOT have a matching value in the
 table?
  
  I cannot create any objects in the schema I
 am
  working in otherwise I would create a table
  with
  the values and do a minus, but I can't figure
  out
  how to do it in SQL only.
  
  Thanks in advance, folks.
  
  Steve
  -- 
  Please see the official ORACLE-L FAQ:
  http://www.orafaq.com
  -- 
  Author: Steven Haas
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- 858-538-5051
  http://www.fatcity.com
  San Diego, California-- Mailing list
  and web hosting services
 

-
  To REMOVE yourself from this mailing list,
 send
  an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling
  of 'ListGuru') and in
  the message BODY, include a line containing:
  UNSUB ORACLE-L
  (or the name of mailing list you want to be
  removed from).  You may
  also send the HELP command for other
  information (like subscribing).
 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.com
 -- 
 Author: Steven Haas
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com
 San Diego, California-- Mailing list
 and web hosting services

-
 To REMOVE yourself from this mailing list, send
 an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling
 of 'ListGuru') and in
 the message BODY, include a line containing:
 UNSUB ORACLE-L
 (or the name of mailing list you want to be
 removed from).  You may
 also send the HELP command for other
 information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Haas
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question

2002-09-23 Thread Jamadagni, Rajendra

Title: RE: SQL question





select *
 from(select 'a' from dual union select 'b' from dual union select 'c' from dual ...)
minus
select distinct code
from table
/


HTH
Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-
From: Steven Haas [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 12:28 PM
To: Multiple recipients of list ORACLE-L
Subject: SQL question



Good morning list,


Environment HP-UX 11.0 Oracle 8.1.6


Can anyone help with this SQL.


I can get a result set of values from a table
that match a given list of values -


select code
from table
where code in ('A','B','C','D','E')


I can get a result set of values from a table
that do not match a given list of values -


select code
from table
where code not in ('A','B','C','D','E')


So far so good.


Now, how do I get the set of values from the list
that do NOT have a matching value in the table?


I cannot create any objects in the schema I am
working in otherwise I would create a table with
the values and do a minus, but I can't figure out
how to do it in SQL only.


Thanks in advance, folks.


Steve
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Haas
 INET: [EMAIL PROTECTED]


Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).




*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

Re: SQL question

2002-09-23 Thread Igor Neyman


Jonathan Gennick has an excellent article in Oracle magazine (sept./oct.),
which should help.
He demonstrates two approaches: with and without pivot table.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, September 23, 2002 1:28 PM


 It is a little awkward, but a union in an inline query may do the trick:

   1  select a.code
   2  from (select '10' code from dual union
   3select '20' code from dual union
   4select '30' code from dual union
   5select '40' code from dual union
   6select '50' code from dual ) a
   7* where a.code not in (select to_char(deptno) from emp)
 SQL /

 CO
 --
 40
 50

 Dan Fink

 -Original Message-
 Sent: Monday, September 23, 2002 10:28 AM
 To: Multiple recipients of list ORACLE-L


 Good morning list,

 Environment HP-UX 11.0 Oracle 8.1.6

 Can anyone help with this SQL.

 I can get a result set of values from a table
 that match a given list of values -

 select code
 from table
 where code in ('A','B','C','D','E')

 I can get a result set of values from a table
 that do not match a given list of values -

 select code
 from table
 where code not in ('A','B','C','D','E')

 So far so good.

 Now, how do I get the set of values from the list
 that do NOT have a matching value in the table?

 I cannot create any objects in the schema I am
 working in otherwise I would create a table with
 the values and do a minus, but I can't figure out
 how to do it in SQL only.

 Thanks in advance, folks.

 Steve
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: Steven Haas
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: Fink, Dan
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Igor Neyman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question

2002-09-23 Thread Jamadagni, Rajendra

Title: RE: SQL question





Steve,


select 'select a.code ' || chr(10) || ' from(' ||
from dual
union
select distinct 'select ' ||  || code ||  || ' code from dual ' || chr(10) || 'union' ||
 from my_code_table 
union
select ')' || chr(10) || 'minus' from dual
/
select 'select distinct code from my_table' || chr(10) || '/'
/



drop off the last union before the closing parenthesis ...


See ... now you don't have to type ...


Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-
From: Steven Haas [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 1:38 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: SQL question



Dan (and Charlie),


Thanks.
Good suggestions, but the IN clause contains just
over 1700 values.


Puzzling, huh?


steve




This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*2

RE: SQL question

2002-09-23 Thread Steven Haas


Raj,

It wasn't a question of coding the sql to create
all of the unions, but hitting a limitation on
the number of unions in the sql.

But, thanks to all for the effort.

steve

--- Jamadagni, Rajendra
[EMAIL PROTECTED] wrote:
 Steve,
 
 select 'select a.code ' || chr(10) || ' from('
 ||
 from dual
 union
 select distinct 'select ' ||  || code ||
  || ' code from dual ' ||
 chr(10) || 'union' ||
   from my_code_table 
 union
 select ')' || chr(10) || 'minus' from dual
 /
 select 'select distinct code from my_table' ||
 chr(10) || '/'
 /
 
 
 drop off the last union before the closing
 parenthesis ...
 
 See ... now you don't have to type ...
 
 Raj

__
 Rajendra JamadagniMIS, ESPN Inc.
 Rajendra dot Jamadagni at ESPN dot com
 Any opinion expressed here is personal and
 doesn't reflect that of ESPN Inc.
 
 QOTD: Any clod can have facts, but having an
 opinion is an art!
 
 
 -Original Message-
 From: Steven Haas [mailto:[EMAIL PROTECTED]]
 Sent: Monday, September 23, 2002 1:38 PM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: SQL question
 
 
 Dan (and Charlie),
 
 Thanks.
 Good suggestions, but the IN clause contains
 just
 over 1700 values.
 
 Puzzling, huh?
 
 steve
 
This
 e-mail message is confidential, intended only
 for the named recipient(s) above and may
 contain information that is privileged,
 attorney work product or exempt from disclosure
 under applicable law. If you have received this
 message in error, or are not the named
 recipient(s), please immediately notify
 corporate MIS at (860) 766-2000 and delete this
 e-mail message from your computer, Thank

you.*2
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Haas
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL question

2002-09-23 Thread Igor Neyman


Sorry, forgot to provide a link:

http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, September 23, 2002 2:33 PM


 Jonathan Gennick has an excellent article in Oracle magazine
(sept./oct.),
 which should help.
 He demonstrates two approaches: with and without pivot table.

 Igor Neyman, OCP DBA
 [EMAIL PROTECTED]



 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Monday, September 23, 2002 1:28 PM


  It is a little awkward, but a union in an inline query may do the trick:
 
1  select a.code
2  from (select '10' code from dual union
3select '20' code from dual union
4select '30' code from dual union
5select '40' code from dual union
6select '50' code from dual ) a
7* where a.code not in (select to_char(deptno) from emp)
  SQL /
 
  CO
  --
  40
  50
 
  Dan Fink
 
  -Original Message-
  Sent: Monday, September 23, 2002 10:28 AM
  To: Multiple recipients of list ORACLE-L
 
 
  Good morning list,
 
  Environment HP-UX 11.0 Oracle 8.1.6
 
  Can anyone help with this SQL.
 
  I can get a result set of values from a table
  that match a given list of values -
 
  select code
  from table
  where code in ('A','B','C','D','E')
 
  I can get a result set of values from a table
  that do not match a given list of values -
 
  select code
  from table
  where code not in ('A','B','C','D','E')
 
  So far so good.
 
  Now, how do I get the set of values from the list
  that do NOT have a matching value in the table?
 
  I cannot create any objects in the schema I am
  working in otherwise I would create a table with
  the values and do a minus, but I can't figure out
  how to do it in SQL only.
 
  Thanks in advance, folks.
 
  Steve
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Steven Haas
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting services
  -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Fink, Dan
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting services
  -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from).  You may
  also send the HELP command for other information (like subscribing).

 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: Igor Neyman
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Igor Neyman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question

2002-09-23 Thread Nicoll, Iain \(Calanais\)


what are the 1700 values

if the are all alphabetic and not too long you could do something like the
below though it's all getting a bit long-winded


select
chr(65+(floor((rownum-1)/676)))||chr(65+(floor((mod(rownum-1,676))/26)))||ch
r(65+(mod(rownum-1,26)))
from addresses -- any table big enough
where rownum  26*26*26
group by
chr(65+(floor((rownum-1)/676)))||chr(65+(floor((mod(rownum-1,676))/26)))||ch
r(65+(mod(rownum-1,26)))
having
chr(65+(floor((rownum-1)/676)))||chr(65+(floor((mod(rownum-1,676))/26)))||ch
r(65+(mod(rownum-1,26))) in 
  ('ABA','ACY','ABT'...)  -- the 1700 values
minus
select code 
from table



-Original Message-
Sent: Monday, September 23, 2002 5:28 PM
To: Multiple recipients of list ORACLE-L


Good morning list,

Environment HP-UX 11.0 Oracle 8.1.6

Can anyone help with this SQL.

I can get a result set of values from a table
that match a given list of values -

select code
from table
where code in ('A','B','C','D','E')

I can get a result set of values from a table
that do not match a given list of values -

select code
from table
where code not in ('A','B','C','D','E')

So far so good.

Now, how do I get the set of values from the list
that do NOT have a matching value in the table?

I cannot create any objects in the schema I am
working in otherwise I would create a table with
the values and do a minus, but I can't figure out
how to do it in SQL only.

Thanks in advance, folks.

Steve
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Haas
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain \(Calanais\)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL question

2002-09-23 Thread Jamadagni, Rajendra

Title: RE: SQL question





Maybe I think differently, I usually let server think about size or the number of clauses ...


if you have codes in a table what's wrong with ...


select distinct code
from my_code_table
minus
select distinct code
 from my_data_table
/


???
Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-
From: Steven Haas [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 23, 2002 2:43 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: SQL question



Raj,


It wasn't a question of coding the sql to create
all of the unions, but hitting a limitation on
the number of unions in the sql.


But, thanks to all for the effort.


steve




This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*2

Re: SQL Question (DISREGARD 1ST MESSAGE, SORRY)

2002-06-08 Thread Stephane Faroult


Viktor wrote:
 
 Hello All,
 
 It looks as if I've hit a brick wall and I'd very much
 appreciate if you can help.
 
 desc Names
 
 FIRST_INIT  NOT NULL CHAR(4)
 SECOND_INIT NOT NULL CHAR(1)
 INIT_SEQUENCE   NOT NULL NUMBER
 LAST_NAMEVARCHAR2(30)
 FIRST_NAME   VARCHAR2(20)
 FLAGNOT NULL NUMBER
 
 desc MEMBER
 
 MEM_FIRST_INIT   CHAR(4)
 MEM_SECOND_INIT  CHAR(1)
 MEM_INIT_SEQUENCENUMBER
 
 Member table references Names table on FIRST_INIT,
 SECOND_INIT, INIT_SEQUENCE (FOREIGN KEYS).
 
 Names table has NOT NULL column flag. It can only be 0
 or 1 -- means Name is a member.
 
 But, not all NAME records with FLAG 1 are in MEMBER.
 In other words, records in MEMBER usually represent
 other some other types of memership.
 
 But, in this case, I need to get those NAME(parent)
 records that have FLAG = 1, and those MEMBER(child)
 records that reference NAMES via foreign keys.
 Sometimes NAME record with FLAG = 1, also has a MEMBER
 record, and it could be that record with FLAG = 1 does
 not have a record in MEMBER.
 
 I need all those with FLAG = 1 in NAMES + all records
 that are in MEMBER.
 
 Is there another way besides the UNION:
 
 SELECT a.first_init||a.second_init||a.init_sequence
 INITIALS,
a.last_name LAST_NAME,
a.first_name FIRST_NAME,
a.flag MEMBER_FLAG,
 FROM names a
 WHERE a.advisor_flag = 1
 UNION
 SELECT a.first_init||a.second_init||a.init_sequence
 INITIALS,
a.last_name LAST_NAME,
a.first_name FIRST_NAME,
a.flag MEMBER_FLAG
 FROM names a,
  members m
 WHERE a.first_init = m.mem_first_init
  and a.second_init = m.mem_second_init
  and a.init_sequence = m.mem_init_sequence
 
 Thanks a lot in advance!
 
 Regards,
 
 Viktor
 

The UNION is perfect. You could possibly do something with external
joins and an 'OR', but it would not be more legible.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL Question

2002-06-07 Thread Mladen Gogala


Are you looking for something trivial like:

select n.first_name, 'E.' middle_initial,n.last_name,m.mem_init_sequence
from   names n, member m
where  n.first_init=m.mem_first_init and
n.second_init=m.mem_second_init
order by 1 desc, 2 asc;



On 2002.06.08 01:33 Viktor wrote:
 Hello All,
 
 It looks as if I've hit a brick wall and I'd very much
 appreciate if you can help. I've got a query that has
 to get some names and tie them to members. Name table
 is the main table with and member table is child
 table.
 
 desc Names
 
 FIRST_INIT  NOT NULL CHAR(4)
 SECOND_INIT NOT NULL CHAR(1)
 INIT_SEQUENCE   NOT NULL NUMBER
 LAST_NAMEVARCHAR2(30)
 FIRST_NAME   VARCHAR2(20)
 FLAGNOT NULL NUMBER
 
 desc MEMBER
 
 MEM_FIRST_INIT   CHAR(4)
 MEM_SECOND_INIT  CHAR(1)
 MEM_INIT_SEQUENCENUMBER
 
 Member table references Names table on FIRST_INIT,
 SECOND_INIT, INIT_SEQUENCE.
 
 
 
 
 
 __
 Do You Yahoo!?
 Yahoo! - Official partner of 2002 FIFA World Cup
 http://fifaworldcup.yahoo.com
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: Viktor
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 

-- 
Mladen Gogala
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mladen Gogala
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question

2002-05-31 Thread Kevin Lange


Try this

select a.f1, a.d1, a.d2
from
  (select field1 f1,date1 d1,max(date2) d2 from temp group by field1,date1)
a,
  (select field1 f1,date1 d1,max(date2) d2 from temp group by field1,date1)
b
where a.f1 = b.f1
and a.d1  b.d1
and a.d2  b.d2


-Original Message-
Sent: Friday, May 31, 2002 10:59 AM
To: Multiple recipients of list ORACLE-L


Hi,

I have a table with 1 field and 2 dates: field1, date1, date2. I need to
find the max value of date2 for all the field1, date1 combinations. Then I
want to join the table to itself on field1 and find all the rows where
field1 matches, date1  date1, and max(date2)  max(date2). I did this in 2
queries. First I created a view as follows:

create view v1 (f1, d1, d2)
as select field1,date1,max(date2) from table1
group by field1,date1;

Then I joined the 2 views together like this:

select a.f1,a.d1,a.d2 from v1 a, v1 b
where a.f1 = b.f1
and a.d1  b.d1
and a.d2  b.d2;

This worked fine, but I was wondering if there was a way to do this in
one query without having to create a view.


Bill Carle
ATT
Database Administrator
816-995-3922
[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Carle, William T (Bill), ALCAS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kevin Lange
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question

2002-05-30 Thread Connor McDonald


I'm sure you're already aware of this, but the
substr/instr is not as complicated as it looks since
instr takes 4 parms, the 4th of which makes cycling
through fields 1=8 easy.

hth
connor

 --- Deshpande, Kirti [EMAIL PROTECTED]
wrote:  Stephane,
  Thanks. 
  Nice idea :) 
  I will pass on this idea to them... Hope it flies..
 
 
  Looks like either a function or a view around the
 'ugly' code is the only
 choice. 
 
 - Kirti 
  
 
 
 -Original Message-
 Sent: Wednesday, May 29, 2002 3:32 PM
 To: Multiple recipients of list ORACLE-L
 
 Snip
 
 
 Kirti,
 
I had a vague remembrance of something which
 might have been helpful
 in dbms_utility but the closer you get to is a
 comma_to_table()
 procedure. Even without your reluctance to
 REPLACE(), you would be lucky
 if your data could bear this kind of transformation.
   Well, its a bit like sweeping dirt under the
 carpet, but I think that
 a function
  getfield(string in varchar2, pos in
 number, separator
 in char)
  return varchar2;
  full of substr and instr should make the upper
 level query more legible
 ...
 
  
 Regards,
 
 Stephane Faroult
 Oriole Software
 -- 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.com
 -- 
 Author: Deshpande, Kirti
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX:
 (858) 538-5051
 San Diego, California-- Public Internet
 access / Mailing Lists


 To REMOVE yourself from this mailing list, send an
 E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of
 'ListGuru') and in
 the message BODY, include a line containing: UNSUB
 ORACLE-L
 (or the name of mailing list you want to be removed
 from).  You may
 also send the HELP command for other information
 (like subscribing). 

=
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

Some days you're the pigeon, some days you're the statue

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question

2002-05-30 Thread Deshpande, Kirti


Hi Conner,
 Yes, I agree.
 But its the 'green bean' developers that I am dealing with :) 

 Regards,

- Kirti 

PS : Your BCHR enhancer code is coming extremely handy :)  Great Job, you
did !!  


-Original Message-
Sent: Thursday, May 30, 2002 4:23 AM
To: Multiple recipients of list ORACLE-L


I'm sure you're already aware of this, but the
substr/instr is not as complicated as it looks since
instr takes 4 parms, the 4th of which makes cycling
through fields 1=8 easy.

hth
connor

 --- Deshpande, Kirti [EMAIL PROTECTED]
wrote:  Stephane,
  Thanks. 
  Nice idea :) 
  I will pass on this idea to them... Hope it flies..
 
 
  Looks like either a function or a view around the
 'ugly' code is the only
 choice. 
 
 - Kirti 
  
 
 
 -
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question

2002-05-29 Thread Nicoll, Iain (Calanais)


What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question


Thanks. 
Substr/instr was rejected because it was a bit difficult to read the code. 
 
Also, they wanted to extract the fields in their own column headings (new
requirement). So 'replace' may not fly much !! 

- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 1:45 PM
To: Multiple recipients of list ORACLE-L


What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question

2002-05-29 Thread Karniotis, Stephen


Kirti:

  If this was Cobol, you could implement a record with a varying size
REDEFINES clause.  Using Iain's idea, you could change the separator string
and then redefine into a record.

  What about defining a type with the tilde as the separator?

Thank You

Stephen P. Karniotis
Product Architect
Compuware Corporation
Direct: (248) 865-4350
Mobile: (248) 408-2918
Email:  [EMAIL PROTECTED]
Web:www.compuware.com

 -Original Message-
Sent:   Wednesday, May 29, 2002 2:45 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: SQL Question

What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it. 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Karniotis, Stephen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question

2002-05-29 Thread DENNIS WILLIAMS


Kirti - We have a denormalized table like this in one database. An excellent
moral lesson for those who doubt the wisdom of normalization. 
My first choice would be to lobby to redesign this table. The longer
it remains and the more programs are built around this design, the more
painful the eventual redesign will be. 
My second choice would be something along the lines of a shadow
table to move all the records periodically for reporting. If you need to
search on these fields, you could create a function-based index on each
column. One idea that occurs to me that I haven't tested would be whether
you could write a stored procedure that would parse the field and return the
columns. This would be usable by any utility that could execute a PL/SQL
procedure. 
Kirti - you have my sympathy.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 29, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L


Thanks. 
Substr/instr was rejected because it was a bit difficult to read the code. 
 
Also, they wanted to extract the fields in their own column headings (new
requirement). So 'replace' may not fly much !! 

- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 1:45 PM
To: Multiple recipients of list ORACLE-L


What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question


Steve:
 I (too) miss COBOL when it comes to something like this!!! 

 I will let the Developers of the 'type' solution. It's good that it is an
8i database :)

 Thanks.
- Kirti

-Original Message-
Sent: Wednesday, May 29, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L


Kirti:

  If this was Cobol, you could implement a record with a varying size
REDEFINES clause.  Using Iain's idea, you could change the separator string
and then redefine into a record.

  What about defining a type with the tilde as the separator?

Thank You

Stephen P. Karniotis
Product Architect
Compuware Corporation
Direct: (248) 865-4350
Mobile: (248) 408-2918
Email:  [EMAIL PROTECTED]
Web:www.compuware.com

 -Original Message-
Sent:   Wednesday, May 29, 2002 2:45 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: SQL Question

What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it. 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Karniotis, Stephen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question


Dennis,

Thanks for the ideas, but... 

1. Not going to happen. It's a production system already in place (Vendor
designed? But, of course!!)

2. See above. 

This is what happens when someone decides to write their own reports against
tables that were not designed by themselves. Damagement thinks it is just a
simple SELECT !!! 

Looks like they just will have to live with unreadable SQL ! 

Thanks. 
- Kirti

-Original Message-
Sent: Wednesday, May 29, 2002 2:30 PM
To: Multiple recipients of list ORACLE-L


Kirti - We have a denormalized table like this in one database. An excellent
moral lesson for those who doubt the wisdom of normalization. 
My first choice would be to lobby to redesign this table. The longer
it remains and the more programs are built around this design, the more
painful the eventual redesign will be. 
My second choice would be something along the lines of a shadow
table to move all the records periodically for reporting. If you need to
search on these fields, you could create a function-based index on each
column. One idea that occurs to me that I haven't tested would be whether
you could write a stored procedure that would parse the field and return the
columns. This would be usable by any utility that could execute a PL/SQL
procedure. 
Kirti - you have my sympathy.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 29, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L


Thanks. 
Substr/instr was rejected because it was a bit difficult to read the code. 
 
Also, they wanted to extract the fields in their own column headings (new
requirement). So 'replace' may not fly much !! 

- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 1:45 PM
To: Multiple recipients of list ORACLE-L


What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line

RE: SQL Question

2002-05-29 Thread Rachel Carmichael


if you are going to use a shadow table, how about a trigger on the
original table that parses the field into separate columns and does an
insert into the shadow table? Update if necessary (not all that
difficult, just replace all the parsed fields in case) and delete,
depending on the types of dml done on the original table.

not elegant but it would give you the normalized table.


--- DENNIS WILLIAMS [EMAIL PROTECTED] wrote:
 Kirti - We have a denormalized table like this in one database. An
 excellent
 moral lesson for those who doubt the wisdom of normalization. 
   My first choice would be to lobby to redesign this table. The longer
 it remains and the more programs are built around this design, the
 more
 painful the eventual redesign will be. 
   My second choice would be something along the lines of a shadow
 table to move all the records periodically for reporting. If you need
 to
 search on these fields, you could create a function-based index on
 each
 column. One idea that occurs to me that I haven't tested would be
 whether
 you could write a stored procedure that would parse the field and
 return the
 columns. This would be usable by any utility that could execute a
 PL/SQL
 procedure. 
   Kirti - you have my sympathy.
 Dennis Williams
 DBA
 Lifetouch, Inc.
 [EMAIL PROTECTED]
 
 
 -Original Message-
 Sent: Wednesday, May 29, 2002 2:09 PM
 To: Multiple recipients of list ORACLE-L
 
 
 Thanks. 
 Substr/instr was rejected because it was a bit difficult to read the
 code. 
  
 Also, they wanted to extract the fields in their own column headings
 (new
 requirement). So 'replace' may not fly much !! 
 
 - Kirti 
 
 -Original Message-
 Sent: Wednesday, May 29, 2002 1:45 PM
 To: Multiple recipients of list ORACLE-L
 
 
 What about
 
 select commission_id, replace(com_text_msg,'~',chr(9))
 from tab1
 
 which would work if going to a tab separated file for something like
 excel.
 
 
 Whats wrong with substr/instr?
 
 Iain Nicoll
 
 -Original Message-
 Sent: Wednesday, May 29, 2002 7:22 PM
 To: Multiple recipients of list ORACLE-L
 
 
 I need some help... 
 
 The database table has following structure.
 commision_id  number
 com_text_msg  varchar2(500)
 
 The second column contains data fields that are delimited by ~ and
 delimiter's position varies. But there are only eight data fields in
 the
 column.  
 
 Is there a way in SQL, other than substr/instr combinations, to
 extract each
 data field to report? 
 
 Thanks.
 
 - Kirti 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: Deshpande, Kirti
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing
 Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: Nicoll, Iain (Calanais)
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing
 Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: Deshpande, Kirti
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing
 Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 -- 
 Author: DENNIS WILLIAMS
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing
 Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of

RE: SQL Question

2002-05-29 Thread Bob Metelsky


An oversimplification no doubt... But what about creating a snapshot
table for reporting?
It would be much less painfull then revisiting the column names every
time a report is requested.

Now, getting a spec of reporting fields can be a challenge it its own
right but... The snapshots do work

Bob


 Dennis,
 
 Thanks for the ideas, but... 
 
 1. Not going to happen. It's a production system already in 
 place (Vendor designed? But, of course!!)
 
 2. See above. 
 
 This is what happens when someone decides to write their own 
 reports against tables that were not designed by themselves. 
 Damagement thinks it is just a simple SELECT !!! 
 
 Looks like they just will have to live with unreadable SQL ! 
 
 Thanks. 
 - Kirti
 
 -Original Message-
 Sent: Wednesday, May 29, 2002 2:30 PM
 To: Multiple recipients of list ORACLE-L
 
 
 Kirti - We have a denormalized table like this in one 
 database. An excellent moral lesson for those who doubt the 
 wisdom of normalization. 
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bob Metelsky
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL Question

2002-05-29 Thread Stephane Faroult


Deshpande, Kirti wrote:
 
 Thanks.
 Substr/instr was rejected because it was a bit difficult to read the code.
 
 Also, they wanted to extract the fields in their own column headings (new
 requirement). So 'replace' may not fly much !!
 
 - Kirti
 
 -Original Message-
 Sent: Wednesday, May 29, 2002 1:45 PM
 To: Multiple recipients of list ORACLE-L
 
 What about
 
 select commission_id, replace(com_text_msg,'~',chr(9))
 from tab1
 
 which would work if going to a tab separated file for something like excel.
 
 Whats wrong with substr/instr?
 
 Iain Nicoll
 
 -Original Message-
 Sent: Wednesday, May 29, 2002 7:22 PM
 To: Multiple recipients of list ORACLE-L
 
 I need some help...
 
 The database table has following structure.
 commision_id  number
 com_text_msg  varchar2(500)
 
 The second column contains data fields that are delimited by ~ and
 delimiter's position varies. But there are only eight data fields in the
 column.
 
 Is there a way in SQL, other than substr/instr combinations, to extract each
 data field to report?
 
 Thanks.
 
 - Kirti
 

Kirti,

   I had a vague remembrance of something which might have been helpful
in dbms_utility but the closer you get to is a comma_to_table()
procedure. Even without your reluctance to REPLACE(), you would be lucky
if your data could bear this kind of transformation.
  Well, its a bit like sweeping dirt under the carpet, but I think that
a function
 getfield(string in varchar2, pos in number, separator
in char)
 return varchar2;
 full of substr and instr should make the upper level query more legible
...

 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question

2002-05-29 Thread Terrian, Tom




Would they allow you to create a view with substr/instr and then just code off
of the view?

-Original Message-
Sent: Wednesday, May 29, 2002 3:30 PM
To: Multiple recipients of list ORACLE-L

Kirti - We have a denormalized table like this in one database. An excellent
moral lesson for those who doubt the wisdom of normalization. 
My first choice would be to lobby to redesign this table. The longer
it remains and the more programs are built around this design, the more
painful the eventual redesign will be. 
My second choice would be something along the lines of a shadow
table to move all the records periodically for reporting. If you need to
search on these fields, you could create a function-based index on each
column. One idea that occurs to me that I haven't tested would be whether
you could write a stored procedure that would parse the field and return the
columns. This would be usable by any utility that could execute a PL/SQL
procedure. 
Kirti - you have my sympathy.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 29, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L


Thanks. 
Substr/instr was rejected because it was a bit difficult to read the code. 
 
Also, they wanted to extract the fields in their own column headings (new
requirement). So 'replace' may not fly much !! 

- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 1:45 PM
To: Multiple recipients of list ORACLE-L


What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Terrian, Tom
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858)

Re: SQL Question

2002-05-29 Thread Igor Neyman


What about creating a view and hiding 'unreadable SQL' in view definition,
and granting 'select on' view instead of table.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]


- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, May 29, 2002 4:00 PM


 Dennis,

 Thanks for the ideas, but...

 1. Not going to happen. It's a production system already in place (Vendor
 designed? But, of course!!)

 2. See above.

 This is what happens when someone decides to write their own reports
against
 tables that were not designed by themselves. Damagement thinks it is just
a
 simple SELECT !!!

 Looks like they just will have to live with unreadable SQL !

 Thanks.
 - Kirti

 -Original Message-
 Sent: Wednesday, May 29, 2002 2:30 PM
 To: Multiple recipients of list ORACLE-L


 Kirti - We have a denormalized table like this in one database. An
excellent
 moral lesson for those who doubt the wisdom of normalization.
 My first choice would be to lobby to redesign this table. The longer
 it remains and the more programs are built around this design, the more
 painful the eventual redesign will be.
 My second choice would be something along the lines of a shadow
 table to move all the records periodically for reporting. If you need to
 search on these fields, you could create a function-based index on each
 column. One idea that occurs to me that I haven't tested would be whether
 you could write a stored procedure that would parse the field and return
the
 columns. This would be usable by any utility that could execute a PL/SQL
 procedure.
 Kirti - you have my sympathy.
 Dennis Williams
 DBA
 Lifetouch, Inc.
 [EMAIL PROTECTED]


 -Original Message-
 Sent: Wednesday, May 29, 2002 2:09 PM
 To: Multiple recipients of list ORACLE-L


 Thanks.
 Substr/instr was rejected because it was a bit difficult to read the code.

 Also, they wanted to extract the fields in their own column headings (new
 requirement). So 'replace' may not fly much !!

 - Kirti

 -Original Message-
 Sent: Wednesday, May 29, 2002 1:45 PM
 To: Multiple recipients of list ORACLE-L


 What about

 select commission_id, replace(com_text_msg,'~',chr(9))
 from tab1

 which would work if going to a tab separated file for something like
excel.


 Whats wrong with substr/instr?

 Iain Nicoll

 -Original Message-
 Sent: Wednesday, May 29, 2002 7:22 PM
 To: Multiple recipients of list ORACLE-L


 I need some help...

 The database table has following structure.
 commision_id  number
 com_text_msg  varchar2(500)

 The second column contains data fields that are delimited by ~ and
 delimiter's position varies. But there are only eight data fields in the
 column.

 Is there a way in SQL, other than substr/instr combinations, to extract
each
 data field to report?

 Thanks.

 - Kirti

 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: Deshpande, Kirti
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: Nicoll, Iain (Calanais)
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: Deshpande, Kirti
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
 San Diego, California-- Public Internet access / Mailing Lists
 
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author: DENNIS WILLIAMS
   INET: [EMAIL PROTECTED]

 Fat City Network

RE: SQL Question


Another nice idea !
I will pass it on. 

Looks like instr/substr can not be avoided... :( 

Thanks.

- Kirti

-Original Message-
Sent: Wednesday, May 29, 2002 3:32 PM
To: Multiple recipients of list ORACLE-L




Would they allow you to create a view with substr/instr and then just code
off
of the view?

-Original Message-
Sent: Wednesday, May 29, 2002 3:30 PM
To: Multiple recipients of list ORACLE-L

Kirti - We have a denormalized table like this in one database. An excellent
moral lesson for those who doubt the wisdom of normalization. 
My first choice would be to lobby to redesign this table. The longer
it remains and the more programs are built around this design, the more
painful the eventual redesign will be. 
My second choice would be something along the lines of a shadow
table to move all the records periodically for reporting. If you need to
search on these fields, you could create a function-based index on each
column. One idea that occurs to me that I haven't tested would be whether
you could write a stored procedure that would parse the field and return the
columns. This would be usable by any utility that could execute a PL/SQL
procedure. 
Kirti - you have my sympathy.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 29, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L


Thanks. 
Substr/instr was rejected because it was a bit difficult to read the code. 
 
Also, they wanted to extract the fields in their own column headings (new
requirement). So 'replace' may not fly much !! 

- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 1:45 PM
To: Multiple recipients of list ORACLE-L


What about

select commission_id, replace(com_text_msg,'~',chr(9))
from tab1

which would work if going to a tab separated file for something like excel.


Whats wrong with substr/instr?

Iain Nicoll

-Original Message-
Sent: Wednesday, May 29, 2002 7:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also

RE: SQL Question


Stephane,
 Thanks. 
 Nice idea :) 
 I will pass on this idea to them... Hope it flies.. 

 Looks like either a function or a view around the 'ugly' code is the only
choice. 

- Kirti 
 


-Original Message-
Sent: Wednesday, May 29, 2002 3:32 PM
To: Multiple recipients of list ORACLE-L

Snip


Kirti,

   I had a vague remembrance of something which might have been helpful
in dbms_utility but the closer you get to is a comma_to_table()
procedure. Even without your reluctance to REPLACE(), you would be lucky
if your data could bear this kind of transformation.
  Well, its a bit like sweeping dirt under the carpet, but I think that
a function
 getfield(string in varchar2, pos in number, separator
in char)
 return varchar2;
 full of substr and instr should make the upper level query more legible
...

 
Regards,

Stephane Faroult
Oriole Software
-- 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question

2002-05-29 Thread Jamadagni, Rajendra


Stephane,

comma_to_table converts it to a pl/sql table.

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!



*2

This e-mail message is confidential, intended only for the named recipient(s) above 
and may contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank you.

*2

RE: SQL Question


Not sure if they would agree to snapshots, but I will suggest it anyway..

Thanks.

- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 3:26 PM
To: Multiple recipients of list ORACLE-L


An oversimplification no doubt... But what about creating a snapshot
table for reporting?
It would be much less painfull then revisiting the column names every
time a report is requested.

Now, getting a spec of reporting fields can be a challenge it its own
right but... The snapshots do work

Bob


 Dennis,
 
 Thanks for the ideas, but... 
 
 1. Not going to happen. It's a production system already in 
 place (Vendor designed? But, of course!!)
 
 2. See above. 
 
 This is what happens when someone decides to write their own 
 reports against tables that were not designed by themselves. 
 Damagement thinks it is just a simple SELECT !!! 
 
 Looks like they just will have to live with unreadable SQL ! 
 
 Thanks. 
 - Kirti
 
 -Original Message-
 Sent: Wednesday, May 29, 2002 2:30 PM
 To: Multiple recipients of list ORACLE-L
 
 
 Kirti - We have a denormalized table like this in one 
 database. An excellent moral lesson for those who doubt the 
 wisdom of normalization. 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bob Metelsky
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question

2002-05-29 Thread DENNIS WILLIAMS


Kirti - My guess is that this application was not developed on Oracle
originally. My experience is that sometimes these transplanted applications
don't scale well at the enterprise level. Depending on your organization's
goals, this may be an issue to raise, whether it will support the
anticipated future usage. Another idea is to ask the vendor how to do
reporting on this table (especially now that you have some good suggestions
to bounce back in case the vendor doesn't provide a good idea). Depending on
the politics at your organization, you can give the vendor a little static
about their bad design. Also, tell us the vendor's name so if someone
proposes that application at my site I can roll my eyes and say good grief,
not those people.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 29, 2002 1:22 PM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SQL Question

2002-05-29 Thread Peter . McLarty


Kirti 
I needed to do something similar but it isn't due to bad normalisation it 
is to extract data in a load process so we don't have bad normalisation. I 
decided to use Java and built a parser in that and I just feed it a line 
and the code simply extracts that data on the selected delimiter. Mine the 
delimiter is a fixed value in code, it could be altered to use a runtime 
selected value. I can provide the source if it would be of use. 

Cheers


--
=
Peter McLarty   E-mail: [EMAIL PROTECTED]
Technical ConsultantWWW: http://www.mincom.com
APAC Technical Services Phone: +61 (0)7 3303 3461
Brisbane,  AustraliaMobile: +61 (0)402 094 238
Facsimile: +61 (0)7 3303 3048
=
A great pleasure in life is doing what people say you cannot do.

- Walter Bagehot (1826-1877 British Economist)
=
Mincom The People, The Experience, The Vision

=

This transmission is for the intended addressee only and is confidential 
information. If you have received this transmission in error, please 
delete it and notify the sender. The contents of this e-mail are the 
opinion of the writer only and are not endorsed by the Mincom Group of 
companies unless expressly stated otherwise. 






Deshpande, Kirti [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
30-05-2002 04:22 AM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc: 
Fax to: 
Subject:SQL Question


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column. 

Is there a way in SQL, other than substr/instr combinations, to extract 
each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).





STG17766
Description: Binary data

RE: SQL Question

2002-05-29 Thread Johnson, Michael


Kirti,

my first thought and fwiw would be to write a PL/SQL routine.

Mike

-Original Message-
Sent: Wednesday, May 29, 2002 11:22 AM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johnson, Michael 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question


Mike,
 They were looking for a SQL solution first.
 Now a view (hiding substr/instr) looks like an acceptable thing :)

Thanks.

- Kirti 

-Original Message-
Sent: Wednesday, May 29, 2002 9:38 PM
To: Multiple recipients of list ORACLE-L


Kirti,

my first thought and fwiw would be to write a PL/SQL routine.

Mike

-Original Message-
Sent: Wednesday, May 29, 2002 11:22 AM
To: Multiple recipients of list ORACLE-L


I need some help... 

The database table has following structure.
commision_id  number
com_text_msg  varchar2(500)

The second column contains data fields that are delimited by ~ and
delimiter's position varies. But there are only eight data fields in the
column.  

Is there a way in SQL, other than substr/instr combinations, to extract each
data field to report? 

Thanks.

- Kirti 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johnson, Michael 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: SQL Question