synchronization error in view

(0) Share

Report

Posted on by Riyas ahamed F

1,215

Hi guys,

Scenario : Calculate salary for each month, so that I created one view

below code:

public static str totalMth(Name _monthName)
    {
        str totalmonthName;

        totalmonthName   =('SELECT Isnull(Sum(Amountcur), 0)   '  
                      'FROM   MPPAYCYCLELINES          '  
                      'WHERE  DataAreaId = \'%1\'     '  
                      'AND partition = %2      '  
                      'AND MPPAYCYCLELINES.FROMDATE >= MONTH(Getdate()) = \'%3\'     '  
                      'AND MPPAYCYCLELINES.TODATE <= EOMONTH(Getdate()) = \'%4\' ' 
        SysComputedColumn::returnField(tableStr(MPEarningSummary_CTC), identifierStr(MPPayCycleLines), fieldStr(MPPayCycleLines, DataAreaId)),
        SysComputedColumn::returnField(tableStr(MPEarningSummary_CTC), identifierStr(MPPayCycleLines), fieldStr(MPPayCycleLines, Partition)),
        SysComputedColumn::returnField(tableStr(MPEarningSummary_CTC), identifierStr(MPPayCycleLines), fieldStr(MPPayCycleLines, FromDate)),
        SysComputedColumn::returnField(tableStr(MPEarningSummary_CTC), identifierStr(MPPayCycleLines), fieldStr(MPPayCycleLines, ToDate)));

        return strfmt(totalmonthName,_monthName);
    }

synchronization error in view.

4130.SQLError.PNG

As shown the picture above while a open in view its getting error.

Project build : No error.

Can you tell me what is says that error and what am i doing wrong ?

Thanks

I have the same question (0)

All responses (6)

Answers (1)

huijij 19,811 on at

Like (0)

Report
Hi Riyas,

Please try it:

'WHERE DataAreaId = \'%1\' ' 'AND partition = \'%2\' '

regards

Was this reply helpful? Yes No
Riyas ahamed F 1,215 on at

Like (0)

Report

Thanks for your replay judy jee,

Same i did it as you mentioned then i build project getting error.

Error:

As i mentioned while build its getting now. what can be issue and what could be the causes and resolutions?

Thanks.

Was this reply helpful? Yes No
Martin Dráb 237,976 Most Valuable Professional on at

Like (0)

Report

Your code has a huge logical flaw - you're calling getDate() at DB synchronization. This date will be used to generate your T-SQL code and therefore your view will always return values for the month of DB synchronization, not for the current month.

How you should design your query depends on your data model. Most importantly, may the period (between FromDate and ToDate) cover more than a single calendar month? If not, the most efficient solution may be grouping by MONTH(FromDate).

Was this reply helpful? Yes No
Riyas ahamed F 1,215 on at

Like (0)

Report

Thanks for your valuable replay sir,

Yes sir its from date and to date cover more than a single calendar month.

And in the complex SQL i know the function DatestartMonth and dateEndMonth in x++.

You're correct getDate() its current month in complex SQL. can you tell me sir how can I get datestartmonth and endMth in T-SQL?

Also what am i doing wrong as i mentioned above my code can you rewrite it again for me its help full sir.

Thanks

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,976 Most Valuable Professional on at

Like (1)

Report
I'm sorry, I misread your code. Using GetDate() in T-SQL is possible.

You can use EOMONTH() to get the end of month for a give date. One of the solutions for getting the first date of a month is using OEMONTH() to get the last date of the previous month and adding one day:

DATEADD(DAY, 1, EOMONTH(T1.MyDateField, -1))

Was this reply helpful? Yes No
Riyas ahamed F 1,215 on at

Like (0)

Report

Thank you very much your answer sir.

Was this reply helpful? Yes No