Analysis Service is sending invalid SQL

freewilly007 · Post by **freewilly007** » Wed 23 Jan 2008 20:03

I'm receiving SQL from analysis services. The query is below. The problem is that Postgres does not handle "Select CHAR(32)". Its trying to format some date data. Is there a change in the cartridge I could make soi that AS doesnt use this SQL? Thanks.

SELECT "dbo_tblDimCalendar"."dbo_tblDimCalendarddtDateTime0_0" AS "dbo_tblDimCalendarddtDateTime0_0","dbo_tblDimCalendar"."dbo_tblDimCalendardtxDoWDate0_1" AS "dbo_tblDimCalendardtxDoWDate0_1","dbo_tblDimCalendar"."dbo_tblDimCalendardtxSpDoWDate0_2" AS "dbo_tblDimCalendardtxSpDoWDate0_2","dbo_tblDimCalendar"."dbo_tblDimCalendardtxGerDoWDate0_3" AS "dbo_tblDimCalendardtxGerDoWDate0_3"
FROM
(

SELECT "ddtDateTime" AS "dbo_tblDimCalendarddtDateTime0_0",CONVERT(VARCHAR,"dbo"."tblDimCalendar"."cpdDayOfWeekName") + CHAR(32) + CHAR(45) + CHAR(32) + CONVERT(VARCHAR,"dbo"."tblDimCalendar"."ddtDateTime",101) AS "dbo_tblDimCalendardtxDoWDate0_1",CONVERT(VARCHAR,"dbo"."tblDimCalendar"."cpdSpanishDayOfWeekName") + CHAR(32) + CHAR(45) + CHAR(32) + CONVERT(VARCHAR,"dbo"."tblDimCalendar"."ddtDateTime",101) AS "dbo_tblDimCalendardtxSpDoWDate0_2",CONVERT(VARCHAR,"dbo"."tblDimCalendar"."cpdGermanDayOfWeekName") + CHAR(32) + CHAR(45) + CHAR(32) + CONVERT(VARCHAR,"dbo"."tblDimCalendar"."ddtDateTime",101) AS "dbo_tblDimCalendardtxGerDoWDate0_3","CALENDAR_KEY" AS "dbo_tblDimCalendarCALENDAR_KEY0_4"
FROM "dbo"."tblDimCalendar"
)
AS "dbo_tblDimCalendar"
WHERE
(

(
"dbo_tblDimCalendar"."dbo_tblDimCalendardtxDoWDate0_1" =
@Param1
)

)

Alexey.mdr · Post by **Alexey.mdr** » Thu 24 Jan 2008 10:57

Please send me a small test project to reproduce the problem.
It is desirable to use 'test' schema objects, otherwise include the
definition of your own database objects.
Do not use third party components.
If it is impossible for you to create the test project, send us a piece of
your code where the error occurs.

Could you please comment the following code, what do you expect from it?

Code: Select all

...
...CONVERT(VARCHAR,"dbo"."tblDimCalendar"."cpdDayOfWeekName") + CHAR(32) + CHAR(45) + CHAR(32) +...
...

freewilly007 · Post by **freewilly007** » Thu 24 Jan 2008 18:14

the output would be somthing like a string

"Friday - 10/12/1997"

So what MSAS is try to do is format a varchar column as described above. I will try to put together a simple test case for you. Thank you.

Alexey.mdr · Post by **Alexey.mdr** » Fri 25 Jan 2008 12:15

Ok. We are waiting for a sample.