Home » SQL & PL/SQL » SQL & PL/SQL » Reading "Or" Operator (merged)
Reading "Or" Operator (merged) [message #382321] Wed, 21 January 2009 23:20 Go to next message
auto
Messages: 8
Registered: January 2009
Location: aus
Junior Member
HI we are upgrading from syybase to oracle 10g database.

And i am trying to update the old scripts. I am running a sql that is not giving out all teh records though the records are in the database with that particular gift_account. Example: Id 331290 with account K7071-22222 is missing from the result!

Please find the query below. are 'Or' operators read differently in oracle that is causing the problem?

script
select distinct gift.gift_donor_id from gift
where (gift.gift_account like 'K0017%' or
gift.gift_account like 'K701[25]%' or
gift.gift_account = 'K7013-D1015' or
gift.gift_account like 'K70[245789]1%' or
gift.gift_account like 'K71[01269]1%' or
gift.gift_account like 'K71[29]2%') and
(gift.gift_transaction_type not like '[BFISM2ZR]%' or gift.gift_transaction_type like 'Z[09AB]' )
Re: Reading "Or" Operator [message #382322 is a reply to message #382321] Wed, 21 January 2009 23:22 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Reading "Or" Operator [message #382324 is a reply to message #382321] Wed, 21 January 2009 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

In the end, give a feedback on the answers we give you, we are still, waiting for it in your previous topic to know if we had answered to your concern.
On this, my previous advice is still worth for this question. If you want to port Sybase, you MUST read Oracle SQL Reference I posted you.

Regards
Michel

[Updated on: Wed, 21 January 2009 23:25]

Report message to a moderator

Re: Reading "Or" Operator [message #382325 is a reply to message #382321] Wed, 21 January 2009 23:29 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Do NOT multi/cross post!
Re: Reading "Or" Operator (merged) [message #382379 is a reply to message #382321] Thu, 22 January 2009 01:24 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
auto wrote on Thu, 22 January 2009 06:20
Example: Id 331290 with account K7071-22222 is missing from the result

Correct behaviour, as it does not start with any of these strings: 'K0017%', 'K701[25]%', 'K7013-D1015', 'K70[245789]1%', 'K71[01269]1%', 'K71[29]2%'.
Note, that Oracle LIKE condition pattern knows only "%", '_' and (defined) escape special characters. If you want to use regular expressions, use REGEXP_LIKE function. Have a look into documentation for its syntax and usage.
Re: Reading "Or" Operator (merged) [message #382397 is a reply to message #382321] Thu, 22 January 2009 02:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't think it's the OR conditions that are givin you problems - it's the LIKE, as @Flyboy mentioned.

I think you need the 10g Regexp functions, particularly REGEXP_LIKE

I think this is a translation of the Gift_Account matching part of your logic.

If you can post some test data, and the results that you'd expect, then we can get you a more accurate query.
Re: Reading "Or" Operator (merged) [message #382534 is a reply to message #382321] Thu, 22 January 2009 19:36 Go to previous messageGo to next message
auto
Messages: 8
Registered: January 2009
Location: aus
Junior Member
Ok tahnks for the help. I have modified my query with Regexp_Like operator and the result has improved so much better!

But I am still missing many records. I have looked up Regexp on internet and could not find clear explanations and examples that cover all aspects. I am not sure if i have everything right in the code below and if it acurately matches my original old sql that I have posted in my first post. TIA


SELECT COUNT(DISTINCT gift.gift_donor_id) 
FROM   gift 
WHERE  (Regexp_like(gift.gift_account,'K0017') 
         OR Regexp_like(gift.gift_account,'K701[25]') 
         OR gift.gift_account = 'K7013-D1015' 
         OR Regexp_like(gift.gift_account,'K70[245789]') 
         OR Regexp_like(gift.gift_account,'K71[01269]1') 
         OR Regexp_like(gift.gift_account,'K71[29]2')) 
       AND (NOT Regexp_like(gift.gift_transaction_type,'[BFISM2ZR]') 
             OR NOT Regexp_like(gift.gift_transaction_type,'Z[09AB]')); 
Re: Reading "Or" Operator (merged) [message #382541 is a reply to message #382321] Thu, 22 January 2009 19:53 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Reading "Or" Operator (merged) [message #382604 is a reply to message #382534] Fri, 23 January 2009 03:37 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Did you follow my link to the Oracle documentation on REGEXP_LIKE?

The only way to tell if your query matches the original is to run the original against the Sybase data, run the new one against the migrated Oracle data, and compare the results.

I think you've got at least one mistake in there:
Last line of original query:
and (gift.gift_transaction_type not like '[BFISM2ZR]%' or gift.gift_transaction_type like 'Z[09AB]' )


Last line of new query:
AND (NOT Regexp_like(gift.gift_transaction_type,'[BFISM2ZR]') OR NOT regexp_like(gift.gift_transaction_type,'Z[09AB]'))


Your NOTs don't match.
Previous Topic: new and old in triggers
Next Topic: To Call Procedure Name Based on the CURSOR VALUE..
Goto Forum:
  


Current Time: Fri Dec 09 01:59:19 CST 2016

Total time taken to generate the page: 0.18711 seconds