hi,
i get error message Command is missing required clause
i try as under, but i need to insert the result at cursor or new table
SELECT divi, mac1, ;
SUM(IIF(MONTH(dat)=1 AND typ = 1, true2sec, 00000)) AS m1_m, ;
SUM(IIF(MONTH(dat)=1 AND typ = 2, true2sec, 00000)) AS m1_a,;
SUM(IIF(MONTH(dat)=2 AND typ = 1, true2sec, 00000)) AS m1_m, ;
SUM(IIF(MONTH(dat)=2 AND typ = 2, true2sec, 00000)) AS m1_a, ;
SUM(IIF(MONTH(dat)=3 AND typ = 1, true2sec, 00000)) AS m1_m, ;
SUM(IIF(MONTH(dat)=3 AND typ = 2, true2sec, 00000)) AS m1_a, ;
SUM(IIF(MONTH(dat)=4 AND typ = 1, true2sec, 00000)) AS m1_m, ;
SUM(IIF(MONTH(dat)=4 AND typ = 2, true2sec, 00000)) AS m1_a, ;
SUM(IIF(MONTH(dat)=5 AND typ = 1, true2sec, 00000)) AS m1_m, ;
SUM(IIF(MONTH(dat)=5 AND typ = 2, true2sec, 00000)) AS m1_a, ;
SUM(IIF(MONTH(dat)=6 AND typ = 1, true2sec, 00000)) AS m1_m, ;
SUM(IIF(MONTH(dat)=6 AND typ = 2, true2sec, 00000)) AS m1_a, ;
SUM(IIF(MONTH(dat)=7 AND typ = 1, true2sec, 00000)) AS m1_m, ;
SUM(IIF(MONTH(dat)=7 AND typ = 2, true2sec, 00000)) AS m1_a, ;
SUM(IIF(MONTH(dat)=8 AND typ = 1, true2sec, 00000)) AS m1_m, ;
SUM(IIF(MONTH(dat)=8 AND typ = 2, true2sec, 00000)) AS m1_a, ;
SUM(IIF(MONTH(dat)=9 AND typ = 1, true2sec, 00000)) AS m1_m, ;
SUM(IIF(MONTH(dat)=9 AND typ = 2, true2sec, 00000)) AS m1_a, ;
SUM(IIF(MONTH(dat)=10 AND typ = 1, true2sec, 00000)) AS m1_m, ;
SUM(IIF(MONTH(dat)=10 AND typ = 2, true2sec, 00000)) AS m1_a, ;
SUM(IIF(MONTH(dat)=11 AND typ = 1, true2sec, 00000)) AS m1_m, ;
SUM(IIF(MONTH(dat)=11 AND typ = 2, true2sec, 00000)) AS m1_a, ;
SUM(IIF(MONTH(dat)=12 AND typ = 1, true2sec, 000000)) AS m12_m, ;
SUM(IIF(MONTH(dat)=12 AND typ = 2, true2sec, 000000)) AS m12_a ;
FROM table1 ;
WHERE dat BETWEEN ldFirstDayOfTheYear AND ldLastDayOfTheYear ;
GROUP BY 1,2
>Here's a pseudo code for the query
>SELECT divi, mac1, ;
> SUM(IIF(MONTH(dat)=1 AND typ = 1, true2sec, 00000)) AS m1_m, ;
> SUM(IIF(MONTH(dat)=1 AND typ = 2, true2sec, 00000)) AS m1_a, ;
>...
> SUM(IIF(MONTH(dat)=12 AND typ = 1, true2sec, 000000)) AS m12_m, ;
> SUM(IIF(MONTH(dat)=12 AND typ = 2, true2sec, 000000)) AS m12_a ;
>FROM mytable ;
>WHERE dat BETWEEN ldFirstDayOfTheYear AND ldLastDayOfTheYear ;
>GROUP BY 1,2
>
>>
>>my table have fields
>>divi type character
>>dat type date && i need month(dat) as 1,2,3...
>>mac1 type character
>>typ type numaric as 1 mean the morning period 2 the aftrnon period
>>true type hh:mm
>>
>>i need to create cursor have result as under
>>
>>morning mean sum field true if typ=1
>>aftrnoon mean sum field true if typ=2
>>
>>
>>divi mac1 month 1 month2 ...........to month12
>> morning aftrnon morning aftrnoon
>>aa eo4 166:33 109:15 145:10 170:45 .........
>>aa eo3
>>bbb jo7
>>bbb jo6
>>www .
>>. .
>>. .
>>. .
>> ---------------------------------------------------------------
>>total 166:33.....................................
>>
>>this code under to sum field true as hh:mm
>>
>>SUM CTOT(true) - CTOT("00:00:00") to lnSec
>>SUM VAL(LEFT(true,2))*3600 + ;
>> VAL(SUBSTR(true,4,2))*60 + ;
>> VAL(RIGHT(true,2)) TO lnSec
>>lnSec11=lnSec
>>
>>true= PADL(INT(lnSec/3600),4, "0") + ":" + ;
>> PADL(INT((lnSec%3600)/60),2, "0")
>>>>
>>thanks
