从 SQL Server 中的用户选择设置变量 [英] Set variable from user selection in SQL Server

问题描述

我是 SQL Server 的初学者

I am beginner in SQL Server

我在 SAP B1 中的查询是

My query in SAP B1 is

  DECLARE @FROMDATE AS DATETIME
  SET @FROMDATE= [%0]

  DECLARE @TODATE AS DATETIME
  SET @TODATE= [%1]

  DECLARE @ACCNUM AS NVARCHAR(100)
  SET @ACCNUM= [%2]

获取账户的每笔交易的账户名、编号

get the account name, number for every transaction of an account

   SELECT 
   T4.[ACCOUNT] AS 'A/C NUM',
   T4.[ACCTNAME] AS 'A/C NAME',
   T4.[TransId] AS 'KEY',

   SUM(VEL_DB) AS 'VEL DB',
   SUM(VEL_CR) AS 'VEL CR',

   SUM(DEF_DB) AS 'DEF_DB',
   SUM(DEF_CR) AS 'DEF CR',
   T4.[LINEMEMO],
   T1.[U_Naaration]


   FROM OJDT T1 

根据 ocrcode2 计算帐户中的每笔交易的贷记和借记

for every transaction in an account depending on the ocrcode2 credits and debits are calculated

   INNER JOIN 
      (
     SELECT T2.[TRANSID],T2.[ACCOUNT],T3.[ACCTNAME],T2.[LINEMEMO],

     case
     when T2.[OcrCode2]='VEL'
     THEN
       (T2.[Debit]) 
     ELSE
       NULL
     END as VEL_DB,

    case
    when T2.[OcrCode2]='VEL'
    THEN
     (T2.[CREDIT]) 
    ELSE
     NULL
    END as VEL_CR,

   case
   when T2.[OcrCode2]='' OR T2.[OCRCODE2] IS NULL
   THEN
     (T2.[Debit]) 
   ELSE
     NULL
   END as DEF_DB,

   case
   when T2.[OcrCode2]='' OR T2.[OCRCODE2]IS NULL
   THEN
       (T2.[CREDIT]) 
   ELSE
     NULL
   END as DEF_CR


   FROM JDT1 T2 
      INNER JOIN OACT T3 ON T3.[ACCTCODE]=T2.[ACCOUNT]
    where T2.[DUEDATE]>=@FROMDATE AND T2.[DUEDATE]<=@TODATE ) 
    T4 ON T4.TRANSID = T1.TRANSID AND T4.[ACCOUNT]=@ACCNUM


  GROUP BY T4.[TransId] ,T1.[U_Naaration],T4.[ACCTNAME],T4.[ACCOUNT],T4.[LINEMEMO]

   FOR BROWSE

我收到错误

必须选择要从中选择的表格

must select a table to select from

推荐答案

根据 Petr Verner 的正确答案 在这里，您可能需要重写查询以避免使用子查询.

As per the correct answer by Petr Verner here, you may need to rewrite your query to avoid using a subquery.

答案还继续建议选项:

1. 将查询嵌入到存储过程中并调用该 SP.

1. Embed the query into a stored procedure and call that SP instead.

使用视图.(您可以将它用于子查询部分或整个查询).

Use a view. (You could use it either for the subquery part or for the entire query).

将子查询的结果存储到表变量(或临时表)中，然后在主查询中使用保存的结果集.

Store the results of a subquery into a table variable (or into a temporary table), and then use the saved result set in the main query.

这篇关于从 SQL Server 中的用户选择设置变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！