Test does not show up

Hello everyone,

I’m working on Analyze in BAQ. When I pressed the Analyze button, I got the message “Syntax is OK”. However, when you press the Test button, no data appears with the error message below.
What’s the problem? Please help.
Thanks in advance!


bad sql mean there is an issue on your query. Back to Query builder and capture screen to show more information so that we can check it.

  • What your table
  • Relationship between table
  • Table condition
  • Subquery…
1 Like

This is a very simple table with no subqueries.



Change the formula for Month to be 12 and Analyze. If it runs then, you know the issue is with that formula. What is the formula for EstDate - are you sure that’s the right Data Type?

Go to the main BAQ screen, paste the Query Phrase into this, might see something there.

Usually when I’ve gotten the type of error you’re getting (Syntax OK, errors when you test), I’ve had something wrong in my criteria when I’ve had expressions there.

/*

  • Disclaimer!!!
  • This is not a real query being executed, but a simplified version for general vision.
  • Executing it with any other tool may produce a different result.
    */

select
(datepart(month, EstDate)) as [Calculated_Month],
(CASE
WHEN POHeader.TermsCode = ‘N30’ THEN EOMONTH(PODetail.DueDate,1)
WHEN POHeader.TermsCode = ‘N15’ THEN EOMONTH(PODetail.DueDate,0)
WHEN POHeader.TermsCode = ‘N45’ THEN EOMONTH(PODetail.DueDate,2)
WHEN POHeader.TermsCode = ‘N60’ THEN EOMONTH(PODetail.DueDate,2)
WHEN POHeader.TermsCode = ‘N90’ THEN EOMONTH(PODetail.DueDate,3)
WHEN POHeader.TermsCode = ‘PPD’ THEN EOMONTH(PODetail.DueDate,-1)
ELSE PODetail.DueDate
END) as [Calculated_EstDate],
[PODetail].[LineDesc] as [PODetail_LineDesc],
[Vendor].[Name] as [Vendor_Name],
[PODetail].[ClassID] as [PODetail_ClassID],
(CASE
WHEN PORel.TranType = ‘PUR-SUB’ THEN ‘Subcontract Operation’
WHEN PORel.TranType = ‘PUR-UKN’ THEN ‘Other’
ELSE ‘Inventory’
END) as [Calculated_Inventory],
(CASE
WHEN POHeader.CurrencyCode = ‘KRW’ THEN (Remain * PODetail.UnitCost)
ELSE ‘’
END) as [Calculated_KRW],
(CASE
WHEN POHeader.CurrencyCode = ‘USD’ THEN (Remain * PODetail.UnitCost)
ELSE ‘’
END) as [Calculated_USD],
(CASE
WHEN POHeader.CurrencyCode = ‘KRW’ THEN KRW
WHEN POHeader.CurrencyCode = ‘USD’ THEN (USD*1169.21)
END) as [Calculated_TotalKRW],
[POHeader].[BuyerID] as [POHeader_BuyerID],
[PODetail].[DocUnitCost] as [PODetail_DocUnitCost],
(PODetail.OrderQty - PORel.ArrivedQty) as [Calculated_Remain],
[POHeader].[CurrencyCode] as [POHeader_CurrencyCode],
[PODetail].[DocExtCost] as [PODetail_DocExtCost],
[PODetail].[XOrderQty] as [PODetail_XOrderQty],
[PODetail].[IUM] as [PODetail_IUM],
[PORel].[ArrivedQty] as [PORel_ArrivedQty],
[POHeader].[OrderDate] as [POHeader_OrderDate],
[Vendor].[VendorID] as [Vendor_VendorID],
[PORel].[ReceivedQty] as [PORel_ReceivedQty],
((case when podetail.openline=1 then ‘Open’ else ‘Closed’ end)) as [Calculated_LineOpen],
[PODetail].[DueDate] as [PODetail_DueDate],
[POHeader].[TermsCode] as [POHeader_TermsCode],
(1169.21) as [Calculated_Excrate],
[PORel].[TranType] as [PORel_TranType],
[POHeader].[PONum] as [POHeader_PONum],
[PODetail].[POLine] as [PODetail_POLine],
[PODetail].[PartNum] as [PODetail_PartNum]
from Erp.POHeader as POHeader
inner join Erp.PODetail as PODetail on
POHeader.Company = PODetail.Company
and POHeader.PONum = PODetail.PONUM
inner join Erp.PORel as PORel on
PODetail.Company = PORel.Company
and PODetail.PONUM = PORel.PONum
and PODetail.POLine = PORel.POLine
inner join Erp.Vendor as Vendor on
POHeader.Company = Vendor.Company
and POHeader.VendorNum = Vendor.VendorNum
inner join Erp.PurAgent as PurAgent on
POHeader.Company = PurAgent.Company
and POHeader.BuyerID = PurAgent.BuyerID
where (POHeader.VoidOrder = False)

Sorry to bother you, but the formula for EstDate is below:


Didn’t see anything obvious. I’d try eliminating calculated fields one at a time and testing, see if you can narrow down which one’s causing it. No sense in wasting your time looking at one field when it could be something else.

The case statement for Inventory looked a little funny. I thought it should be:
(CASE PORel.TranType
WHEN ‘PUR-SUB’ THEN ‘Subcontract Operation’
WHEN ‘PUR-UKN’ THEN ‘Other’
ELSE ‘Inventory’
END)
Your version might work also, that’s why troubleshooting to narrow down where it’s coming from helps.

I’ve eliminated calculated fields one at a time and tested.(In reverse order of my creation : TotalKRW >> USD >> KRW). As a result, it tested normally when I eliminated the three calculated fields below. Could you please check what the problem is?



Your fields are defined as int, yet in the ELSE you’re assigning an empty string. Assign it a value of zero.

Also, I’m not sure if your case statement on TOTALKRW is right. If you’re treating it like a true case/switch statement, it should probably be similar to the one I posted above. It’s either:
CASE fieldname
WHEN …
WHEN …
WHEN…
ELSE
END

If you don’t have the field name that you’re doing the “switch” on, then it’s a simple if…then.else. You’d need that second WHEN statement embedded in another if…then…else, so:
(CASE WHEN condition1 THEN value1
ELSE (CASE WHEN condition2 THEN value 2 ELSE default END)
END)

It was this ’ ’ that was causing the problem. I removed this ’ ’ from all calculated fields and it works fine.
But I need this empty column. How can there be no way?

If it’s numeric, you can’t assign an alphanumeric to it, which is what the blank field is. You could try changing the format (currently ->>,>>9.99) to see if you could zero suppress. Is this going to a report or a dashboard? If a report, you could edit/zero suppress it there.

This is going to a dashboard.