使用IF SELECT INTO语句在订单附近发生错误的语法

So I'll post my SQL code below (actually just the part that is failing) and this is for a SQL Server as well:

所以我将在下面发布我的SQL代码(实际上只是失败的部分),这也适用于SQL Server:

SELECT @newPID = (SELECT policy_id FROM #StatusTable);
SELECT @newTXN = (SELECT txn_number FROM #StatusTable);

IF (@newPID IS NOT NULL) 
BEGIN
    SELECT * 
    INTO #StatusTable 
        (SELECT TOP (1) 
             id, policy_product_id, txn_number, agent_id, user_id, old_status_id, 
             new_status_id, status_update_date, status_type_id, notes, policy_id
         FROM  sales_blotter_status_changes
         WHERE (policy_id = @newPID)
         ORDER BY status_update_date DESC)
END 
ELSE BEGIN
    SELECT * 
    INTO #StatusTable 
        (SELECT TOP (1) 
             id, policy_product_id, txn_number, agent_id, user_id, old_status_id, 
             new_status_id, status_update_date, status_type_id, notes, policy_id
         FROM  sales_blotter_status_changes
         WHERE (txn_number = @newTXN)
         ORDER BY status_update_date DESC)
END

So if you look through this basically what I'm trying to create a temp table based on whether or not a specific field from a previous table is Null or not. When I run this query I get a incorrect syntax error that states the problem is next to ORDER (even though it doesn't state I'm pretty sure it would be the first one). Can someone take a look at this and let me know where I'm going wrong, I've been working on this for about 4 hours and I just can't find the issue.

所以,如果你仔细研究一下我基本上是根据前一个表中的特定字段是否为空来创建临时表。当我运行此查询时,我得到一个不正确的语法错误,指出问题是在ORDER旁边(即使它没有声明我很确定它将是第一个)。有人可以看看这个,让我知道我哪里出错了,我已经在这工作了大约4个小时,我找不到问题。

1 个解决方案

#1

The problem is that subqueries require an alias in sql-server. So, try this quick fix:

问题是子查询需要sql-server中的别名。所以,试试这个快速修复:

SELECT @newPID = (SELECT policy_id FROM #StatusTable);
SELECT @newTXN = (SELECT txn_number FROM #StatusTable);

IF (@newPID IS NOT NULL) 
BEGIN
SELECT * INTO #StatusTable (SELECT TOP (1) id, policy_product_id, txn_number, agent_id, user_id, old_status_id, new_status_id, status_update_date, status_type_id, notes, policy_id
FROM  sales_blotter_status_changes
WHERE (policy_id = @newPID)
ORDER BY status_update_date DESC) t
END ELSE BEGIN
SELECT * INTO #StatusTable (SELECT TOP (1) id, policy_product_id, txn_number, agent_id, user_id, old_status_id, new_status_id, status_update_date, status_type_id, notes, policy_id
FROM  sales_blotter_status_changes
WHERE (txn_number = @newTXN)
ORDER BY status_update_date DESC) t
END

By the way, you don't need the subquery. In fact, you don't even need two queries. You can write the logic as:

顺便说一句,您不需要子查询。实际上,您甚至不需要两个查询。您可以将逻辑编写为:

SELECT TOP (1) id, policy_product_id, txn_number, agent_id, user_id, old_status_id, new_status_id, status_update_date, status_type_id, notes, policy_id
INTO #StatusTable 
FROM  sales_blotter_status_changes
WHERE (@newPID is not null and policy_id = @newPID) or (@newPID is null and txn_number = @newTXN)
ORDER BY status_update_date DESC;

#1