Error: Sage Accpac - Sales Master 3-0 PVS and SQL

From Sage Intelligence Knowledgebase
Jump to: navigation, search

The period expression in the container displays the periods 1-9 as single digits and prevents the order of periods from being displayed in calendar order.


SYMPTOMS


The following is an example of an error that may occur:

If using a lookup on the ‘Period’ expression, the periods will not display in calendar order.


CAUSE


The periods 1 – 9 display as single digits and since they are joined along with the year they are converted to a text string. Sorting numbers in text format does not display in the preferred method of calendar order in this situation.


RESOLUTION/WORKAROUND


Download the latest template being:

Sales Master 3-2 (AE-SQL) OR Sales Master 3-2 (AE-PVS)


The changes made were as follows:


1. Sales Master 3-0 container: The expression “period” has been changed as follows:


SQL:

Expression source:

[OESHDT].[YR]+'-'+ CASE WHEN LEN([OESHDT].[PERIOD])=1 THEN CAST(0 AS VARCHAR)++ CAST([OESHDT].[PERIOD] AS VARCHAR) ELSE CAST([OESHDT].[PERIOD] AS VARCHAR) END


Lookup Sql Select statement:


SELECT DISTINCT [OESHDT].[YR]+'-'+ CASE WHEN LEN([OESHDT].[PERIOD])=1 THEN CAST(0 AS VARCHAR)++ CAST([OESHDT].[PERIOD] AS VARCHAR) ELSE CAST([OESHDT].[PERIOD] AS VARCHAR) END FROM [OESHDT] ORDER BY [OESHDT].[YR]+'-'+ CASE WHEN LEN([OESHDT].[PERIOD])=1 THEN CAST(0 AS VARCHAR)++ CAST([OESHDT].[PERIOD] AS VARCHAR) ELSE CAST([OESHDT].[PERIOD] AS VARCHAR) END


PVS:

Expression source:

"OESHDT"."YR"+'-'+ CASE WHEN LENGTH("OESHDT"."PERIOD")=1 THEN CAST(0 AS VARCHAR)++CAST("OESHDT"."PERIOD" AS VARCHAR) ELSE CAST("OESHDT"."PERIOD" AS VARCHAR) END


Lookup Sql Select statement:


SELECT DISTINCT "OESHDT"."YR"+'-'+ CASE WHEN LENGTH("OESHDT"."PERIOD")=1 THEN CAST(0 AS VARCHAR)++CAST("OESHDT"."PERIOD" AS VARCHAR) ELSE CAST("OESHDT"."PERIOD" AS VARCHAR) END FROM "OESHDT" ORDER BY "OESHDT"."YR"+'-'+ CASE WHEN LENGTH("OESHDT"."PERIOD")=1 THEN CAST(0 AS VARCHAR)++CAST("OESHDT"."PERIOD" AS VARCHAR) ELSE CAST("OESHDT"."PERIOD" AS VARCHAR) END


APPLIES TO


Sales Master 3-0 (AE-PVS)

Sales Master 3-1 (AE-PVS)

Sales Master 3-0 (AE-SQL)