OraFAQ Forum: SQL & PL/SQL » WHERE Clause With A Range

Home » SQL & PL/SQL » SQL & PL/SQL » WHERE Clause With A Range

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

WHERE Clause With A Range [message #660034]

Tue, 07 February 2017 15:24

jmltinc
Messages: 14
Registered: January 2016

Junior Member

Hi Folks,

I have a query to return a record dependent upon three conditions:

SELECT 
SEQ_PK
FROM LU_TM_PRODUCTS_MODEL LEFT JOIN 
TM_TEST_SEQUENCES ON 
LU_TM_PRODUCTS_MODEL.LUMOD_PK = 
TM_TEST_SEQUENCES.SEQ_MODEL
WHERE SEQ_ACTIVE=1
AND LUMOD_MODEL='AMP-4-150-30-00'
AND SEQ_REVISION='N'
AND SEQ_TEST_TYPE >0

Currently, all records have a unique Revision. I need to change the query so that one record can accommodate a range of Revisions - e.g. O-N - where O is the first (original) revision and N is the latest (with all others coming between).

My task is more complicated as the first Revision in the range may be O and not necessarily B, C, D, etc... Is there a way to use this as a range?

Thanks,
-John

[Updated on: Wed, 08 February 2017 01:01] by Moderator

Report message to a moderator

Re: WHERE Clause With A Range [message #660038 is a reply to message #660034]

Tue, 07 February 2017 15:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> I need to change the query so that one record can accommodate a range of Revisions - e.g. O-N
FWIW - Oracle tables do NOT have records. They have rows.
Please explain how 1 row can have more than 1 REVISION value.

post CREATE TABLE statements for tables involved.
post INSERT statements to populate tables with sample data

post expected & desired results from SQL & explain why these results are correct

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: WHERE Clause With A Range [message #660049 is a reply to message #660038]

Tue, 07 February 2017 22:59

Bill B
Messages: 1971
Registered: December 2004

Senior Member

And seq_revision between 'O' and 'N'

Report message to a moderator

Re: WHERE Clause With A Range [message #660050 is a reply to message #660049]

Tue, 07 February 2017 23:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Bill B wrote on Tue, 07 February 2017 20:59

And seq_revision between 'O' and 'N'

what letters are between 'O' & 'N'? (none)

Report message to a moderator

Re: WHERE Clause With A Range [message #660068 is a reply to message #660050]

Wed, 08 February 2017 05:15

jmltinc
Messages: 14
Registered: January 2016

Junior Member

I am sorry, I was not clear.

The table in question may have only one record (Row) that is revision-specific. Conversely, many revisions may share a single record (Row).

For records related to a specific Revision, the table field SEQ_REVISION is simply a single letter, say "C" and my posted query works (assuming "C" appears in the table). For those records which will serve many revisions, SEQ_REVISION will contain a revision range such as "A-G". Unfortunately, our original revision (when the product is rolled-out) is "O", next followed by "A", "B", "C",... So a Revision field may have "A-G" for all revisions A through G (excluding O and any revisions past G) or may have "O-G" for all revisions from roll-out through G. However, the variable returned to satisfy the condition of the WHERE clause is a single letter - say "C".

I guess the question is:
If the Revision field contains "O-G" and I am returning "C", what does the SQL look like?

Thanks,
-John

Report message to a moderator

Re: WHERE Clause With A Range [message #660070 is a reply to message #660068]

Wed, 08 February 2017 05:41

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

So does this column only ever contain either:
a) a single letter
b) a letter, a dash then another letter.

Is O the only letter that is out of alphabetic sequence?

Report message to a moderator

Re: WHERE Clause With A Range [message #660071 is a reply to message #660070]

Wed, 08 February 2017 06:35

jmltinc
Messages: 14
Registered: January 2016

Junior Member

cookiemonster wrote on Wed, 08 February 2017 05:41

So does this column only ever contain either:
a) a single letter
b) a letter, a dash then another letter.

Is O the only letter that is out of alphabetic sequence?

No, I did not want to muddy the waters...

To a) The column may have a letter followed by a number for a minor revision such as "C1". This would apply to ranges as well, so the SEQ_REVISION field could be "O-C1".

To b), there will always be a dash with no spaces. As explained above, the dash may not be followed by a letter as in "A2-C".

Yes, O is the only character that would be out of the sequence.

And currently, the table contains NULL for some Rows which would indicate the record can be used for any revision, but if the NULL presents a problem, it could be replaced with a range.

Thanks for the reply.
-John

[Updated on: Wed, 08 February 2017 06:37]

Report message to a moderator

Re: WHERE Clause With A Range [message #660072 is a reply to message #660071]

Wed, 08 February 2017 07:17

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Adding that information is part of explaining the requirement, not muddying the waters.

Looks like you want something along the lines of

seq_revision is null
or :value between substr(replace(seq_revision, 'O', '0'),1, instr(seq_revision, '-') - 1) and substr(replace(seq_revision, 'O', '0'), instr(seq_revision, '-') - +)

So replace turns the O into a zero which is alphabetically before all your other values. And then we split into two parts for the between. If there is no - then both parts will be the same so it'll be like
:value between A and A
which is equivalent to
:value = A

Report message to a moderator

Re: WHERE Clause With A Range [message #660080 is a reply to message #660072]

Wed, 08 February 2017 09:06

jmltinc
Messages: 14
Registered: January 2016

Junior Member

I am sorry, but a couple things...

I simplified my SQL and added your code, replacing the "+" at the end of the last INSTR function with 1:

SELECT 
SEQ_PK
FROM TM_TEST_SEQUENCES
WHERE
SEQ_REVISION is null or 
'A' between substr(replace(SEQ_REVISION, 'O', '0'),1, instr(SEQ_REVISION, '-')-1) and substr(replace(SEQ_REVISION,'O','0'),instr(SEQ_REVISION,'-') - 1)

However it returns a value only if SEQ_REVISION is NULL. If the field is "A" or "O-A", "O-N", "A-Z" it returns nothing.

I do not understand how the code will find a character that does not exist in the field (e.g. I have in SEQ_REVISION the value "O-N" and I query for "A". "A" does not appear in the field - only "O-N"...).

Thanks for your patience and help.

-John

Report message to a moderator

Re: WHERE Clause With A Range [message #660081 is a reply to message #660080]

Wed, 08 February 2017 09:22

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You spotted the typo but made the wrong fix.
2nd substr has to get everything after the '-'. So you use instr to get he position then add 1.

If it still doesn't work then I suggest you remove the substrs from the where clause and add them to the select clause so you can see what they evaluate to. Since I don't have your tables or data I'm not really in a position to do that.

As for not understanding how it'll find stuff that's not in the field - what do you think between does?

Report message to a moderator

Re: WHERE Clause With A Range [message #660088 is a reply to message #660081]

Wed, 08 February 2017 12:10

jmltinc
Messages: 14
Registered: January 2016

Junior Member

For testing, I am using a simple table with only two fields: SEQ_PK (Primary Key) and SEQ_REVISION. I added two records - one is a dummy and has SEQ_REVISION as "N" and the other (of interest) has a value "A"

The SQL is:

SELECT 
SEQ_PK
FROM TM_TEST_SEQUENCES
WHERE
SEQ_REVISION is null or 
'A' between substr(replace(SEQ_REVISION, 'O', '0'),1, instr(SEQ_REVISION, '-')-1) and 
substr(replace(SEQ_REVISION,'O','0'),instr(SEQ_REVISION,'-') + 1)

I return no record when SEQ_REVISION is "A".
I return a record for "O-A", "O-C", "A-C"..., and of course a record for NULL in that field.

Using

select substr(replace(SEQ_REVISION, 'O', '0'),1, instr(SEQ_REVISION, '-')-1)FROM TM_TEST_SEQUENCES

"A" returns NULL
"O-A" returns 0

Using

select substr(replace(SEQ_REVISION,'O','0'),instr(SEQ_REVISION,'-') + 1)FROM TM_TEST_SEQUENCES

"A" returns "A"
"O-A" returns "A"

To answer your question, I do understand Embarassed

Embarassed

Thanks,
-J

Report message to a moderator

Re: WHERE Clause With A Range [message #660089 is a reply to message #660088]

Wed, 08 February 2017 12:36

jmltinc
Messages: 14
Registered: January 2016

Junior Member

The quick fix was to add:

OR SEQ_REVISION='A'

This makes it work, but it is a good opportunity to wrap my head around your code. I think I get it, but need to stare a bit...

Thanks,
-J

Report message to a moderator

Re: WHERE Clause With A Range [message #660108 is a reply to message #660089]

Thu, 09 February 2017 03:00

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

If seq_revision is a single character then both should return that character. the first one doesn't because I forgot to allow for the instr returning 0.
This fixes that:
seq_revision between select substr(replace(SEQ_REVISION, 'O', '0'),1, decode(instr(SEQ_REVISION, '-'), 0, 1, instr(SEQ_REVISION, '-')-1))
and substr(replace(SEQ_REVISION,'O','0'),instr(SEQ_REVISION,'-') + 1)

Report message to a moderator

Re: WHERE Clause With A Range [message #660125 is a reply to message #660108]

Thu, 09 February 2017 04:41

jmltinc
Messages: 14
Registered: January 2016

Junior Member

I am learning here. I understand what you are doing with SUBSTR and INSTR. DECODE is a new concept to me. It dawned on me as I was testing code, I omitted what is a huge demand upon this WHERE clause. I wasn't going to 'go to the well' one more time and have been trying a work-around, but it requires a lot of OR operators to the WHERE clause. You asked me Quote:

So does this column only ever contain either:
a) a single letter
b) a letter, a dash then another letter.

I replied without thought to a few special circumstances which I expect make this code much harder:
Specifically, each Revision; 0,A,B,C... may have a minor revision (e.g. O1, O2, C1, C2...) in addition to the major revisions. They are rare, but do exist. Fiddling with the code you supplied, a range of "O1-D1" in the SEQ_REVISION field returns records for O through D, but not when the additional number is the string, though I tried changing the arguments in the SUBSTR function. I am not surprised it does not work as it seems Regex would fail as the range is broken by the number.

Do you think there is a way to solve this?

Sorry for the craziness. It was meeting-day yesterday and I had little time for code thought.

Thanks,
-J

Report message to a moderator

Re: WHERE Clause With A Range [message #660126 is a reply to message #660125]

Thu, 09 February 2017 04:54

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

works for me:

SQL> with data as (select 'A' as seq_revision from dual
  2                union all
  3                select 'O-A' as seq_revision from dual
  4                union
  5                select 'O1-D1' as seq_revision from dual
  6                union
  7                select 'A1-N' as seq_revision from dual)
  8  select substr(replace(SEQ_REVISION, 'O', '0'),1, decode(instr(SEQ_REVISION, '-'), 0, 1, instr(SEQ_REVISION, '-')-1)),
  9  substr(replace(SEQ_REVISION,'O','0'),instr(SEQ_REVISION,'-') + 1), seq_revision
 10  from data
 11  where 'D1' between substr(replace(SEQ_REVISION, 'O', '0'),1, decode(instr(SEQ_REVISION, '-'), 0, 1, instr(SEQ_REVISION, '-')-1))
 12  and substr(replace(SEQ_REVISION,'O','0'),instr(SEQ_REVISION,'-') + 1)
 13  ;
 
SUBSTR(REPLACE(SEQ_REVISION,'O SUBSTR(REPLACE(SEQ_REVISION,'O SEQ_REVISION
------------------------------ ------------------------------ ------------
A1                             N                              A1-N
01                             D1                             O1-D1
 
SQL>

Report message to a moderator

Re: WHERE Clause With A Range [message #660127 is a reply to message #660126]

Thu, 09 February 2017 04:56

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

The decode simply says if the instr returns 0 use 1 otherwise use result of instr.
You could write it as a case:

where 'D1' between substr(replace(SEQ_REVISION, 'O', '0'),1, CASE WHEN instr(SEQ_REVISION, '-') =  0 THEN 1
                                                             ELSE instr(SEQ_REVISION, '-')-1
                                                             END)

Report message to a moderator

Re: WHERE Clause With A Range [message #660129 is a reply to message #660127]

Thu, 09 February 2017 05:41

jmltinc
Messages: 14
Registered: January 2016

Junior Member

Sir,

I cannot thank you enough. I was once an Access developer, now developing LabVIEW and VB applications to work with an Oracle database for our company. Some of these query solutions are very new to me and you have been very patient and most helpful. I now realize the DECODE function is an IIF() in Access.

I cannot absorb nor test your code until this afternoon, but am very excited to implement the WITH Clause into the SQL. Besides solving my problem, it will hopefully teach me a great deal. One more question: You have hard-coded a few examples of the very many possibilities of values found in SEQ_REVISION and have returned results. But at run-time, I have no idea what might be in SEQ_REVISION and in writing the query could not possibly account for all permutations in the WITH clause. How would this work in the real world?

Thank you.
-John

[Updated on: Thu, 09 February 2017 08:16]

Report message to a moderator

Re: WHERE Clause With A Range [message #660135 is a reply to message #660129]

Thu, 09 February 2017 08:23

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You could use an in-line view to reduce the number of function calls and make the code easier to follow:

with data as (select 'A' as seq_revision from dual 
              union all 
              select 'O-A' as seq_revision from dual
              union 
              select 'O1-D1' as seq_revision from dual
              union 
              select 'A1-N' as seq_revision from dual)
select * 
from (select instr(SEQ_REVISION,'-') as dash_pos, 
      replace(SEQ_REVISION,'O','0') as seq_revision
      from data) v
where 'A' between substr(seq_revision, 1, case when dash_pos = 0 then length(seq_revision) else dash_pos -1 end)
and substr(seq_revision, dash_pos + 1)

Note I've tweaked the case statement to use length in case you can have values without a dash that are more than 1 character.

You could even create virtual columns on the table that do the split:

SQL> create table testt(seq_revision varchar2(8),
  2                     start_seq_revision varchar2(50) generated always as (substr(replace(SEQ_REVISION, 'O', '0'),
  3                                                                               1,
  4                                                                               CASE WHEN instr(SEQ_REVISION, '-') =  0
  5                                                                                    THEN length(seq_revision)
  6                                                                               ELSE instr(SEQ_REVISION, '-')-1
  7                                                                               END)) virtual,
  8                     end_seq_revision varchar2(50) generated always as (substr(replace(SEQ_REVISION,'O','0'),
  9                                                                             instr(SEQ_REVISION,'-') + 1)) virtual
 10                    );
 
Table created
SQL> insert into testt(seq_revision) values ('A');
 
1 row inserted
SQL> insert into testt(seq_revision) values ('A1-B4');
 
1 row inserted
SQL> insert into testt(seq_revision) values ('B-N');
 
1 row inserted
SQL> insert into testt(seq_revision) values ('O-C3');
 
1 row inserted
 
SQL> select * from testt;
 
SEQ_REVISION START_SEQ_REVISION                                 END_SEQ_REVISION
------------ -------------------------------------------------- --------------------------------------------------
A            A                                                  A
A1-B4        A1                                                 B4
B-N          B                                                  N
O-C3         0                                                  C3
 
SQL> select * from testt where 'A' between start_seq_revision and end_seq_revision;
 
SEQ_REVISION START_SEQ_REVISION                                 END_SEQ_REVISION
------------ -------------------------------------------------- --------------------------------------------------
A            A                                                  A
O-C3         0                                                  C3
 
SQL>

Report message to a moderator

Re: WHERE Clause With A Range [message #660136 is a reply to message #660135]

Thu, 09 February 2017 08:41

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

And one last rearrangement to hopefully make it it simpler to follow, with added comments:

where 'A' between case when dash_pos = 0 then seq_revision --no dash get everything
                       else substr(seq_revision, 1, dash_pos -1) --get everything upto the dash
                       end
and substr(seq_revision, dash_pos + 1) --Get everything after dash or everything if no dash
                                       --no dash means dash_pos = 0, so substr starts at 0 + 1
                                       --so first character

Report message to a moderator

Previous Topic:	generate dynamic query and avoiding recompiling query
Next Topic:	Using Oracle UTL_MAIL to send email through Kerio email server

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 18:14:54 CDT 2024