Solved

Select where Right (ID, 2) = ('00','01')

Posted on 2014-12-10
11
135 Views
Last Modified: 2014-12-18
Hi

I would like to add a where clause that if the 2 last chars in the field ID = '00' or '01' .

That way, i will get all the PO ID that finish with 00 or 01.

How can i do that?

thanks for your help.


SELECT DISTINCT ID, SCHED_NUM, SOURCE_ID, DEST_ID FROM LCLRPT.LCL_ORD_AUD WHERE SOURCE_ID IN ('21','24','03','60','67','34','57')

Open in new window

0
Comment
Question by:Wilder1626
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 69

Accepted Solution

by:
Qlemo earned 400 total points
ID: 40492854
... Where Substr(SOURCE_ID, -2) IN ('21','24','03','60','67','34','57')
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40492855
But note that renders any index unusuable, so it might introduce performance penalities.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 40492868
What type of field is "ID"? Integer?

If it is integer then you would have to convert to string first, then use: SUBSTR(STRING_ID, -2) IN ('00','01')

But also note that using functions like this is not great for performance as indexes won't be used - unless you also create a function based index to suit.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Author Comment

by:Wilder1626
ID: 40493369
The field that i have is a generic number with 12 numbers. Ex: '201412161823'

let me give it a try.

Thanks
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40493461
Another option could be to create a virtual column with the SUBSTR... and then create an index upon that new virtual column ;-)
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40493597
If the field is a number datatype, there is no need to convert it to a string.  The SUBSTR function call will do an implicit conversion for you.

However, be aware that if you are looking for 00 or 01 and the field contains a single digit number (i.e. 1) it will not be found because there is no leading 0.  If a single digit could happen and you want to select that row, you would have to do the conversion to character yourself and account for adding leading 0s.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40493610
to do the conversion to character yourself and account for adding leading 0s
-> using LPAD function

The SUBSTR function call will do an implicit conversion for you.
-> Yes, but you definitely should use a conversion function (TO_CHAR) whenever using numeric values in "string-oriented" functions! It's far more "readable" and imho a better/proper coding style/pattern. Implicit conversions may cause weird problems which in trun might be hard to find...
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40493637
If you need to add leading 0s and you want to do an explicit conversion, why use both TO_CHAR and LPAD?  TO_CHAR is capable of the padding required in this scenario without an additional function call.

While in most cases I would use explicit conversion, on number to string, there really isn't much environmental factors that could change the outcome.  Date to string absolutely.  An implicit conversion would tend to be a little faster performance wise as well.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40493762
another nice link about padding in Oracle:
http://jonathanlewis.wordpress.com/2011/12/02/to_char/
0
 
LVL 32

Expert Comment

by:awking00
ID: 40503057
I'm not sure what you mean by the field being a "generic number". Is it a varchar2 datatype or a number datatype. If characters, the substring method suggested (i.e. substr(field,-2) in ('00',01') will work. If it's a number you can use - where mod(field,100) in (0,1)
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 40507697
Thanks a lot for all your help.

It was finally not to hard to do what i needed. Substr was perfect.

Thanks again to all of you that help me understand multiple options.
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL query and VBA 5 52
PL/SQl Expanding the WHERE statement in query 3 33
sql update 2 36
Need quicker response from an Execption table 11 19
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question