OraFAQ Forum: SQL & PL/SQL » How to define range in where clause?

Home » SQL & PL/SQL » SQL & PL/SQL » How to define range in where clause? (Windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to define range in where clause? [message #613401]

Tue, 06 May 2014 06:49

indrarai
Messages: 2
Registered: May 2014
Location: Nepal

Junior Member

Dear All,
I want to define the range in where command like:

WHERE citizenship_cd='64-01-70-04592' to '64-01-70-04670'
then how can i define in sql command.

Please help.

Indra

Report message to a moderator

Re: How to define range in where clause? [message #613402 is a reply to message #613401]

Tue, 06 May 2014 06:52

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

See BETWEEN.

Report message to a moderator

Re: How to define range in where clause? [message #613403 is a reply to message #613401]

Tue, 06 May 2014 06:53

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

Have a look at BETWEEN Condition
[EDIT:] Didn't see Michel's post before submitting Smile

[Updated on: Tue, 06 May 2014 06:53]

Report message to a moderator

Re: How to define range in where clause? [message #613405 is a reply to message #613403]

Tue, 06 May 2014 07:02

indrarai
Messages: 2
Registered: May 2014
Location: Nepal

Junior Member

Thanks both of you. But I am still stuck. In following case, how to define "64-01-70-" which is static, only last five digits (04592) dynamic.

WHERE citizenship_cd='64-01-70-04592' to '64-01-70-04670'

Report message to a moderator

Re: How to define range in where clause? [message #613406 is a reply to message #613405]

Tue, 06 May 2014 07:14

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

YOu cannot have dynamic values in SQL. What are you trying to achieve. Please post a test case.

Report message to a moderator

Re: How to define range in where clause? [message #613410 is a reply to message #613406]

Tue, 06 May 2014 07:29

Maaher
Messages: 7065
Registered: December 2001

Senior Member

How does this work for you?

With yourtable as
( Select 1 col_1, '64-01-70-04590' citizenship_cd from dual union all -- rejected
  Select 2 col_1, '64-01-70-04592' citizenship_cd from dual union all
  Select 3 col_1, '64-01-70-04594' citizenship_cd from dual union all
  Select 4 col_1, '64-01-70-04596' citizenship_cd from dual union all
  Select 5 col_1, '64-01-70-04710' citizenship_cd from dual union all -- rejected
  Select 6 col_1, '64-01-70-04612' citizenship_cd from dual union all
  Select 7 col_1, '64-01-70-04814' citizenship_cd from dual union all -- rejected
  Select 8 col_1, '64-01-70-04616' citizenship_cd from dual
)
Select col_1
     , citizenship_cd
From   yourtable
Where substr(citizenship_cd, -5) between '04592' and '04670'
/

SUBSTR

MHE

Report message to a moderator

Re: How to define range in where clause? [message #613412 is a reply to message #613410]

Tue, 06 May 2014 07:39

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

@OP, You are dealing with a bad database design. You must first normalize it. Then querying the data would be much simpler and appropriate.

Report message to a moderator

Re: How to define range in where clause? [message #613413 is a reply to message #613410]

Tue, 06 May 2014 07:39

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Are you sure?

Your code will select '64-01-70-04592', '68-01-70-04592', '64-01-99-04592', etc. while OP wants '64-01-70-04592' to '64-01-70-04670'.

I'd say:

citizenship_cd between '64-01-70-04592' and '64-01-70-04670'

SY.
P.S. I assume citizenship_cd always has format XX-XX-XX-XXXXX where X is any digit.

[Updated on: Tue, 06 May 2014 07:41]

Report message to a moderator

Re: How to define range in where clause? [message #613414 is a reply to message #613413]

Tue, 06 May 2014 07:43

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Solomon Yakobson wrote on Tue, 06 May 2014 18:09

Your code will select '64-01-70-04592', '68-01-70-04592', '64-01-99-04592', etc.

OP mentioned that "64-01-70-" will not change :

indrarai wrote on Tue, 06 May 2014 17:32

"64-01-70-" which is static, only last five digits (04592) dynamic.

From design perspective, the data should be first normalized.

[Updated on: Tue, 06 May 2014 07:45]

Report message to a moderator

Re: How to define range in where clause? [message #613415 is a reply to message #613413]

Tue, 06 May 2014 07:43

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Fair point, Solomon. That will teach me to read a question properly. @Lalit: I have encountered enough examples of bad design throughout the years and in most cases there was no opportunity to change that.

MHE

[Updated on: Tue, 06 May 2014 07:44]

Report message to a moderator

Re: How to define range in where clause? [message #613416 is a reply to message #613414]

Tue, 06 May 2014 07:46

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

I am 99.9% sure OP meant starts with '64-01-70' with last 5 digits changing from 04592 to 04592.

SY.

[Updated on: Tue, 06 May 2014 07:46]

Report message to a moderator

Re: How to define range in where clause? [message #613417 is a reply to message #613413]

Tue, 06 May 2014 07:48

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Solomon Yakobson wrote on Tue, 06 May 2014 14:39

P.S. I assume citizenship_cd always has format XX-XX-XX-XXXXX where X is any digit.

And this is where things tend to get messy. First we need to establish a couple of facts:
1. is the assumption of Solomon correct? Is the layout the way he sees it?
2. when you say ""64-01-70-" which is static", what do you mean exactly? Is it always the same in the database and it won't change? Is Solomon Yakobson right (again) with his where clause? If that is the fact, my example won't do.

MHE

Report message to a moderator

Re: How to define range in where clause? [message #613418 is a reply to message #613416]

Tue, 06 May 2014 07:51

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Solomon Yakobson wrote on Tue, 06 May 2014 18:16

I am 99.9% sure OP meant starts with '64-01-70' with last 5 digits changing from 04592 to 04592.

Perhaps you are 100% correct. It's the way OP has explained in words - "static". I assume it is always same for all rows. I wish OP posted a test case.

Report message to a moderator

Re: How to define range in where clause? [message #613419 is a reply to message #613418]

Tue, 06 May 2014 07:54

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Well, it is possible but hard to believe ALL citizenship codes start with '64-01-70', but if they do storing '64-01-70' is the database is complete waste. Anyway, I think we dug in too deep into assumptions - we have to wait for OP for clear explanations.

SY.

Report message to a moderator

Re: How to define range in where clause? [message #613420 is a reply to message #613415]

Tue, 06 May 2014 07:56

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Maaher wrote on Tue, 06 May 2014 18:13

@Lalit: I have encountered enough examples of bad design throughout the years and in most cases there was no opportunity to change that.

I agree. Some, few, many, most of us have certainly encountered such examples. But at a later stage we end up with more issues and spend more time finding alternatives to fix it. It is a suggestion, rest depends on OP whether or not to implement it.

Report message to a moderator

Previous Topic:	ORA-00979: not a GROUP BY expression
Next Topic:	Date format in PL/SQL vs. PSP

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 11:29:10 CDT 2024