Home » SQL & PL/SQL » SQL & PL/SQL » Not exist (Oracle 9i)
Not exist [message #345858] Fri, 05 September 2008 02:27 Go to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
Hi

I ahve got this Join thats joins 2 table on Putawayzone.
This shows me where A = C I want it to show me where A.PUTAWAYZONE is not equal to C.PUTAWAYZONE

Select A.SKU, A.PUTAWAYZONE, C.PUTAWAYZONE from sku A
join LOC C
on A.PUTAWAYZONE = C.PUTAWAYZONE

Data Returned ----------------
----------------------------------------
SKU PUTAWAYZONE PUTAWAYZONE_1
02G00D ABULK-Z ABULK-Z
02G00D ABULK-Z ABULK-Z
02G00D ABULK-Z ABULK-Z
02G00D ABULK-Z ABULK-Z
02G00D ABULK-Z ABULK-Z
02G00D ABULK-Z ABULK-Z
02G00D ABULK-Z ABULK-Z
02G00D ABULK-Z ABULK-Z
02G00D ABULK-Z ABULK-Z
02G00D ABULK-Z ABULK-Z
02G00D ABULK-Z ABULK-Z
02G00D ABULK-Z ABULK-Z
Re: Not exist [message #345864 is a reply to message #345858] Fri, 05 September 2008 02:49 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would you, please, read the OraFAQ Forum Guide? It will help you to get familiar with certain rules that apply on our Forum. Among other things, you are required to properly format your code, as well as use punctuation because - the way you've written it - your message is hardly readable.

This is your current query:
Select A.SKU, A.PUTAWAYZONE, C.PUTAWAYZONE 
from sku A
join LOC C
on A.PUTAWAYZONE = C.PUTAWAYZONE

As you'd like to
Quote:
show A.PUTAWAYZONE is not equal to C.PUTAWAYZONE

modify the WHERE clause:
Select A.SKU, A.PUTAWAYZONE, C.PUTAWAYZONE 
from sku A, loc C
WHERE A.PUTAWAYZONE <> C.PUTAWAYZONE
Re: Not exist [message #345923 is a reply to message #345858] Fri, 05 September 2008 05:12 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
With a little bit of work. This is what I got and it works great.

Select DISTINCT
LX.SKU, sk.descr,LX.QTY, LX.LOC AS LOC_IN_LOTXLOCID,l.LOC AS LOC_IN_LOC , L.PUTAWAYZONE AS PUTAWAYZONE_IN_LOC, SK.PUTAWAYZONE AS PUTAWAYZONE_IN_SKU from lotxlocxid LX
JOIN LOC L
ON LX.LOC = L.loc
JOIN SKU SK
ON LX.SKU = SK.SKU
WHERE
LENGTH(lx.loc) = 9
AND L.PUTAWAYZONE <> SK.PUTAWAYZONE
and LPAD(L.PUTAWAYZONE,1) <> LPAD(SK.PUTAWAYZONE,1)
and LPAD(LX.LOC,2) <> 'AS'
and LPAD(LX.LOC,2) <> 'BS'
and LX.QTY > 0
ORDER BY SKU

How can I wriet it so that I don't have to say AND AND AND AND AND. Is there a shorter way?
Re: Not exist [message #345925 is a reply to message #345923] Fri, 05 September 2008 05:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That appears to be the answer to a completely different question to the one you asked.

Why don't you take 5 minutes, and write us an explaation of what you're trying to acheive. Assume that we know nothing about your application, nothing about your tables, and nothing about the problem you're trying to solve.

Re: Not exist [message #345927 is a reply to message #345923] Fri, 05 September 2008 05:54 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Hi,

Deon Smit wrote on Fri, 05 September 2008 15:42

AND L.PUTAWAYZONE <> SK.PUTAWAYZONE
and LPAD(L.PUTAWAYZONE,1) <> LPAD(SK.PUTAWAYZONE,1)


What is the use of the above condition? IF L.PUTAWAYZONE <> SK.PUTAWAYZONE, do you expect it to match by padding the columns with 1.
So I guess you can avoid LPAD(L.PUTAWAYZONE,1) <> LPAD(SK.PUTAWAYZONE,1) condition.

Deon Smit wrote on Fri, 05 September 2008 15:42

and LPAD(LX.LOC,2) <> 'AS'
and LPAD(LX.LOC,2) <> 'BS'


Try to use a NOT IN OR NOT EXISTS to combine the above conditions.

Regards,
Jo
Re: Not exist [message #345930 is a reply to message #345923] Fri, 05 September 2008 05:58 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Deon Smit,

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) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Fri, 05 September 2008 05:58]

Report message to a moderator

Previous Topic: Writing a Log File
Next Topic: Regarding passing Array as a argument in the stored procedure/Function
Goto Forum:
  


Current Time: Thu Dec 08 00:15:58 CST 2016

Total time taken to generate the page: 0.06992 seconds