Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Oct 26, 2016 at 08:28 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 26, 2016 at 01:10 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 14, 2017 at 11:55 AM

    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

    Add comment
    10|10000 characters needed characters exceeded