Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need information about not equal condition in DB2.
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Fri Jun 17, 2011 7:23 pm    Post subject: Need information about not equal condition in DB2.
Reply with quote

I have a table which has Dealer Name and Dealer codes.

I want to list only the dealer codes which don’t have the code as 'ES'

When I ran the below query I am able to receive the dealer codes with ES also.

SELECT .Dealer_name, Dealer_code
FROM Dealer_table
WHERE dealer_code <> 'ES' ;

Please guide me.

Thanks in advance
Back to top
View user's profile Send private message

gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Jun 17, 2011 7:28 pm    Post subject:
Reply with quote

Check the dealer code if it has appended spaces. example "ES ".

Try to use STRIP function.
Back to top
View user's profile Send private message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Fri Jun 17, 2011 7:31 pm    Post subject:
Reply with quote

Hi,

I have not used STRIP function and there is no spaces also in the dealar
code it is only char(2).

Please advice me.

Thanks
sivasaraswathy
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jun 17, 2011 7:46 pm    Post subject:
Reply with quote

what is the datatype and column attributes of 'dealer code'?

is there only one dealer code per row?

is there only one dealer name per row?
Back to top
View user's profile Send private message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Fri Jun 17, 2011 7:54 pm    Post subject:
Reply with quote

Dealer_code char(2) not null

No, there can be many dealer codes for each row

Same there can be many dealer names for each row

Each code has each dealer names
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Jun 17, 2011 8:02 pm    Post subject:
Reply with quote

is the dealer codes "es" or "ES" in the table?
Back to top
View user's profile Send private message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Fri Jun 17, 2011 8:04 pm    Post subject:
Reply with quote

dealer code is 'ES' only
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 961
Location: Richmond, Virginia

PostPosted: Fri Jun 17, 2011 8:17 pm    Post subject:
Reply with quote

How about cut&paste with code tags your sql and a short part of the results.

I do not understand the period before your first column name after SELECT.
Back to top
View user's profile Send private message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Fri Jun 17, 2011 8:23 pm    Post subject:
Reply with quote

SELECT Dealer_name, Dealer_code
FROM Dealer_table
WHERE Dealer_code <> 'ES' ;
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 961
Location: Richmond, Virginia

PostPosted: Fri Jun 17, 2011 8:25 pm    Post subject:
Reply with quote

What about the word "results" is confusing to you?
Back to top
View user's profile Send private message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Fri Jun 17, 2011 8:33 pm    Post subject:
Reply with quote

Results

APAMETALSS AP
NSABOARDSS NA
MASCONSBLE MA

There are 10 records in the file and out of that I received these 3 records which don’t have ES as dealer code.
But when I check the dealer name APAMETALSS this has the ES dealer code,

I want only 2 records to be displayed which don’t have ES dealer codes
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jun 17, 2011 8:44 pm    Post subject:
Reply with quote

Hello,

Well, i'm certainly confused. . . icon_confused.gif

Quote:
But when I check the dealer name APAMETALSS this has the ES dealer code,
I see no ES in the result below.
Quote:
APAMETALSS AP


How many rows have APAMETALSS as the dealer name?
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Fri Jun 17, 2011 8:53 pm    Post subject:
Reply with quote

Looks like you have duplicate rows for Dealer_name but with different dealer_code.
Try this untested...
Code:
WITH FIRST_PASS AS
(
 SELECT SELECT Dealer_name
 FROM Dealer_table
 WHERE Dealer_code = 'ES'
)
, FINAL_PASS AS
(
SELECT DT1.Dealer_name, DT1.Dealer_code
FROM Dealer_table DT1 LEFT OUTER JOIN
     FIRST_PASS FP
ON DT1.Dealer_name = FP.Dealer_name
WHERE FP.Dealer_name IS NULL
)
SELECT * FROM FINAL_PASS;

Thanks,
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7904
Location: Bellevue, IA

PostPosted: Fri Jun 17, 2011 8:54 pm    Post subject:
Reply with quote

Dick, I got confused when I saw this from sivasaras:
Quote:
No, there can be many dealer codes for each row

Same there can be many dealer names for each row

Each code has each dealer names
Somebody does not understand what a row is, or somebody does not understand what "relational" means, or the data base is not defined as we have been told, or .... hence, CONFUSION!
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jun 17, 2011 9:19 pm    Post subject: Reply to: Need information about not equal condition in DB2.
Reply with quote

Hi Robert,

Quote:
Somebody does not understand what a row is, or somebody does not understand what "relational" means, or the data base is not defined as we have been told, or .... hence, CONFUSION!

Yup icon_rolleyes.gif

d
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Jun 20, 2011 1:31 pm    Post subject:
Reply with quote

Code:
select * from table1 A
where not exists (select * from table1 B
where A.PK = B.PK and dealer_code = 'ES')
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1218
Location: Israel

PostPosted: Mon Jun 20, 2011 9:10 pm    Post subject:
Reply with quote

If your table contains:
Code:
APAMETALSS AP   
APAMETALSS ES
NSABOARDSS NA
MASCONSBLE MA

and your query is:
Code:
SELECT Dealer_name, Dealer_code
FROM Dealer_table
WHERE Dealer_code <> 'ES' ;

then you've got what you deserve...
To get what you want, you can use:
Code:
SELECT M.Dealer_name, M.Dealer_code
FROM Dealer_table M
WHERE M.Dealer_name NOT IN (SELECT S.Dealer_name FROM Dealer_table S WHERE S.Dealer_code = 'ES');
Back to top
View user's profile Send private message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Tue Jun 21, 2011 8:15 pm    Post subject: query information
Reply with quote

Hi,

I want to query using one table.

Table Name : Dealer_table

Dealer_name char(10) Dealer_code char(2)

Marksmitha AA
Marksmitha BB
Marksmitha CC
Marksmitha DD
Marksmitha EE
Marksmitha FF
Marksmitha GG
Marksmitha HH
Marksmitha SS
Marksmitha MM
Marksmitha PQ
AGSDELAERS AA
AGSDELAERS BB
AGSDELAERS CC
AGSDELAERS PQ
SAMSUNGMSS AA
SAMSUNGMSS BB
SAMSUNGMSS CC
SAMSUNGMSS HH

These are the sample records.

NOw i want the dealer names which dont have the dealer code as PQ.

when i ran the below query i got the above 3 dealer names

SELECT DEALER_NAME,DEALER_CODE
FROM DEALER_TABLE
WHERE DEALER_CODE <> PQ ;

Result
Marksmitha
AGSDELAERS
SAMSUNGMSS

But i want only the SAMSUNGMSS dealer name to be displayed.

Thanks
Siva
Back to top
View user's profile Send private message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Tue Jun 21, 2011 8:20 pm    Post subject:
Reply with quote

sorry i posted 2 times

please forgive me
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Jun 21, 2011 8:34 pm    Post subject:
Reply with quote

me & marso gave you possible solutions? Do you want to implement it yourself or do we have to do your job for you?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts Obtaining file information pahiker COBOL Programming 25 Tue Sep 27, 2016 7:25 pm
No new posts LIKE vs EQUAL Nileshkul DB2 7 Sat Jul 16, 2016 7:50 am
No new posts INCLUDE condition questions van bui DFSORT/ICETOOL 1 Thu Jun 30, 2016 9:52 am
No new posts Need information on COBOL MOVE CORRES... subratarec COBOL Programming 10 Tue Jun 28, 2016 10:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us