Select where Right (ID, 2) = ('00','01')
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.
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')ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Qlemo
But note that renders any index unusuable, so it might introduce performance penalities.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The field that i have is a generic number with 12 numbers. Ex: '201412161823'
let me give it a try.
Thanks
let me give it a try.
Thanks
![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
Another option could be to create a virtual column with the SUBSTR... and then create an index upon that new virtual column ;-)
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.
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.
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...
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.
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.
another nice link about padding in Oracle:
http://jonathanlewis.wordpress.com/2011/12/02/to_char/
http://jonathanlewis.wordpress.com/2011/12/02/to_char/
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)
ASKER
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.
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.