Home » SQL & PL/SQL » SQL & PL/SQL » SQL Select Query (Oracle 10g)
SQL Select Query [message #401046] Fri, 01 May 2009 05:24 Go to next message
vinod_tiwari
Messages: 4
Registered: April 2009
Location: New Delhi
Junior Member
Hi,

I have to query a table (written below) which has like statement, this query has to be fired multiple times for different like values, is there any way I can convert it into a single query with something like IN clause?

Select * FROM CO_CALL_DATA_RECORDS WHERE INVOICE_NUMBER LIKE '200920%' AND LENGTH(INVOICE_NUMBER) =13;

Select * FROM CO_CALL_DATA_RECORDS WHERE INVOICE_NUMBER LIKE '200930%' AND LENGTH(INVOICE_NUMBER) =13;

Select * FROM CO_CALL_DATA_RECORDS WHERE INVOICE_NUMBER LIKE '200932%' AND LENGTH(INVOICE_NUMBER) =13;

Select * FROM CO_CALL_DATA_RECORDS WHERE INVOICE_NUMBER LIKE '200938%' AND LENGTH(INVOICE_NUMBER) =13;

Select * FROM CO_CALL_DATA_RECORDS WHERE INVOICE_NUMBER LIKE '200950%' AND LENGTH(INVOICE_NUMBER) =13;

Select * FROM CO_CALL_DATA_RECORDS WHERE INVOICE_NUMBER LIKE '200944%' AND LENGTH(INVOICE_NUMBER) =13;

Thanks for the help.

Cheers,
Vinod
Re: SQL Select Query [message #401047 is a reply to message #401046] Fri, 01 May 2009 05:28 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
LIKE doesn't work with IN, but if all the values you're doing the like against are the same length you could use substr.

You'd have to check the performance implications of doing so and possibly use a function based index.
Re: SQL Select Query [message #401048 is a reply to message #401046] Fri, 01 May 2009 05:31 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps something like
SELECT * FROM CO_CALL_DATA_RECORDS 
WHERE LENGTH(INVOICE_NUMBER) =13
  AND (INVOICE_NUMBER LIKE '200920%' or
       INVOICE_NUMBER LIKE '200930%' or
       INVOICE_NUMBER LIKE '200932%'
      )
Re: SQL Select Query [message #401059 is a reply to message #401048] Fri, 01 May 2009 06:08 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
perhaps another solution
if there is adefenite pattern of invoice number then
you can apply BETWEEN and AND also can be used

[Updated on: Fri, 01 May 2009 06:11]

Report message to a moderator

Previous Topic: Clarification in 'Insert into' statement (merged)
Next Topic: common column
Goto Forum:
  


Current Time: Fri Dec 09 09:38:01 CST 2016

Total time taken to generate the page: 0.12716 seconds