Home » SQL & PL/SQL » SQL & PL/SQL » Comparing two fields in the same table to see if they are different (10g)
Comparing two fields in the same table to see if they are different [message #403254] Thu, 14 May 2009 08:28 Go to next message
ddoss23
Messages: 2
Registered: May 2009
Location: Modesto, California
Junior Member
I have a transaction table in a warehouse management system and I need to see when the inventory license plate is being changed when certain transactions occur. The two fields I am trying to see if they are different are the from_lic and the to_lic. If the from_lic is not the same as the to_lic I would like to return the transaction record. I tried to use the != and <> operators but no luck. Any assistance will be greatly appreciated. Thanks.
Re: Comparing two fields in the same table to see if they are different [message #403256 is a reply to message #403254] Thu, 14 May 2009 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.

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).

Regards
Michel
Re: Comparing two fields in the same table to see if they are different [message #403258 is a reply to message #403254] Thu, 14 May 2009 08:35 Go to previous messageGo to next message
ddoss23
Messages: 2
Registered: May 2009
Location: Modesto, California
Junior Member
The SQL query that I am using is the following:

SELECT *
FROM TRANS
WHERE FROM_LIC <> TO_LIC
AND TYPE IN('000','004','009');
Re: Comparing two fields in the same table to see if they are different [message #403262 is a reply to message #403258] Thu, 14 May 2009 08:54 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You've either got no rows with differences on, or you've got nulls in one or both fields.

You could try this:
SELECT * 
 FROM TRANS
 WHERE (FROM_LIC <> TO_LIC
     OR (FROM_LIC IS NULL OR TO_LIC IS NULL))
 AND TYPE IN('000','004','009')
Previous Topic: Package verification
Next Topic: Read CSV file in PLSQL
Goto Forum:
  


Current Time: Tue Dec 06 16:01:02 CST 2016

Total time taken to generate the page: 0.26103 seconds