SQL ODBC failure -Exceed Threshold of 3000 DB error DLL

[jposner]

OK. I have a report that works fine as is and highlights discrepancies between fields and our 18 digit grant code which is compile fromt he codes (but in not linke din the software and so this is my data validation). I have set-up Crystal alerts to identify onbly the discrepancies, but you cannot export an altert's results, so I want to create a report that just shows me what the alert does; however, crystal syntax is different than SQL and I cannot get my query to run without receivingf the error that it has exceeded the thresold of 3000 (and since this isn't MS Access I cannot just increase this setting; at leas tnot that I know of). here's the query, anyone see anything wrong with it?

Any help is much apprecaited!

Thanks

Joe


SELECT DISTINCT
EBase."EbFirstName", EBase."EbMiddleName", EBase."EbLastName", EBase."EbClock",
EPayrollBase."PrbPayDiv", EPayrollBase."PrbPayDept", EPayrollBase."PrbDistData",
EJob."EjLocation", EJob."EjJobCode",
EEmploy."EeCategory",
EDistribution."DieLineNo", EDistribution."DieDistData", EDistribution."DieDistPercent"
FROM
(EBASE LEFT OUTER JOIN EDISTRIBUTION ON EBASE.EBFLXID = EDISTRIBUTION.DIEFLXIDEB), EEMPLOY, JOBCODE, EPAYROLLBASE, EJOB
WHERE
EBASE.&quot;EBFLXID&quot; = EEMPLOY.&quot;EEFLXIDEB&quot; AND EBASE.&quot;EBFLXID&quot; = EPAYROLLBASE.&quot;PRBFLXIDEB&quot; AND EBASE.&quot;EBFLXID&quot; = EJOB.&quot;EJFLXIDEB&quot; AND EJOB.&quot;EJJOBCODE&quot; = JOBCODE.&quot;JBJOBCODE&quot; AND EBASE.&quot;EBFLAGEMP&quot; = 'Y' AND EJOB.&quot;EJDATEEND&quot; IS NULL AND EPAYROLLBASE.&quot;PRBDATEEND&quot; IS NULL AND EESTATUS <> 'TERMINATED' AND PRBPAYRLSTATUS <> 'T' AND
(EJob.EjJobCode <> Substring (EPayrollBase.PrbDistData,16,3)OR
(EEmploy.EeCategory = 'F' AND Substring (EPayrollBase.PrbDistData,14,2) <> '70')
OR (EEmploy.EeCategory = 'PFT'AND Substring (EPayrollBase.PrbDistData,14,2) <> '70')
OR (EEmploy.EeCategory = 'P' AND substring(Epayrollbase.PrbDistData,14,2) <> '71')
OR Substring (EPayrollBase.PrbDistData,9,2) <> EPayrollBase.PrbPayDept
OR Substring (EPayrollBase.PrbDistData,11,3) <> EJob.EjLocation)
OR
((Substring (EPayrollBase.PrbDistData,5,2) <> '91' AND Left(EPayrollBase.PrbPayDiv,1) <> 'S' AND Substring (EPayrollBase.PrbPayDiv,2,2) <> Substring (EPayrollBase.PrbDistData,7,2)))
OR ((Substring (EPayrollBase.PrbDistData,5,2) <> '97' AND Left(EPayrollBase.PrbPayDiv,1) <> 'L' AND Substring (EPayrollBase.PrbPayDiv,2,2) <> Substring (EPayrollBase.PrbDistData,7,2)))
OR ((Substring (EPayrollBase.PrbDistData,5,2) <> '90' AND Left(EPayrollBase.PrbPayDiv,1) <> 'P' AND Substring (EPayrollBase.PrbPayDiv,2,2) <> Substring (EPayrollBase.PrbDistData,7,2)))
OR ((Substring (EPayrollBase.PrbDistData,5,2) <> '20' AND Left(EPayrollBase.PrbPayDiv,1) <> 'O' AND Substring (EPayrollBase.PrbPayDiv,2,2) <> Substring (EPayrollBase.PrbDistData,7,2)))
OR ((Substring (EPayrollBase.PrbDistData,5,2) <> '10' AND Left(EPayrollBase.PrbPayDiv,1) <> 'A' AND Substring (EPayrollBase.PrbPayDiv,2,2) <> Substring (EPayrollBase.PrbDistData,7,2)))
OR ((Substring (EPayrollBase.PrbDistData,5,2) <> '44' AND Left(EPayrollBase.PrbPayDiv,1) <> 'D' AND Substring (EPayrollBase.PrbPayDiv,2,2) <> Substring (EPayrollBase.PrbDistData,7,2)))
OR ((Substring (EPayrollBase.PrbDistData,5,2) <> '31' AND Left(EPayrollBase.PrbPayDiv,1) <> 'G' AND Substring (EPayrollBase.PrbPayDiv,2,2) <> Substring (EPayrollBase.PrbDistData,7,2)))
OR ((Substring (EPayrollBase.PrbDistData,5,2) <> '21' AND Left(EPayrollBase.PrbPayDiv,1) <> 'F' AND Substring (EPayrollBase.PrbPayDiv,2,2) <> Substring (EPayrollBase.PrbDistData,7,2)))

 

[IdoMillet]

Not sure but:

1. qualify all your columns with their table names.
Here's where you don't do this:
-----------------------------------------------------
EESTATUS <> 'TERMINATED' AND PRBPAYRLSTATUS <> 'T'
-----------------------------------------------------

2. The length of your SELECT statement is approaching 3000 characters. Just in case this is the issue, declare short aliases for your tables:
-------------------------------------
...FROM (EBASE EB LEFT OUTER JOIN EDISTRIBUTION ED ON EB.EBFLXID = ED.DIEFLXIDEB), EEMPLOY EE, JOBCODE JO, EPAYROLLBASE EP, EJOB EJ ..
-------------------------------------
and use these short aliases everywhere else in the SELECT statement.

hth,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

If possible, create a View or SP to handle this.

Ido's suggestion might get you by the problem, but you may find that getting in the habit of basing reports on Views or SP's promotes reusability and minimizes maintenance. Set up the report once, if the underlying structure changes, just correct the view/sp, you won't have to modify and redistribute the reports.

-k

 

[jposner]

Thanks Ido. I made the correction to where I dropped off the table name prefix and shortened the table name (though it was about 2700 characters before and still didn't exceed 3000) but am still told it will exceed the threshold.

Synapsevampire, I am not very familiar with creating views within crystal. How would I go about that?

Also, does anyone know oif simpler way, such as someway of being able to export the results of an Alert into Excel

Basically I need to convert the criteria that the Alert does into the SQL box, so that instead of an Alert, it just IS the report.

I replaced Mid with Substring and instead of NOT I tried just making everything <> within () and a series of AND links (see Alert syntax below).

Any suggestions?

({EJob.EjJobCode} <> Mid ({EPayrollBase.PrbDistData},16,3)OR
({EEmploy.EeCategory} = 'F' AND Mid ({EPayrollBase.PrbDistData},14,2) <> '70')
OR ({EEmploy.EeCategory} = 'PFT'AND Mid ({EPayrollBase.PrbDistData},14,2) <> '70')
OR ({EEmploy.EeCategory} = 'P' AND mid({Epayrollbase.PrbDistData},14,2) <> '71')
OR Mid ({EPayrollBase.PrbDistData},9,2) <> {EPayrollBase.PrbPayDept}
OR Mid ({EPayrollBase.PrbDistData},11,3) <> {EJob.EjLocation})
OR Not((Mid ({EPayrollBase.PrbDistData},5,2) = '91' AND Left({EPayrollBase.PrbPayDiv},1) = 'S' AND Mid ({EPayrollBase.PrbPayDiv},2,2) = Mid ({EPayrollBase.PrbDistData},7,2)))
AND Not((Mid ({EPayrollBase.PrbDistData},5,2) = '97' AND Left({EPayrollBase.PrbPayDiv},1) = 'L' AND Mid ({EPayrollBase.PrbPayDiv},2,2) = Mid ({EPayrollBase.PrbDistData},7,2)))
AND NOT((Mid ({EPayrollBase.PrbDistData},5,2) = '90' AND Left({EPayrollBase.PrbPayDiv},1) = 'P' AND Mid ({EPayrollBase.PrbPayDiv},2,2) = Mid ({EPayrollBase.PrbDistData},7,2)))
AND NOT((Mid ({EPayrollBase.PrbDistData},5,2) = '20' AND Left({EPayrollBase.PrbPayDiv},1) = 'O' AND Mid ({EPayrollBase.PrbPayDiv},2,2) = Mid ({EPayrollBase.PrbDistData},7,2)))
AND NOT((Mid ({EPayrollBase.PrbDistData},5,2) = '10' AND Left({EPayrollBase.PrbPayDiv},1) = 'A' AND Mid ({EPayrollBase.PrbPayDiv},2,2) = Mid ({EPayrollBase.PrbDistData},7,2)))
AND NOT((Mid ({EPayrollBase.PrbDistData},5,2) = '44' AND Left({EPayrollBase.PrbPayDiv},1) = 'D' AND Mid ({EPayrollBase.PrbPayDiv},2,2) = Mid ({EPayrollBase.PrbDistData},7,2)))
AND NOT((Mid ({EPayrollBase.PrbDistData},5,2) = '31' AND Left({EPayrollBase.PrbPayDiv},1) = 'G' AND Mid ({EPayrollBase.PrbPayDiv},2,2) = Mid ({EPayrollBase.PrbDistData},7,2)))
AND NOT((Mid ({EPayrollBase.PrbDistData},5,2) = '21' AND Left({EPayrollBase.PrbPayDiv},1) = 'F' AND Mid ({EPayrollBase.PrbPayDiv},2,2) = Mid ({EPayrollBase.PrbDistData},7,2)))

 

[IdoMillet]

VIEWs are created in the DBMS - not in Crystal.
In Access they are called Queries.
Crystal treats them as Tables.

Did you consider creating On Demand subreports to display (and export) the alert data?

Do you want user intervention in exporting the alerts or do you want to automate alert exports and notifications?

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[jposner]

Hi Ito,

That's what I figured. I can't create Views because this is linked to my HRIS. I do not have the ability to generate views in the typical manner one would in an Access DB or a SQL Server (though this is a SQL Server DB, but it is hosted by our vendor and I cannot even establish an ODBC connection to it).

A subreport is still a report in and of itself so wouldn't I still need to be able to fix this query in order for it to work?

Most users do not have the ability to enter Crystal for modifications; they only get a Crystal preview which allows them to export or to print data.

Exporting this alert would be done by the one user in the Accounting group that has rights to enter into Crystal who would then disseminate this to his staff.

 

[synapsevampire]

Perhaps your vendor will allow you to send them the SQL to base the View/SP on.

You have some form of connectivity to the database, otherwise Crystal couldn't query it. So there must be a user set up on the SQL Server, perhaps you can link the tables to a Access database and do the real database work in there, then base your report on the Query in Access.

In either case, yes, you would have to repair your query to base a subreport on it.

-k