Solved

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

Posted on 2014-12-10
11
128 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 68

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 68

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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 34

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 34

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 31

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now