Skip to Content
0

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

Oct 25, 2016 at 01:07 PM

168

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Johan Hakkesteegt Oct 26, 2016 at 08:28 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Jaouad El Hachimi El Belghiti Oct 26, 2016 at 01:10 PM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Marini Shen Dec 14, 2017 at 11:55 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded