need sql help for select min

[kjaouhari]

Hi all ,

I'm trying to select the minimun date of table, for example :
-----------------------------------
num1 01/01/06 --> ok
num1 01/02/06
num1 01/11/07
num2 01/01/06 --> ok
num2 01/03/06
-----------------------------------
result expect :
num1 01/01/06
num2 01/01/06

I use min() but when I perform a view data I have no difference.

Thanks in advance !

[Amos.Rosmarin]

Hi,

It's hard to say without looking at your SQL

Assuming your SQL is correct - It can be a view-data problem, try to execute the job and see the results.
The view-data is an excellent development helper but sometimes it can be misleading.

HTH,
Amos

[DSguru2B]

what is your sql? Need to take a look at that.
Also, when you write your own sql, press OK, get out of the stage, go back in and check wheather the changes you made were saved or not. I know the DB2 API gave me a lot of trouble once with this.

[kjaouhari]

This is my sql code :
-----------------------------------------------------
SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0,MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS')),
FROM #LNomDos#.PERIOD
GROUP BY PERIOD.CPY_0
----------------------------------------------------
I want to keep just lines with minimum date ...

[kcbland]

Your SQL doesn't work. You either need to add more columns to the GROUP clause or use a subselect. You also need to do the MIN on the data before converting it to char. You also have a trailing comma in the column list that won't let the SQL work.

Either:

Code: Select all

SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0, TO_CHAR(MIN(PERIOD.PERSTR_0), 'YYYY-MM-DD HH24:MI:SS') 
FROM #LNomDos#.PERIOD 
GROUP BY PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0

Or:

Code: Select all

SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0, TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS') 
FROM #LNomDos#.PERIOD 
WHERE PERIOD.CPY_0||PERIOD.PERSTR_0 IN
   (SELECT PERIOD.CPY_0||MIN(PERIOD.PERSTR_0)
    FROM #LNomDos#.PERIOD GROUP BY PERIOD.CPY_0 
   )

Both give different answers, but you need to decide which one is appropriate.

[kris007]

Try this

Code: Select all

SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0, MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS')), 
FROM #LNomDos#.PERIOD 
GROUP BY PERIOD.CPY_0, PERIOD.FIYNUM_0, PERIOD.PERNUM_0

or you should also perform some kind of aggregate functions on columns PERIOD.FIYNUM_0,PERIOD.PERNUM_0

[sb_akarmarkar]

Try this

Code: Select all

SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0, MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS')), 
FROM #LNomDos#.PERIOD 
GROUP BY PERIOD.CPY_0, PERIOD.FIYNUM_0, PERIOD.PERNUM_0

or you should also perform some kind of aggregate functions on columns PERIOD.FIYNUM_0,PERIOD.PERNUM_0

MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS'))

Won't look correct .......... Will it give desire result.........

Thanks,
Anupam

[kris007]

MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS'))

Won't look correct .......... Will it give desire result.........

Will work as good as it can
Yes, it gives desired results. I used the MAX function many a times. So, shouldn't be any different for MIN function.

[sb_akarmarkar]

MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS'))

Won't look correct .......... Will it give desire result.........

Will work as good as it can
Yes, it gives desired results. I used the MAX function many a times. So, shouldn't be any different for MIN function.

But you are converting date to char and then applying the MIN to char value

I think it should be.. TO_CHAR(MIN(PERIOD.PERSTR_0), 'YYYY-MM-DD HH24:MI:SS')) ...

Thanks,
Anupam

[kjaouhari]

Thanks guys, but I have still message error ...

Then I'm using an aggregator,
but It seems to not work correctly with timestamp, but it works good for other column type.

[kcbland]

You have not posted an error message. First we fix your SQL, but you switched to using a different method while we are wasting time on your SQL.

Since you have not posted the error message and we can't read your mind, I'm guessing you're getting a data type error. If you use the Aggregator stage with DATE or TIMESTAMP data type, just put the data into YYYY-MM-DD format and toggle the data types to VARCHAR as the Aggregator wants DATE in the internal format, which is stupid. If you change the metadata to VARCHAR for use in the Aggregator it won't complain.

Do us a favor and respond to our points. It's really difficult to help someone who isn't paying attention.

[kris007]

But you are converting date to char and then applying the MIN to char value
I think it should be.. TO_CHAR(MIN(PERIOD.PERSTR_0), 'YYYY-MM-DD HH24:MI:SS')) ...

It didn't work for me when I tried using what you have mentioned and hence I stuck to what I mentioned and it was working fine for me i.e. giving the desired results.

[kjaouhari]

Ok I'm agree with you that I'm not very explicit and I didn't mentionned the error message .

But It's not stupid to switch to using an other method, It can be interesting for Dsxians to know the different solution of a request ...

[kumar_s]

Ok I'm agree with you that I'm not very explicit and I didn't mentionned the error message .

But It's not stupid to switch to using an other method, It can be interesting for Dsxians to know the different solution of a request ...

HI kjaouhari,

It is no wehere mentioned as stupidity to switch between options. The all required is the exact requirement of yours, and the exact process that currently follow and the exact error mesage if at all you get.
So that it would be easy for the posters to judge and answer for your query.
Else you force them to make some wild assumptions, which will again wont help you.

[kcbland]

I think you didn't read my recent response carefully.

as the Aggregator wants DATE in the internal format, which is stupid.

I was commenting on the Aggregator stage requirement to process DATE values in the internal DS BASIC format.


As for this thread, your attention wandered, you posted one issue, didn't use our responses, then came up with new issues for a different set of problems and posted that. Since your new problems relate to trying something different, you made our answers irrelevant and uselss, thus wasting our time.