Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-12-10
11
Medium Priority
?
139 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 71

Accepted Solution

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

Expert Comment

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

Assisted Solution

by:PortletPaul
PortletPaul earned 400 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 14

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 14

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 14

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

782 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