|
View previous topic :: View next topic
|
| Author |
Message |
sivasaras
New User
Joined: 29 Sep 2007
Posts: 93
Location: chenna/i-
|
|
|
|
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 |
|
 |
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Check the dealer code if it has appended spaces. example "ES ".
Try to use STRIP function. |
|
| Back to top |
|
|
sivasaras
New User
Joined: 29 Sep 2007
Posts: 93
Location: chenna/i-
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
sivasaras
New User
Joined: 29 Sep 2007
Posts: 93
Location: chenna/i-
|
|
|
|
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 |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| is the dealer codes "es" or "ES" in the table? |
|
| Back to top |
|
|
sivasaras
New User
Joined: 29 Sep 2007
Posts: 93
Location: chenna/i-
|
|
|
|
| dealer code is 'ES' only |
|
| Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1050
Location: Richmond, Virginia
|
|
|
|
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 |
|
|
sivasaras
New User
Joined: 29 Sep 2007
Posts: 93
Location: chenna/i-
|
|
|
|
SELECT Dealer_name, Dealer_code
FROM Dealer_table
WHERE Dealer_code <> 'ES' ; |
|
| Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1050
Location: Richmond, Virginia
|
|
|
|
| What about the word "results" is confusing to you? |
|
| Back to top |
|
|
sivasaras
New User
Joined: 29 Sep 2007
Posts: 93
Location: chenna/i-
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Well, i'm certainly confused. . . 
| Quote: |
| But when I check the dealer name APAMETALSS this has the ES dealer code, |
I see no ES in the result below.
How many rows have APAMETALSS as the dealer name? |
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 
d |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
select * from table1 A
where not exists (select * from table1 B
where A.PK = B.PK and dealer_code = 'ES') |
|
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
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 |
|
|
sivasaras
New User
Joined: 29 Sep 2007
Posts: 93
Location: chenna/i-
|
|
|
|
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 |
|
|
sivasaras
New User
Joined: 29 Sep 2007
Posts: 93
Location: chenna/i-
|
|
|
|
sorry i posted 2 times
please forgive me |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
sivasaras
New User
Joined: 29 Sep 2007
Posts: 93
Location: chenna/i-
|
|
|
|
i am running the query in Production now.
once done let you know
please dont be angry yaar |
|
| Back to top |
|
|
sivasaras
New User
Joined: 29 Sep 2007
Posts: 93
Location: chenna/i-
|
|
|
|
| when i ran the query through the job, it is taking more CPU time to process. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
| Quote: |
| it is taking more CPU time to process. |
more that what ?
and what are we supposed to do about it ? |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| 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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA
|
|
|
|
| Hey, if the results don't have to be correct, you can very close to zero resource usage. |
|
| Back to top |
|
|
|
|
