Home » SQL & PL/SQL » SQL & PL/SQL » WHERE Clause With A Range
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 #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
|
|
|
Re: WHERE Clause With A Range [message #660070 is a reply to message #660068] |
Wed, 08 February 2017 05:41 |
cookiemonster
Messages: 13925 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?
|
|
|
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:41So 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: 13925 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
|
|
|
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
|
|
|
Re: WHERE Clause With A Range [message #660081 is a reply to message #660080] |
Wed, 08 February 2017 09:22 |
cookiemonster
Messages: 13925 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?
|
|
|
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
Thanks,
-J
|
|
|
|
Re: WHERE Clause With A Range [message #660108 is a reply to message #660089] |
Thu, 09 February 2017 03:00 |
cookiemonster
Messages: 13925 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)
|
|
|
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
|
|
|
Re: WHERE Clause With A Range [message #660126 is a reply to message #660125] |
Thu, 09 February 2017 04:54 |
cookiemonster
Messages: 13925 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>
|
|
|
Re: WHERE Clause With A Range [message #660127 is a reply to message #660126] |
Thu, 09 February 2017 04:56 |
cookiemonster
Messages: 13925 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)
|
|
|
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: 13925 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>
|
|
|
Re: WHERE Clause With A Range [message #660136 is a reply to message #660135] |
Thu, 09 February 2017 08:41 |
cookiemonster
Messages: 13925 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
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:26:16 CDT 2024
|