Home » SQL & PL/SQL » SQL & PL/SQL » select statement, default value
select statement, default value [message #187527] Mon, 14 August 2006 06:23 Go to next message
amitsarna
Messages: 6
Registered: August 2006
Junior Member
Hi,

I'd like to do a select statement where if a regular expression doesn't match i'd like a default value for one of the fields being selected instead of completely ignoring it.

e.g.
say my regular expression was X.Y, and table 1 had field A which matched up to field B on table 2 according to this regular expression, and returns field A together with some other field C from table 2. now if the expression doesn't match, i want to have a default value for field C returned in my select statement so the results would contain the non-matching field A items next to this default value.

How can this be done?

Cheers

Amit

Re: select statement, default value [message #187528 is a reply to message #187527] Mon, 14 August 2006 06:28 Go to previous messageGo to next message
asherisfine
Messages: 63
Registered: June 2006
Location: India
Member
hi amit

I don't think i could get a clue as to what your question is Embarassed sorry!!!

can you try putting up it in a different way, iam sure you will.
Re: select statement, default value [message #187530 is a reply to message #187527] Mon, 14 August 2006 06:31 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Without more details (ie. sample data & create scripts), I would offer up using REGEXP_INSTR inside a DECODE.
Re: select statement, default value [message #187532 is a reply to message #187528] Mon, 14 August 2006 06:37 Go to previous messageGo to next message
amitsarna
Messages: 6
Registered: August 2006
Junior Member
Hi,
I'll try and illustrate it with some actual data, i don't really know how to phrase it.

say one table contains (this is a made up example so makes no sense as a real world database):

regexp book
a.e PA
a.f QT

and the other table contains

join_field order
axe 1221
axf 1222
rrr 1223


so my query would use the regexp field in the first table to match to the join_field in the second table. This would give me the results:

book order
PA 1221
QT 1222


But i want the results to give me a default value of YZ as the book when the regular expression in table 1 doesn't match up with any of the join_fields in table 2. so my results would be

book order
PA 1221
QT 1222
YZ 1223


I know i could have one of the regular expressions as ... but this would match to everything (including patterns already matched) so i'd get multiple entries per order.

I hope this makes sense

Cheers

Amit







Re: select statement, default value [message #187533 is a reply to message #187532] Mon, 14 August 2006 06:40 Go to previous messageGo to next message
amitsarna
Messages: 6
Registered: August 2006
Junior Member
oh and in the post above the words above the data are the field name, i thought they would line up like in my post, didn't read the preview properly!
Re: select statement, default value [message #187568 is a reply to message #187532] Mon, 14 August 2006 08:55 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL>select * from reg1;

REGEXP		BOOK
--------------- ------
a.e		PA
a.f		QT

SQL>select * from reg2;

JOIN_FIELD	ORDER_NUM
--------------- ----------
axe		1221
axf		1222
rrr		1223


SQL>select nvl(book,'YZ') book, order_num
  2	from reg2 left outer join reg1
  3	on join_field like replace(regexp,'.','_');

BOOK		ORDER_NUM
--------------- ------------
PA			1221
QT			1222
YZ			1223


If you are on 10g:

SQL> select nvl(book,'YZ') book, order_num
 2	from reg2 left outer join reg1
 3	on regexp_like(reg2.join_field,reg1.regexp);

BOOK		ORDER_NUM
--------------- ------------
PA			1221
QT			1222
YZ			1223

[Updated on: Mon, 14 August 2006 09:21]

Report message to a moderator

Re: select statement, default value [message #187580 is a reply to message #187568] Mon, 14 August 2006 09:35 Go to previous message
amitsarna
Messages: 6
Registered: August 2006
Junior Member
Hi,

Thats exactly what i needed!

Cheers

Amit

P.S. sorry if my question was so badly phrased in the first place that you didn't know this is what i meant!
Previous Topic: TNS ERROR 12154 COULD NOT SOLVE SOMTHING
Next Topic: Reset Sequence
Goto Forum:
  


Current Time: Fri Dec 09 01:54:20 CST 2016

Total time taken to generate the page: 0.08601 seconds