Query not working in SAP Business One but yes in SQL Server

[former_member268360]

Hello everyone , I have a query thats works correctly in SQL Server but it doesn't work in SAP Business One. How Can I make it work in SAP query manager ?

(please test in SAP query manager before answer)

IF OBJECT_ID ('turnover_view1', 'V') IS NOT NULL DROP VIEW turnover_view1 ; GO CREATE VIEW turnover_view1 AS SELECT T0.CardName , Sum(T0.Doctotal) As Total_factures FROM OINV T0 WHERE ( CONVERT(VARCHAR(10), T0.TaxDate, 120) ) BETWEEN (DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) ) AND (EOMONTH ( GETDATE() )) AND T0.CANCELED = 'N' GROUP BY T0.CardName ; GO IF OBJECT_ID ('turnover_view2', 'V') IS NOT NULL DROP VIEW turnover_view2 ; GO CREATE VIEW turnover_view2 AS SELECT T0.CardName , Sum(T0.Doctotal) As Total_avoirs FROM ORIN T0 WHERE ( CONVERT(VARCHAR(10), T0.TaxDate, 120) ) BETWEEN (DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) ) AND (EOMONTH ( GETDATE() )) AND T0.CANCELED = 'N' GROUP BY T0.CardName ; GO SELECT T1.cardname as "Nom du client", T1.Total_factures - ISNULL(T2.Total_avoirs,0) as "Chiffre d'affaires" FROM turnover_view1 T1 LEFT OUTER JOIN turnover_view2 T2 ON T1.CardName = T2.CardName WHERE (T1.Total_factures - ISNULL(T2.Total_avoirs,0) ) <= 5000

[Johan_H · Accepted solution]

Hi,

The B1 sql parser does not allow volatile commands like drop, alter and create.

Please setup a job in MS SQL Server Management Studio to achieve your purpose.

Regards,

Johan

[former_member268360]

I realized that i don't need those volatile commands, so I added/created those views in sql server in company database

And In SAP query generator I run only the following query

SELECT T1.cardname as "Nom du client", T1.Total_factures - ISNULL(T2.Total_avoirs,0) as "Chiffre d'affaires" FROM turnover_view1 T1 LEFT OUTER JOIN turnover_view2 T2 ON T1.CardName = T2.CardName WHERE (T1.Total_factures - ISNULL(T2.Total_avoirs,0) ) <= 5000

Thank you

[Former Member]

Hi,
Here's the example of B1 Query Generator queries:

SELECT T1.[BatchNum], T1.[RefDate], T1.[TransId],T1.[Line_ID], T1.[Account],T1.[ShortName],T0.[AcctName], T1.[LineMemo], T1.[Debit], T1.[Credit], T1.[Ref1], T1.[Project] FROM OACT T0

And you can not use " " for alias.

It should be :

SELECT T1.[cardname] [Nom du client], T1.[Total_factures] - ISNULL(T2.[Total_avoirs],0) [Chiffre d'affaires] FROM turnover_view1 T1 LEFT OUTER JOIN turnover_view2 T2 ON T1.[CardName] = T2.[CardName] WHERE (T1.[Total_factures] - ISNULL(T2.[Total_avoirs],0) ) <= 5000

hopefully this can help you and please mark as answered if this is correct