1. 

   ajdjackson, Member

   Evening

   Does dbr.calc work with an insert statement?

   I have a select statement which has several dbr.calc calculations. This works as expected when displaying results in browser. However when I put an insert statement before the select statement the dbr.calc calculations do not appear to work ie do not do the calculations.
   I wish to insert the rows from the above select statement in a table.
   I’m using SQL Server.

   Thanks

   Jake
2. 

   myDBR Team, Key Master

   Can you show what you’re trying to do?

   —
   myDBR Team
3. 

   ajdjackson, Member

   Hi

   This is what I’ve got.

   
   select 'dbr.calc','GBPPrice','[DSP]/[FX]';
   
   select 'dbr.calc','LTotal','[Qty]*[GBPPrice]*(100-[Disc])/100';
   
   select 'dbr.calc','Gross','[LTotal]*(100+[VATR])/100';
   
   select 'dbr.calc','VAdd','[LTotal]-[TCost]';
   
   select 'dbr.calc','VATA','[Gross]-[LTotal]';
   
   insert into dbo.tblVec_Sales
   
   select 2 as CompID,
   
          a.[CustomerID] as CustID,
   
          b.[CustomerAccountName] as CustName,
   
          c.[Name] as DocType,
   
          eomonth(a.[DocumentDate]) as MonthEnd,
   
          e.[PrintSequenceNumber] as "Line #",
   
          e.[ItemCode] as "Item Code",
   
          e.ItemName as "Item Description[desc]",
   
          e.NominalAccountNumber as "Nominal",
   
          iif(c.[SOPInvoiceCreditTypeID] in (1,3,5),e.[InvoiceCreditQuantity]*-1,e.[InvoiceCreditQuantity]) as "Qty[Qty]",
   
          e.[ItemPrice] as "Price[DSP]",
   
          i.Code as "Doc Cur.",
   
          a.[ExchangeRate] as "FX Rate[FX]",
   
          null as "Price, GBP[GBPPrice]",
   
          e.[LineDiscountPercent] as "% Disc[Disc]",
   
          null as "Line Total, £[LTotal]",
   
          j.[TaxRate] as "VAT Rate[VATR]",
   
          null as "VAT, £[VATA]",
   
          null as "Gross, £[Gross]",
   
          iif(k.[RealisedQuantity]>0,k.[RealisedCostValue]/k.[RealisedQuantity],k.[RealisedCostValue]) as "Cost Price[CP]",
   
          k.[RealisedCostValue] as "Total Cost[TCost]",
   
          null as "Value Added, £[VAdd]",
   
          a.[DocumentNo] as "Inv/Crd #[DocNum]",
   
          cast(a.[DocumentDate] as date) as DocDate
   
   from [PH-HV-SAGE200].[dbo].[SOPInvoiceCredit] a
   
   join [PH-HV-SAGE200].[dbo].[SLCustomerAccount] b on a.[CustomerID]=b.[SLCustomerAccountID]
   
   join [PH-HV-SAGE200].[dbo].[SOPInvoiceCreditType] c on a.[SOPInvoiceCreditTypeID]=c.[SOPInvoiceCreditTypeID]
   
   left join [PH-HV-SAGE200].[dbo].[SOPOrderReturn] d on a.[SOPOrderReturnID]=d.[SOPOrderReturnID]
   
   left join [PH-HV-SAGE200].[dbo].[SOPInvoiceCreditLine] e on a.[SOPInvoiceCreditID]=e.[SOPInvoiceCreditID]
   
   join [PH-HV-SAGE200].[dbo].[DocumentStatus] g on a.[DocumentStatusID]=g.[DocumentStatusID]
   
   join [PH-HV-SAGE200].[dbo].[SYSCurrency] h on b.[SYSCurrencyID]=h.[SYSCurrencyID]
   
   join [PH-HV-SAGE200].[dbo].[SYSCurrencyISOCode] i on h.[SYSCurrencyISOCodeID]=i.[SYSCurrencyISOCodeID]
   
   join [PH-HV-SAGE200].[dbo].[SYSTaxRate] j on e.[SYSTaxRateID]=j.[SYSTaxRateID]
   
   left join [PH-HV-SAGE200].[dbo].[SOPOrderLineProfitAnalysis] k on e.[SOPOrderReturnLineID]=k.[SOPOrderReturnLineID]
   
   where a.DocumentStatusID<>4 and e.NominalAccountNumber between '4000' and '4199' and eomonth(a.[DocumentDate])='2024-08-31';
   
   

   The rows are inserted but the dbr.calc fields are blank.

   Cheers

   Jake
4. 

   myDBR Team, Key Master

   The INSERT statement is executed within the database when the procedure is run. myDBR processes the result set returned by the database, i.e., the output of the procedure (SELECT statements).

   You can also perform the calculations directly in the SQL.
   --
   myDBR Team
5. 

   ajdjackson, Member

   Hi

   Thanks for your answer - I had thought that was the case.

   I had hoped it would've work as some of those calculations are complex when written in SQL and easy to trip up on.

   I've already rewritten the SP longhand and got it working.

   Thanks for the clarification.

   Cheers

   Jake

Reply

You must log in to post.