Sytntax Error

[Anonymous]

Hello,

LOAD application_no,

     paycode as %PAYCODE,

     request_id,

//     request_date,

     date(leave_from,'DD-MMM-YYYY') as leave_from,

     year(leave_from) as LeaveYear,

     month(leave_from) as LeaveMonth,

     day(leave_from) as LeaveDay,

     leave_to,

     //leave_to,

     leavecode as LEAVECODE,

//     halfday,

     leavedays,

     //if(leavecode='CL', sum(leavedays),'0') as CLAccrued,

     Sum(if(leavecode='CL', leavedays)),

     userremarks

FROM

$(Vrawqvdspath)\LeaveRequest.qvd

(qvd) Group by paycode, month(leave_from);

Can somebody tell me what is the error in this code?

It is giving me error.

Regards,

Balraj

[jonathandienst]

Hi

When using grouping, every field not in an aggregation function (Sum(if...) in your case) must be listed in the group by clause

HTH

Jonathan

[Anonymous]

Hello,

You meant to say, leavecode and leavedays should be listed in grouping?

[mjayachandran]

Try this"

LOAD application_no,

     paycode as %PAYCODE,

     request_id,

//     request_date,

     date(leave_from,'DD-MMM-YYYY') as leave_from,

     year(leave_from) as LeaveYear,

     month(leave_from) as LeaveMonth,

     day(leave_from) as LeaveDay,

     leave_to,

     //leave_to,

     leavecode as LEAVECODE,

//     halfday,

     leavedays,

     //if(leavecode='CL', sum(leavedays),'0') as CLAccrued,

     Sum(if(leavecode='CL', leavedays)),

     userremarks

FROM

$(Vrawqvdspath)\LeaveRequest.qvd

(qvd)

Group by     paycode,

            month(leave_from),

            application_no,

            request_id,

             date(leave_from,'DD-MMM-YYYY'),

             year(leave_from),

             day(leave_from),

             leave_to,

             leavecode

             leavedays,

             userremarks  ;

So, In Groupby you should have all the field which you are not aggregrating

[its_anandrjs]

Hi,

When using Group by clause in the load you have to introduce all the fields available in the table see example otherwise use resident table load statement.

LOAD

     application_no,

     paycode as %PAYCODE,

     request_id,

//   request_date,

     date(leave_from,'DD-MMM-YYYY') as leave_from,

     year(leave_from) as LeaveYear,

     month(leave_from) as LeaveMonth,

     day(leave_from) as LeaveDay,

     leave_to,

     //leave_to,

     leavecode as LEAVECODE,

//   halfday,

     leavedays,

     //if(leavecode='CL', sum(leavedays),'0') as CLAccrued,

     Sum(if(leavecode='CL', leavedays)),

     userremarks

FROM

$(Vrawqvdspath)\LeaveRequest.qvd

(qvd) Group by 

     application_no,

     paycode,

     request_id,

     date(leave_from,'DD-MMM-YYYY'),

     year(leave_from),

     month(leave_from),

     day(leave_from),

     leave_to,

     leavecode,

     leavedays,

     userremarks;

[#6]

What columns (other than aggregation function)  you are selecting in Load statement must be present in group by Clause.

[#7]

Apart from the above, one more cent is

date(leave_from,'DD-MMM-YYYY') as leave_from, -- is wrong

it should be

date#(leave_from,'DD-MMM-YYYY') as leave_from,

date#() is date formatting function. Please refer to Reference Manual

I guess you wanted to format the "leave_from" field in your load.

Thanks

Sri

[Anonymous]

Anand,

Should I include leavecode and leavedays as well, as you mentioned?

[Anonymous]

Thanks Anand, Its done.

[Anonymous]

Thanks Mahesh, Its done.