|
View previous topic :: View next topic
|
| Author |
Message |
chrakesh
New User
.jpg)
Joined: 03 May 2010
Posts: 16
Location: india
|
|
|
|
Hi guys can anyone tell me about this and what query we need to use....
Example: The latest NIK with the same country and supplier, in this example is “AC”
Part
------------------
12345678AA
12345678AB
12345678AC
The above statement to identify the latest part NIK level. (i.e.) The first 8 character should be same and maximum of 9th and 10th character |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| MAX function ??? |
|
| Back to top |
|
|
chrakesh
New User
Joined: 03 May 2010
Posts: 16
Location: india
|
|
|
|
| yes,finding which is max. and the latest one... |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
| Back to top |
|
|
chrakesh
New User
Joined: 03 May 2010
Posts: 16
Location: india
|
|
|
|
yes i got the way to find the max , but for the first 8 chars do we need to use the SUBSTR for sql, but in COBOL pgm what we need to give?
The first 8 character should be same and maximum of 9th and 10th character |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
How many different "first 8 character" values will be processed in one query?
If there will be more than one, you should show a better set of sample data. . . |
|
| Back to top |
|
|
chrakesh
New User
Joined: 03 May 2010
Posts: 16
Location: india
|
|
|
|
| yes there will be more than one , so just we need to compare the data that is given say if there is '12345678' or '87984743' like that many number of part names, based on that we need to check the condition and check the max. of the remaining other two alphabets . |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
if the issue is related to a db2 query why ...
| Quote: |
| but in COBOL pgm what we need to give? |
how is the <thing> defined in the db2 table ? |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
I think this is what you mean:
| Code: |
SELECT * FROM your.table
WHERE NIK = (SELECT MAX(NIK) FROM your.table WHERE NIK LIKE '12345678%') |
NB: query not verified. Please double-check. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
I MIGHT BE WRONG, just asking
can the group by and the max be applied together with the substr ??
something along the lines of
| Code: |
| max(substr(nik,9,2)) group by substr(nik,1,8) |
approximate syntax  |
|
| Back to top |
|
|
chrakesh
New User
Joined: 03 May 2010
Posts: 16
Location: india
|
|
|
|
| Thanks guys . Iwill check it out.... |
|
| Back to top |
|
|
chrakesh
New User
Joined: 03 May 2010
Posts: 16
Location: india
|
|
|
|
| Marso wrote: |
I think this is what you mean:
| Code: |
SELECT * FROM your.table
WHERE NIK = (SELECT MAX(NIK) FROM your.table WHERE NIK LIKE '12345678%') |
NB: query not verified. Please double-check. |
If there many diff NIK id's , what we need to give? |
|
| Back to top |
|
|
Binop B
Active User
Joined: 18 Jun 2009
Posts: 407
Location: Nashville, TN
|
|
|
|
Hi chrakesh,
| Quote: |
| If there many diff NIK id's , what we need to give? |
It would be better if you can explain your requirements better ... probably with some some samples and the desired output ... with the sample that you have given now there is only one NIK id ... Probably what you are looking for is the IN predicate ...  |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| enrico-sorichetti wrote: |
| Code: |
| max(substr(nik,9,2)) group by substr(nik,1,8) |
|
looks fine
you could just say (= same thing)
| Code: |
| max(nik) group by substr(nik,1,8) |
| marso wrote: |
| Code: |
SELECT * FROM your.table
WHERE NIK = (SELECT MAX(NIK) FROM your.table WHERE NIK LIKE '12345678%') |
|
| chrakesh wrote: |
| If there many diff NIK id's , what we need to give? |
My first answer is : your resignation, because anyone with basic SQL knowledge should be able to solve that himself. (=correlated subquery)
My second answer:
| Code: |
SELECT * FROM your.table A
WHERE NIK = (SELECT MAX(B.NIK) FROM your.table B WHERE B.NIK between substr(a.NIK,1,8) and substr(a.NIK,1,8) !! x'FF') |
you can just compare substr(), but between is indexable. |
|
| Back to top |
|
|
|
|
