RE: Need SELECT tips

From: Frederic Bidon <fbi_at_mobilix.dk>
Date: Fri, 4 Feb 2000 16:03:45 +0100
Message-ID: <2D3005375CAED31199D00008C784963F0209A1@ms02.mobilix.dk>

As far I understand :

1/ The easy way.
select col1,col2 FROM TEMP where col1 IN ('A','C','E');

That can be convenient if you don't have many relevant key values for col1.

2/ The nasty way.
If what you want is actually to skip one line from the base table every 2 lines and you have a high number or umpredicatable number of values, it is a bit more difficult :

CREATE OR REPLACE PACKAGE myfunc IS
FUNCTION isodd(col1 IN TEMP.col1%TYPE) RETURN INTEGER; PRAGMA RESTRICT_REFERENCES(isodd,WNDS); END myfunc;

CREATE OR REPLACE PACKAGE BODY myfunc IS priv_state INTEGER;
FUNCTION isodd(col1 IN TEMP.col1%TYPE) RETURN INTEGER IS BEGIN
IF priv_state = 0 THEN

	priv_state:=1;
	RETURN 1;
ELSE
	priv_state:=0;
	RETURN 0;

END IF;
END isodd;

BEGIN
priv_state:=0;
END myfunc;

select t1.col1,t1.col2 FROM

	(select  myfunc.isodd(col1) num,col1,col2 FROM TEMP) t1
	WHERE t1.num = 1;

You can of course imagine any kind of hash-like function which can yield a convenient code from you key value.

Enjoy !

Frederic

-----Original Message-----
From: Violin [mailto:violin.hsiao_at_mail.pouchen.com.tw] Sent: 28. januar 2000 06:19
To: comp.databases.oracle.server_at_list.deja.com Subject: Need SELECT tips

 Message from the Deja.com forum:
 comp.databases.oracle.server
 Your subscription is set to individual email delivery

Hello,
If I have a table : TEMP
COL1 CHAR(1) primary key
COL2 CHAR(1) col1 col2

---

  A        1
  B        1
  C        2
  D       2
  E       3
  F       3

How could I select the result?

col1 col2

---

  A        1
  C        2
  E        3

Thank you for any tips.

Violin.
violin.hsiao_at_mail.pouchen.com.tw

---

 Deja.com: Before you buy.
 http://www.deja.com/

• To modify or remove your subscription, go to http://www.deja.com/edit_sub.xp?group=comp.databases.oracle.server
• Read this thread at http://www.deja.com/thread/%3C3893f0c5.236359%40172.16.7.5%3E

 Sent via Deja.com http://www.deja.com/  Before you buy. Received on Fri Feb 04 2000 - 09:03:45 CST