Solved

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

Posted on 2014-12-10
11
136 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 70

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 70

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
Independent Software Vendors: 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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

739 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