IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need information about not equal condition in DB2.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sivasaras

New User


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

PostPosted: Fri Jun 17, 2011 7:23 pm
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
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: 93
Location: chenna/i-

PostPosted: Fri Jun 17, 2011 7:31 pm
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
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: 93
Location: chenna/i-

PostPosted: Fri Jun 17, 2011 7:54 pm
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
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: 93
Location: chenna/i-

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

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

Senior Member


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

PostPosted: Fri Jun 17, 2011 8:17 pm
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: 93
Location: chenna/i-

PostPosted: Fri Jun 17, 2011 8:23 pm
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

Senior Member


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

PostPosted: Fri Jun 17, 2011 8:25 pm
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: 93
Location: chenna/i-

PostPosted: Fri Jun 17, 2011 8:33 pm
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

Moderator Emeritus


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

PostPosted: Fri Jun 17, 2011 8:44 pm
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: 577
Location: USA

PostPosted: Fri Jun 17, 2011 8:53 pm
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: 8700
Location: Dubuque, Iowa, USA

PostPosted: Fri Jun 17, 2011 8:54 pm
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

Moderator Emeritus


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

PostPosted: Fri Jun 17, 2011 9:19 pm
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: 1281
Location: Belgium

PostPosted: Mon Jun 20, 2011 1:31 pm
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: 1353
Location: Israel

PostPosted: Mon Jun 20, 2011 9:10 pm
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: 93
Location: chenna/i-

PostPosted: Tue Jun 21, 2011 8:15 pm
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: 93
Location: chenna/i-

PostPosted: Tue Jun 21, 2011 8:20 pm
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: 1281
Location: Belgium

PostPosted: Tue Jun 21, 2011 8:34 pm
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
sivasaras

New User


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

PostPosted: Tue Jun 21, 2011 8:35 pm
Reply with quote

i am running the query in Production now.

once done let you know

please dont be angry yaar
Back to top
View user's profile Send private message
sivasaras

New User


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

PostPosted: Tue Jun 21, 2011 9:59 pm
Reply with quote

when i ran the query through the job, it is taking more CPU time to process.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Tue Jun 21, 2011 10:05 pm
Reply with quote

Quote:
it is taking more CPU time to process.
more that what ?
and what are we supposed to do about it ?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Jun 21, 2011 10:09 pm
Reply with quote

sivasaras wrote:
when i ran the query through the job, it is taking more CPU time to process.


Do you want fast incorrect results or correct results?
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA

PostPosted: Tue Jun 21, 2011 10:14 pm
Reply with quote

Hey, if the results don't have to be correct, you can very close to zero resource usage.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2 Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts Add condition to a FINDREP SORT card DFSORT/ICETOOL 4
No new posts To Omit records based n SORT condition DFSORT/ICETOOL 6
No new posts Using Multiple IFTHEN and WHEN condit... SYNCSORT 12
No new posts VSAM LISTCAT INFORMATION JCL & VSAM 2
No new posts Capturing Job Execution Information All Other Mainframe Topics 3
Search our Forums:

Back to Top