OK - this is the kind of crap which drives me over the ledge. Maybe someone can talk me down
I have two IDENTICAL BAQs.
One on a TestDB, One on a LiveDB. Same SW level server/client on both.
TestDB returns a BAD SQL - LiveDB returns a result set as expected.
The KEY to this eSNAFU is a calculated field. The purpose of which is to derive a date conditionally from a DESC field. When ‘dmtuser’ is the user the DESC field will have conversion text with date appended. Desc=“Converted on 06/02/2019”
I know the data is different - but really, the scenarios of data are present in both DBs. That is -
there are records which match dmtuser and some which don’t.
there are records with DESC text and some with no DESC text .
Here is the QueryPhase (exactly same on both systems)
select
[CheckHed].[Company] as [CheckHed_Company],
[PayMethod].[Name] as [PayMethod_Name],
[CheckHed].[BankAcctID] as [CheckHed_BankAcctID],
[BankAcct].[Description] as [BankAcct_Description],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name],
[CheckHed].[CheckNum] as [CheckHed_CheckNum],
[CheckHed].[CheckDate] as [CheckHed_CheckDate],
[CheckHed].[CheckAmt] as [CheckHed_CheckAmt],
[CheckHed].[ClearedCheck] as [CheckHed_ClearedCheck],
[CheckHed].[ClearedAmt] as [CheckHed_ClearedAmt],
[CheckHed].[ClearedDate] as [CheckHed_ClearedDate],
(case
when CheckHed.EntryPerson=‘dmtuser’ then convert(date, substring(APTran.Description, len(APTran.Description)-9 ,10 ), 101)
else CheckHed.CheckDate
end) as [Calculated_CutOverCheckDate],
[APTran].[Description] as [APTran_Description]
from Erp.CheckHed as CheckHed
left outer join Erp.PayMethod as PayMethod on
CheckHed.Company = PayMethod.Company
and CheckHed.PMUID = PayMethod.PMUID
left outer join Erp.Vendor as Vendor on
CheckHed.Company = Vendor.Company
and CheckHed.VendorNum = Vendor.VendorNum
left outer join Erp.BankAcct as BankAcct on
CheckHed.Company = BankAcct.Company
and CheckHed.BankAcctID = BankAcct.BankAcctID
left outer join Erp.APTran as APTran on
CheckHed.Company = APTran.Company
and CheckHed.HeadNum = APTran.HeadNum
and CheckHed.CheckNum = APTran.CheckNum
where (CheckHed.Posted = 1)
order by PayMethod.Name
anyone have an idea for this inconsistency? it’s making me drink
Do you have access to the server Epicor is running on? If you look in Event Viewer under Applications and Services Logs > Epicor App Server, it will give you details about the SQL error. “Bad SQL Statement” is just the generic error Epicor gives when something went wrong when running the query.
I’m willing to bet the convert is failing because of a record that exists in the test DB and not the live DB. Something like an out of range date.
Also take into consideration on any of the clauses where a value may be null. SQL results are random until the sort/order by/group by clauses are applied so the query engine will return results in a random order until that time. That may be why you get a sporadic ‘bad sql’ error.
Most of the time this is my problem when I see ‘bad sql’
thanks - but no luck. Your suggested change produce the same error results
(even though SYNTAX check is good)
case
when CheckHed.EntryPerson=‘dmtuser’ and len(APTran.Description) > 10 then convert(date, substring(APTran.Description, len(APTran.Description)-9 ,10 ), 101)
else CheckHed.CheckDate
end
1 - Have you checked the log file mentioned above?
2 - If I had to guess, then I would say that the substring result is not always convertible to a date
otherwise the rest of the query looks good. For a test, just remove that calculated field.
My standard diagnostic procedure is to remove join and fields in groups until the query works and then start putting them back in one by one. Sometimes the brute-force method is the only way when the answer isn’t obvious or in the log file.
if you want to use this convert string to date function your string should comes on xx/xx/xxxx format all the time, if you only need the result to be read then remove the conversion statement.
OK! We are on the way to success. I’m stepping back in from the ledge.
Problem was determined to be BAD DATA
Specifically a couple records in the bad DB - had a DESC field which could not DATE CONVERT
(and since the LIVE DB has clean data - they did not exist there, although i thought they did)
So with everyone’s helpful suggestions, i will develop fields to test/correct for the INVALID date condition without causing an error.
Jose showed me a neat debugging method - which is to copy the SQL command from the BAQ and post in SQL Mgr to test and see what error is actually produced. nice trick. Thank you Jose.
And thank you Tim - you were right to seek better info on the error.
Mike and Al, .you were correct in your analysis. it came down to bad data. (which makes sense) Thank You
And just to tie this up neatly with a bow, I will post the result when done.
OK - here is the successful result SQL. Pardon my tendency to overcode (ie. use too many fields)
Works like a charm - no matter what the data does.
NOTE: this assumes there is, conditionally, a character string at the end of the APTran.Description field which is formatted as a date - MM/DD/YYYY
select
[CheckHed].[Company] as [CheckHed_Company],
[PayMethod].[Name] as [PayMethod_Name],
[CheckHed].[BankAcctID] as [CheckHed_BankAcctID],
[BankAcct].[Description] as [BankAcct_Description],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name],
[CheckHed].[CheckNum] as [CheckHed_CheckNum],
[CheckHed].[CheckDate] as [CheckHed_CheckDate],
[CheckHed].[CheckAmt] as [CheckHed_CheckAmt],
[CheckHed].[ClearedCheck] as [CheckHed_ClearedCheck],
[CheckHed].[ClearedAmt] as [CheckHed_ClearedAmt],
[CheckHed].[ClearedDate] as [CheckHed_ClearedDate],
(case
when CheckHed.EntryPerson='dmtuser' and CODNumeric=1 then convert(date,substring(APTran.Description, len(APTran.Description)-9 ,10 ), 101)
else CheckHed.CheckDate end) as [Calculated_CODDate],
(case
when CheckHed.EntryPerson='dmtuser' then replace(substring(APTran.Description, len(APTran.Description)-9 ,10),'/','')
else substring(convert(varchar,CheckHed.CheckDate , 101),-3,4) end) as [Calculated_CODDigits],
(isnumeric(CODDigits)) as [Calculated_CODNumeric],
[APTran].[Description] as [APTran_Description],
[CheckHed].[EntryPerson] as [CheckHed_EntryPerson]
from Erp.CheckHed as CheckHed
left outer join Erp.PayMethod as PayMethod on
CheckHed.Company = PayMethod.Company
and CheckHed.PMUID = PayMethod.PMUID
left outer join Erp.Vendor as Vendor on
CheckHed.Company = Vendor.Company
and CheckHed.VendorNum = Vendor.VendorNum
left outer join Erp.BankAcct as BankAcct on
CheckHed.Company = BankAcct.Company
and CheckHed.BankAcctID = BankAcct.BankAcctID
left outer join Erp.APTran as APTran on
CheckHed.Company = APTran.Company
and CheckHed.HeadNum = APTran.HeadNum
and CheckHed.CheckNum = APTran.CheckNum
where (CheckHed.Posted = 1)
order by PayMethod.Name
