Sponsored Content
Top Forums Programming How to use maximum of date function in db2 Post 302527823 by dinesh1985 on Sunday 5th of June 2011 06:00:37 PM
Old 06-05-2011
Question How to use maximum of date function in db2

Hello Folks,
I am having a below example.

Code:
CAR_NAME   SERVICE_DATE
------------------------------------
ALTIMA        01/01/2007
ALTIMA        03/01/2007
ALTIMA        05/02/2008
FORD GT      01/01/2009
FORD GT      01/01/2010
FORD GT      01/06/2010
FORD GT      01/01/2011

In the above example, CARs and their servicing dates are stored in the table. Altima has done three services in which the latest service date is done on 05/02/2008 and FORD GT has done the latest service on 01/01/2011.

I need to write a db2 query to fetch the vehicles for which the latest service is done before Dec 2008. For the Above example, the expected result is ALTIMA.

I am trying something like this
Code:
SELECT DISTINCT CAR_NAME FROM CAR_SERVICE_TABLE WHERE MAX(SERVICE_DATE) < '12/31/2008' WITH UR

But this is not working.

I am getting the error as
SQL0120N invalid use of an aggregate function or OLAP function. SQLSTATE = 42903.

Please help me in writing the query
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

date function

hi, I have to ftp previous days file from a directory to another location. The name of the files are like "xxx20060225" (yyyymmdd format) "xxx20060226" ls -lrt xxx*| tail -2| head -1 will give me the file, but if i could get anything... (2 Replies)
Discussion started by: abey
2 Replies

2. Shell Programming and Scripting

Date Function

Hi, My file format is: E102,0,21-04-2007,0,2/25/1994,E003,A,125400,10450,60.2884620 E103,0,21/04/2007,0,2/2/1996,E003,A,125400,10450,60.2884620 E104,0,04/21/2007,0,2/2/1996,E003,A,125400,10450,60.2884620 E105,0,21-APR-2007,0,2/2/1996,E003,A,125400,10450,60.2884620... (1 Reply)
Discussion started by: charandevu
1 Replies

3. Shell Programming and Scripting

Date Function

Hi, My file format is: E102,0,21-04-2007,0,2/25/1994,E003,A,125400,10450,60.2884620 E103,0,21/04/2007,0,2/2/1996,E003,A,125400,10450,60.2884620 E104,0,04/21/2007,0,2/2/1996,E003,A,125400,10450,60.2884620 E105,0,21-APR-2007,0,2/2/1996,E003,A,125400,10450,60.2884620... (1 Reply)
Discussion started by: charandevu
1 Replies

4. UNIX for Dummies Questions & Answers

Exception while loading DB2 driver Class.forName("com.ibm.db2.jcc.DB2Driver")

Hi... I m working on UNIX z/OS. Actually i have to pass the parameters from the JCL to java-db2 program thru PARM. I am able to pass the arguments but the problem occured is, it is throwing an exception while loading the db2 driver as 'Javaclassnotfound:com.ibm.db2.jcc.DB2Driver'... (0 Replies)
Discussion started by: Sujatha Gowda
0 Replies

5. Shell Programming and Scripting

Date function

I read man page for etc/shadow field.. on the 8th field, i assume that's the field to change account expire date. my question is: What value does the 8th field keep? i assume it's 13514 instead of "Date" value such as 11/10/08. on the man page, it said: " expire value = 13514 = jan 1,... (11 Replies)
Discussion started by: c00kie88
11 Replies

6. Shell Programming and Scripting

Compare date from db2 table to yesterday's Unix system date

I am currently running the following Korn shell script which works fine: #!/usr/bin/ksh count=`db2 -x "select count(*) from schema.tablename"` echo "count" I would like to add a "where" clause to the 2nd line that would allow me to get a record count of all the records from schema.tablename... (9 Replies)
Discussion started by: sasaliasim
9 Replies

7. Shell Programming and Scripting

What is the maximum number of parameter we can pass to a shell script function?

what is the maximum number of parameter we can pass to a shell script function (8 Replies)
Discussion started by: alokjyotibal
8 Replies

8. Programming

Regarding the maximum memory allocated by malloc() function on HP-UX B11.11

In a 'C' program,when I am trying to allocate memory with the help of malloc () function, it is allocating the memory up to a certain limit for e.g. in my case, it is 670 MB (approx). malloc() returns NULL if I allocate more than this amount of memory.When I tried to allocate memory in chunks of... (1 Reply)
Discussion started by: vipinsachan
1 Replies

9. Shell Programming and Scripting

Get maximum per column from CSV file, based on date column

Hello everyone, I am using ksh on Solaris 10 and I'm gathering data in a CSV file that looks like this: 20170628-23:25:01,1,0,0,1,1,1,1,55,55,1 20170628-23:30:01,1,0,0,1,1,1,1,56,56,1 20170628-23:35:00,1,0,0,1,1,2,1,57,57,2 20170628-23:40:00,1,0,0,1,1,1,1,58,58,2... (6 Replies)
Discussion started by: ejianu
6 Replies

10. UNIX for Beginners Questions & Answers

DB2 Query modification to remove duplicate values using LISTAGG function

I am using DB2 v9 and trying to get country values in comma seperated format using below query SELECT distinct LISTAGG(COUNTRIES, ',') WITHIN GROUP(ORDER BY EMPLOYEE) FROM LOCATION ; Output Achieved MEXICO,UNITED STATES,INDIA,JAPAN,UNITED KINGDOM,MEXICO,UNITED STATES The table... (4 Replies)
Discussion started by: Perlbaby
4 Replies
CD-HIT-EST-2D(1)						   User Commands						  CD-HIT-EST-2D(1)

NAME
cdhit-est-2d - run CD-HIT algorithm on RNA/DNA sequences in db1 or db2 format SYNOPSIS
cdhit-est-2d [Options] DESCRIPTION
====== CD-HIT version 4.6 (built on Apr 26 2012) ====== Options -i input filename for db1 in fasta format, required -i2 input filename for db2 in fasta format, required -o output filename, required -c sequence identity threshold, default 0.9 this is the default cd-hit's "global sequence identity" calculated as: number of identical amino acids in alignment divided by the full length of the shorter sequence -G use global sequence identity, default 1 if set to 0, then use local sequence identity, calculated as : number of identical amino acids in alignment divided by the length of the alignment NOTE!!! don't use -G 0 unless you use alignment coverage controls see options -aL, -AL, -aS, -AS -b band_width of alignment, default 20 -M memory limit (in MB) for the program, default 800; 0 for unlimitted; -T number of threads, default 1; with 0, all CPUs will be used -n word_length, default 10, see user's guide for choosing it -l length of throw_away_sequences, default 10 -d length of description in .clstr file, default 20 if set to 0, it takes the fasta defline and stops at first space -s length difference cutoff, default 0.0 if set to 0.9, the shorter sequences need to be at least 90% length of the representative of the cluster -S length difference cutoff in amino acid, default 999999 if set to 60, the length difference between the shorter sequences and the representative of the cluster can not be bigger than 60 -s2 length difference cutoff for db1, default 1.0 by default, seqs in db1 >= seqs in db2 in a same cluster if set to 0.9, seqs in db1 may just >= 90% seqs in db2 -S2 length difference cutoff, default 0 by default, seqs in db1 >= seqs in db2 in a same cluster if set to 60, seqs in db2 may 60aa longer than seqs in db1 -aL alignment coverage for the longer sequence, default 0.0 if set to 0.9, the alignment must covers 90% of the sequence -AL alignment coverage control for the longer sequence, default 99999999 if set to 60, and the length of the sequence is 400, then the alignment must be >= 340 (400-60) residues -aS alignment coverage for the shorter sequence, default 0.0 if set to 0.9, the alignment must covers 90% of the sequence -AS alignment coverage control for the shorter sequence, default 99999999 if set to 60, and the length of the sequence is 400, then the alignment must be >= 340 (400-60) residues -A minimal alignment coverage control for the both sequences, default 0 alignment must cover >= this value for both sequences -uL maximum unmatched percentage for the longer sequence, default 1.0 if set to 0.1, the unmatched region (excluding leading and tailing gaps) must not be more than 10% of the sequence -uS maximum unmatched percentage for the shorter sequence, default 1.0 if set to 0.1, the unmatched region (excluding leading and tail- ing gaps) must not be more than 10% of the sequence -U maximum unmatched length, default 99999999 if set to 10, the unmatched region (excluding leading and tailing gaps) must not be more than 10 bases -B 1 or 0, default 0, by default, sequences are stored in RAM if set to 1, sequence are stored on hard drive it is recommended to use -B 1 for huge databases -p 1 or 0, default 0 if set to 1, print alignment overlap in .clstr file -g 1 or 0, default 0 by cd-hit's default algorithm, a sequence is clustered to the first cluster that meet the threshold (fast clus- ter). If set to 1, the program will cluster it into the most similar cluster that meet the threshold (accurate but slow mode) but either 1 or 0 won't change the representatives of final clusters -r 1 or 0, default 1, by default do both +/+ & +/- alignments if set to 0, only +/+ strand alignment -mask masking letters (e.g. -mask NX, to mask out both 'N' and 'X') -match matching score, default 2 (1 for T-U and N-N) -mismatch mismatching score, default -2 -gap gap opening score, default -6 -gap-ext gap extension score, default -1 -bak write backup cluster file (1 or 0, default 0) -h print this help Questions, bugs, contact Limin Fu at l2fu@ucsd.edu, or Weizhong Li at liwz@sdsc.edu For updated versions and information, please visit: http://cd-hit.org cd-hit web server is also available from http://cd-hit.org If you find cd-hit useful, please kindly cite: "Clustering of highly homologous sequences to reduce thesize of large protein database", Weizhong Li, Lukasz Jaroszewski & Adam Godzik. Bioinformatics, (2001) 17:282-283 "Cd-hit: a fast program for clustering and comparing large sets of protein or nucleotide sequences", Weizhong Li & Adam Godzik. Bioinformatics, (2006) 22:1658-1659 cd-hit-est-2d 4.6-2012-04-25 April 2012 CD-HIT-EST-2D(1)
All times are GMT -4. The time now is 04:28 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy