Hello,

I have a complex query that I need to use for a report, so have created a view on the SQL server to bring stuff together into a simple "ACCOUNTID, FIELD1, FIELD2, FIELD3, ..., FIELDn" flat 'table' for the purposes of Crystal Reports.

The View performs some simple selects from a custom SLX table, and the results look something like this:

I think the SLX Provider is mangling the results somehow, but it's very strange. The view is included in the SLX Database Manager, so I can link to it in SLX groups etc, and if I do so the numbers come out different.

Instead of

A5FRNA000001  |  7  |  7

I'll perhaps get

A5FRNA000001  |  10  |  4

The same applies when getting data for a Crystal Report.

What on earth is the SLX Provider doing? I've had odd date-issues before, where it's adding an hour or two to the datetimes compared to SQL, but that's expected.

Here's the query for the view, but I don't see why (or even how) the provider gets anywhere near this, as SQL should just be returning the result-set - the internals are irrelevent.

SELECT
    C.ACCOUNTID, 
    SUM(CHARINDEX('Group Meeting', E.EVENTTYPE)) AS TypeIsGroup, 
    SUM(CASE WHEN E.EVENTTYPE = 'Group Meeting' AND E.EVENTDATE < DATEADD(month, - 6, CONVERT(varchar(7), GETDATE(), 20) + '-01 00:00:00') THEN 1 ELSE 0 END) AS TypeIsGroupFirstHalf,
    SUM(CASE WHEN E.EVENTTYPE = 'Group Meeting' AND E.EVENTDATE >= DATEADD(month, - 6, CONVERT(varchar(7), GETDATE(), 20) + '-01 00:00:00') THEN 1 ELSE 0 END) AS TypeIsGroupSecondHalf, 
    SUM(CASE WHEN E.EVENTTYPE = 'Seminar' THEN 1 ELSE 0 END) AS TypeIsSeminar, 
    SUM(CASE WHEN E.EVENTTYPE = 'Conference' THEN 1 ELSE 0 END) AS TypeIsConference, 
    SUM(CASE WHEN E.EVENTTYPE = 'Managed Event' THEN 1 ELSE 0 END) AS TypeIsManaged, 
    SUM(CASE WHEN E.EVENTTYPE = 'Training Course' THEN 1 ELSE 0 END) AS TypeIsCourse, 
    SUM(CASE WHEN E.EVENTTYPE = 'Concept Viability' THEN 1 ELSE 0 END) AS TypeIsConceptViability, 
    SUM(CASE WHEN E.EVENTTYPE = 'Corporate Hospitality' THEN 1 ELSE 0 END) AS TypeIsCorpHosp, 
    SUM(CASE WHEN E.EVENTTYPE = 'Lunch' OR E.EVENTTYPE = 'Dinner' THEN 1 ELSE 0 END) AS TypeIsLunch, 
    SUM(CASE WHEN E.EVENTTYPE = 'Reception' OR E.EVENTTYPE = 'Concept Viability' THEN 1 ELSE 0 END) AS TypeIsReception
FROM
    sysdba.EventEnrollments AS E 
    INNER JOIN sysdba.CONTACT AS C ON E.CONTACTID = C.CONTACTID
WHERE
    (LOWER(E.STATUS) NOT IN ('cancelled', 'did not attend')) 
    AND (
        E.EVENTDATE BETWEEN DATEADD(year, - 1, CONVERT(varchar(7), GETDATE(), 20) + '-01 00:00:00') 
        AND DATEADD(day, - 1, CONVERT(varchar(7), GETDATE(), 20) + '-01 23:59:59')
    )
GROUP BY C.ACCOUNTID

The provider will attempt to add Security by Joining To the Secrights table.

To prove if that is the case, run the Statement as Admin, it should pass through with no Security Parsing, so you should see the same results.

That said, you could use the SLX Profiler to take a peek at how the Statement is altered by the Provider, which may help you understand how you may need to modify the query. It will show you the SQL Statement as it received it and the SQL Statement as it executes against the DB.

My take is that, when the provider adds the Security Joins, the User running the statement has many Security access to the Same record (nothing necessarily wrong), so it multiplies the number of rows for that record.

I ran the report with SLXProfiler going, and the queries executed seem to be as expected, and copy'n'pasting those into SQL give the correct results - but the results in SLX are different. If I run the report against "current record" or "current group" I do get links to SECRIGHTS, but with "All records" I don't. Either way it fails in the same way though.

If it was extra joins causing the problem, I'd expect multiple records per account, not fiddling with the values in the records.

Stumped

---------- Client SQL ---------
SELECT 
	"MyView"."USERNAME", "MyView"."INTERNALACCOUNTNO", "MyView"."ACCOUNT", "MyView"."TYPE", 
	"MyView"."SUBSCRIPTION_FEE", "MyView"."ACCOUNT_CATEGORY", "MyView"."STATUS", "MyView"."RESPONSIBLE_TEAM", 
	"MyView"."EscrowCounter", "MyView"."RoomCounter", "MyView"."DocmanCounter", "MyView"."HRCounter", 
	"MyViewEvents"."TypeIsCorpHosp", "MyViewEvents"."TypeIsReception", "MyViewEvents"."TypeIsSeminar", 
	"MyViewEvents"."TypeIsConference", "MyViewEvents"."TypeIsManaged", "MyViewEvents"."TypeIsLunch", 
	"MyViewEvents"."TypeIsCourse", "MyViewEvents"."TypeIsGroupFirstHalf", "MyViewEvents"."TypeIsGroupSecondHalf" 
FROM   "sysdba"."MyView" "MyView" 
LEFT OUTER JOIN "sysdba"."MyViewEvents" "MyViewEvents" ON "MyView"."ACCOUNTID"="MyViewEvents"."ACCOUNTID" 
ORDER BY "MyView"."USERNAME", "MyView"."ACCOUNT"

---------- Executed SQL ----------
SELECT 
	"MyView"."USERNAME", "MyView"."INTERNALACCOUNTNO", "MyView"."ACCOUNT", "MyView"."TYPE", 
	"MyView"."SUBSCRIPTION_FEE", "MyView"."ACCOUNT_CATEGORY", "MyView"."STATUS", "MyView"."RESPONSIBLE_TEAM", 
	"MyView"."EscrowCounter", "MyView"."RoomCounter", "MyView"."DocmanCounter", "MyView"."HRCounter", 
	"MyViewEvents"."TypeIsCorpHosp", "MyViewEvents"."TypeIsReception", "MyViewEvents"."TypeIsSeminar", 
	"MyViewEvents"."TypeIsConference", "MyViewEvents"."TypeIsManaged", "MyViewEvents"."TypeIsLunch", 
	"MyViewEvents"."TypeIsCourse", "MyViewEvents"."TypeIsGroupFirstHalf", "MyViewEvents"."TypeIsGroupSecondHalf" 
FROM   "sysdba"."MyView" "MyView" 
LEFT OUTER JOIN "sysdba"."MyViewEvents" "MyViewEvents" ON "MyView"."ACCOUNTID"="MyViewEvents"."ACCOUNTID"  
ORDER BY "MyView"."USERNAME", "MyView"."ACCOUNT"

Mark,

  The query that you show on your reply is different than the one you show on your original post.

  I can tell the one from your reply will not be modified since it has no Account or Contact table.

  The query from your original Post did have a Reference to the Contact table, which would've been modified by the Parser.

Sorry, I should have made it clearer that the first query is the contents of the view, not the query being sent by SLX.

SLX should basically be doing "SELECT {fieldlist} FROM MyView"

I'd use INNER JOINS.....

Also, did you run the Same Query on SLX Administrator? Just thinking that it could be possible that the Report is giving you the Wrong Results.

Have you verified that the Report is pointing to the Same SQL Database?

Check the Alias that the Report is pointing to, and verify it in Connection Manager.

It could be possible that you are pointing to a different copy of the DB that has different data values (and this has happened to me several times when hosting DEV and Production Databases on the Same DB Server).

@RJ: inner joins? I know what data I want to get out, and an inner join would not be it.

@Raul: Yeah, unfortunately I've checked the db. Our training/dev db is so far behind our live in terms of when it was last cloned, that there wouldn't be any data really.

I've run the SQL query in the SQL Management Studio and get correct results. If I link the view in an SLX group, I get incorrect results. If I execute the query in Admin I get incorrect results. If the query in a test app it fails. Basically, any way I can think of to run it through the provider fails, and any way I run it through SQL directly is fine.

Here's a quick test-app:

using System;
using System.Data.OleDb;

namespace TestSLX
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnectionStringSLX = String.Format(
                "Provider=SLXOLEDB.1;Data Source={0};Initial Catalog={1};User ID={2}assword={3};Extended Properties=LOG=ON;Connect Timeout=5;",
                "SLX_SERVER", "SLX_DATABASE_NAME", "SLXUSERNAME", "SLXPASSWORD");

            string ConnectionStringSQL = String.Format(
                "Provider=SQLOLEDB;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;",
                "SQL_SERVER", "SLX_DATABASE_NAME");

            string q = "SELECT * FROM sysdba.Report_BenefitsUtilisationEvents WHERE ACCOUNTID='A5FRNA00013Y'";


            // SLX
            using (OleDbConnection con = new OleDbConnection(ConnectionStringSLX))
            {
                con.Open();
                using (OleDbCommand objCommand = new OleDbCommand(q, con))
                {
                    using (OleDbDataReader r = objCommand.ExecuteReader())
                    {
                        while (r.Read())
                        {
                            Console.WriteLine("SLX: " + r.GetString(0) + ", " + r.GetInt32(1) + ", " + r.GetInt32(2) + ", " + r.GetInt32(3) + ", " + r.GetInt32(4));
                        }
                    }
                }
                con.Close();
            }

            // SQL
            using (OleDbConnection con = new OleDbConnection(ConnectionStringSQL))
            {
                con.Open();
                using (OleDbCommand objCommand = new OleDbCommand(q, con))
                {
                    using (OleDbDataReader r = objCommand.ExecuteReader())
                    {
                        while (r.Read())
                        {
                            Console.WriteLine("SQL: " + r.GetString(0) + ", " + r.GetInt32(1) + ", " + r.GetInt32(2) + ", " + r.GetInt32(3) + ", " + r.GetInt32(4));
                        }
                    }
                }
                con.Close();
            }

            /* results are:
            
            SLX: A5FRNA00013Y, 14, 3, 11, 2
            SQL: A5FRNA00013Y, 14, 7,  7, 2
            
             */
        }
    }
}

The provider gets really wierd about dates due to the GMT conversions. I find it will work best if you keep all of your date operations at the SQL server level instead of trying to do it the way you are.

My recommendation is to switch from doing DateAdds to construct your dates to doing DateDiffs and then working with the result. This way all of your dates are handled at the SQL server level and it keeps the provider out of the picture.

so this:

  SUM(CASE WHEN E.EVENTTYPE = 'Group Meeting' AND E.EVENTDATE < DATEADD(month, - 6, CONVERT(varchar(7), GETDATE(), 20) + '-01 00:00:00') THEN 1 ELSE 0 END) AS TypeIsGroupFirstHalf,

becomes something more like this: (pardon me if i flipped the dates, but you get the idea)

  SUM(CASE WHEN E.EVENTTYPE = 'Group Meeting' AND DATEDIFF(mm,  E.EVENTDATE, GETUTCDATE() > 6) THEN 1 ELSE 0 END) AS TypeIsGroupFirstHalf,

I have had about zero success trying to construct dates and do provider based SQL queries on them, however if you deconstruct them into the components you care about and let SQL handle it - you should be fine.

ws

Since when are views executed by the Provider? There are no dates in the query sent to SQL.

Hmmm... must have missed that part.

That being said - i still think you should try what i recommended here. I've spent way too much time on these kinds of date issues lately, and this is what i have experienced to be the best working solution. I have just been finding that converting dates to/from strings has just not been as reliable as I would like them to be.

ws

I get that, and I gave up on dates through the provider some years ago - what I'm saying here is that all the date-magic is in a SQL view - so I don't see how the provider is getting anywhere near it. I am doing DateAdds in on the SQL side, not in SLX. The query executed by SLX, and hence the provider, is just "SELECT * FROM MyView".

I will hard-code the date in the view, to see if that sheds light on anything. I've only seen the provider mangling dates by a few hours before, due to the DST/UCT (or whatever it is) standard datetime adjustments affecting a date with a 00:00:00 time rolling back to the previous day - these date ranges span days, if not months, so it's particularly odd.

I'll also try raising it to SLX themselves, but the only answer I ever get from them is "upgrade".