Custom SQL Statement in Business Objects, Web Intelligence

[marckssg]

Hi,

I'm trying to build a custom SQL statement in Business Objects and thanks to the error message Webi generates can't tell if I'm making a school boy error with the SQL statement or trying to get Webi to do something it can't do here's my statement;

SELECT
Incident_Number ,
Fields_Changed,
dbo.fn_adjusted_date( HPD_HelpDesk_AuditLogSystem.Audit_Date),
Log

FROM
HPD_Help_Desk
JOIN HPD_HelpDesk_AuditLogSystem ON HPD_Help_Desk.Entry_ID = HPD_HelpDesk_AuditLogSystem.Original_Request_ID
JOIN
(SELECT HPD_Help_Desk.Incident_Number, min (dbo.fn_adjusted_date( HPD_HelpDesk_AuditLogSystem.Audit_Date)) AS Audit_Date
FROM HPD_Help_Desk
JOIN HPD_HelpDesk_AuditLogSystem ON HPD_Help_Desk.Entry_ID = HPD_HelpDesk_AuditLogSystem.Original_Request_ID
WHERE
Incident_Number = 'INC000001234567' and
Fields_Changed like '%Assigned Group%'
GROUP BY
Incident_Number) AS HPA ON HPD_Help_Desk.Incident_Number = HPA.Incident_Number AND dbo.fn_adjusted_date(HPD_HelpDesk_AuditLogSystem.Audit_Date) = HPA.Audit_Date

It works if I try and link the subquery on the just the Audit_Date, but not on the Incident. The sub query runs fine on its own.

Been looking at it so long I can't see the wood for the trees so sure its me missing something simple, thanks for any advice.


Cheers

Marc

 

[philhege]

What do you get in return when it doesn't work? An error? An unexpected or empty result set?

-----------
With business clients like mine, you'd be better off herding cats.

 

[marckssg]

Nothing helpful I'm affraid, it seems the error message in Webi is pretty generic;

A database error occured. The database error test is;
[Microsoft][ODBC SQL Server Drive][SQL Server]Statement(s) could not be prepared (WIS10901)


I can't test the SQL outside of Webi as I don't have access to SQL Server etc and nobody else here in the office does. But I've tested the individual elements and both the subquery and main query run fine in issolation. So I've either done something simple and messed up the join of the Subquery or Webi can't handle joining a sub query on two fields, but I can't find that mentioned anywhere so presume it can.


Cheers

Marc

 

[marckssg]

Seemed to have fixed it, it appears in Webi it will only run if you alias all the fields in the sub query.

Cheers

Marc