Query shows no results in MSTR but OK against DB

[HrvojeSunjic]

Hi,
this might not be a high profile question, but it is giving me a bit of a headache (the temperatures in Zagreb, Croatia are in the 30 Celsius range, my brain may just be stuck), I appreciate any help you can provide.
To get to the point:
a MicroStrategy report produces the following query:

select a11.LKC_ID ID,
max(a13.LOCATION_NAME) LOCATION_NAME,
sum(a11.NUMBER) WJXBFS1
from SP1_FL_PERSONEL a11,
SPD_D_TIME a12,
SPD_D_LOCATIONS a13
where a11.TIME_ID_YYYY_MM_DD = a12.ID_YYYY_MM_DD and
a11.LKC_ID = a13.ID
and a12.DATUM <= To_Date('05-jun-03')
group by a11.LKC_ID

against Oracle. When MicroStrategy executes the report, no data is returned. When the same report is run directly against Oracle (using TOAD), it returns the correct results. There are no filters, prompts or other objects that could limit the result set. The only prompt is used to determine the end of the period (and results in the '05-jun-03' condition in the query).

Any ideas?
Thanks,

 

[krisav]

This only thing I am ale to see the to make it as
to_date('05-jun-03','mm/dd/rr'). The Toad has a setting of RR format i.e anything less that 50 to be considered as 2050.

-- Krishnan

 

[nlim]

does the query work in MSTR without the condition...that will help isolate whether the issue is in the where clause.

There might also be a date setting issue. I guess 05-Jun-03 could mean either June 3rd, 2005 or June 5th 2003. Check your default dat e settings.

 

[HrvojeSunjic]

nlim,
yes, the resuts are ok without the condition.

 

[krisav]

correction
This only thing I am able to see is to make it as
to_date('05-jun-03','dd-mon-rr'). The Toad has a setting of RR format i.e anything less that 50 to be considered as 2050.

 

[nlim]

check your date settings, I'm guessing it's still set for US default which tends to be MMDDYYYY. Then call tech support...

 

[HrvojeSunjic]

Thanks nlim, krisav.
I have checked all the date format related settings (regional settings etc.), both on the server side, DB side and client side, but the problem persists. I have contacted tech support.

 

[omarmtz]

Where do you run the report, in web or in Desktop???

 

[HrvojeSunjic]

omarmtz,
I run it in desktop, the report has not been tried on Web (not implemented in this phase).

 

[NaughtyVicar]

Have you checked the basics such as the report timeout limit, max number of rows governing?

Also, have you properly set up your heterogenous column mapping (a11.LKC_ID = a13.ID)?

 

[HrvojeSunjic]

NaugtyVicar,
as the rest of the team is on holiday right now (including the developer of the project and report), I can only say this from memory, but
a. the only VLDB settings that we fiddled with are Join settings, and the project level settings for other timeouts are fine, the report does not run long. There is no error, just an empty result set.
b. will check the heterogeneous mapping, but I assume it is ok, since the SQL against the database directly (SQL+, TOAD) gives the correct results. This would, to me, indicate that the attribute definitions and relationships are ok.
Generally,though, I agree - checking the basics should be the first step, preferrably before posting to this forum.

 

[uptnogd]

We had this problem once and it turned out to be that the date attribute was corrupted some how. We created a new date attribute and used it in the report and the issue dissapeared. We never did figure out the reason why it happened.