需要更正select语句以使错误消息正确 [英] The select statement needs to be corrected to get the error message right
问题描述
请检查下面的选择声明并更正。
用户一次选择并处理一条记录。
Please check the select statement below and correct it .
The user select and process one record at a time.
----------------------------------------------------------------------------------
TABLE :: KOANS
IDNO LOANUMBER NUMDAYS REPAY
-------------------------------------
BEN 001 2 SIMPLE
AMY 002 0 REDUCING
ANN 003 1 HOLD
BET 004 0
JON 005 5 SIMPLE
请验证以下代码
---------- -------------------------------------------------- ------------------------
Please validate the codes below
------------------------------------------------------------------------------------
IF NOT EXISTS(SELECT * FROM KOANS WHERE (ISNULL(NUMDAYS,0) = 0)
OR (REPAY NOT IN ('SIMPLE','REDUCING'))
AND IDNO=@ZIDNO AND LOANUMBER= @ZLOANUMBER)
BEGIN
SET @TYEAR=@CYEAR
END
ELSE
BEGIN
RAISERROR('There are incomplete records the process has been Aborted', 11, 1)
END
---------------------- -------------------------------------------------- ----------
以下记录应该在选中时返回错误信息
----------------------------------------------------------------------------------
The following records should return the error message when selected
TABLE :: KOANS
IDNO LOANUMBER NUMDAYS REPAY
-------------------------------------
AMY 002 0 REDUCING
ANN 003 1 HOLD
BET 004 0
请更正 SQL语句。
谢谢
我尝试了什么：
试过我的旧代码和coud not get it
Please correct the SQL statement .
Thanks
What I have tried:
Tried my old codes and coudn't get it
推荐答案
你的select语句有用吗？ SELECT * FROM KOANS WHERE（ISNULL（NUMDAYS，0）= 0）
OR（不进行回复（'简单'，'减少'））
AND IDNO = @ ZIDNO和LOANUMBER = @ZLOANUMBER
用@ ZIDNO和@ZLOANNUMBER替换实际值并得到它的工作。然后处理NOT EXISTs
Does your select statement work? SELECT * FROM KOANS WHERE (ISNULL(NUMDAYS,0) = 0)
OR (REPAY NOT IN ('SIMPLE','REDUCING'))
AND IDNO=@ZIDNO AND LOANUMBER= @ZLOANUMBER
Substitute real values in for @ZIDNO and @ZLOANNUMBER and get that too work. then work on the NOT EXISTs
这是我尝试过的：
Here's what I have tried:
USE [cpqaAnswers]
GO
CREATE TABLE [cpqaAnswers].[cpqa].[KOANS](
[IDNO] nvarchar(13),
[LOANUMBER] nvarchar(14),
[NUMDAYS] nvarchar(13),
[REPAY] nvarchar(23)
)
/*
content of KOAN.txt (tab delimited text):
BEN 001 2 SIMPLE
AMY 002 0 REDUCING
ANN 003 1 HOLD
BET 004 0
JON 005 5 SIMPLE
*/
BULK INSERT [cpqaAnswers].[cpqa].[KOANS] FROM 'C:\cpqaAnswers\cpqa_ST_varDECLARE_160310-1315\KOAN.txt'
SELECT * FROM [cpqaAnswers].[cpqa].[KOANS]
/*
IDNO LOANUMBER NUMDAYS REPAY
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BEN 001 2 SIMPLE
AMY 002 0 REDUCING
ANN 003 1 HOLD
BET 004 0 NULL
JON 005 5 SIMPLE
*/
从这里开始有问题 - >
Something's wrong starting here ->
DECLARE @ZIDNO nvarchar(13)
DECLARE @ZLOANUMBER nvarchar(14)
DECLARE @TYEAR nvarchar(20)
DECLARE @CYEAR nvarchar(410)
我收到一条错误消息（见下文）：
And I get an error message (see beneath) here:
IF NOT EXISTS(SELECT * FROM [cpqaAnswers].[cpqa].[KOANS] WHERE (ISNULL(NUMDAYS,0) = 0)
OR (REPAY NOT IN ('SIMPLE','REDUCING'))
AND IDNO=@ZIDNO AND LOANUMBER= @ZLOANUMBER)
BEGIN
SET @TYEAR=@CYEAR
END
ELSE
BEGIN
RAISERROR('There are incomplete records the process has been Aborted', 11, 1)
END
Text of message:
Msg 50000, Level 11, State 1, Line 14
There are incomplete records the process has been Aborted
是否有任何此类消息类似于我应该看到的任何其他东西？
Does any of this like similar to anything else I should be seeing?
有些事情，我认为它不会导致错误，但你不需要在每一行都使用DECLARE。一个DECLARE和变量之间的逗号。
我从未见过你设置变量@ZIDNO和@ZLOANNUMBER。你在哪里设置@CYear ???这将是一个存储过程，您将传递变量？
以下是我要做的步骤。
1创建表，你已经完成了这个。
2让select语句正常工作，我不要认为这是正常工作
SELECT *
FROM [KOANS]
WHERE（ISNULL（NUMDAYS，0）= 0）< br $>
OR（
不进入）（
'SIMPLE'
，'减少'
）
）
AND IDNO ='Jon'
AND LOANUMBER ='005'
什么是你试图从Select语句中恢复？
3.用值替换你的变量，然后运行它。你应该有两条记录，一条在select语句中不存在，另一条记录存在并返回错误信息。
A couple of things, I do not think that it is causing an error but you do not need to DECLARE on every line. One DECLARE and a comma in between the variables.
I never see you set your variables @ZIDNO and @ZLOANNUMBER. Where are you setting @CYear??? Is this going to be a stored procedure that you are going to pass variables?
Here are the steps that I would do.
1 Create Table, you have done this.
2 get the select statement working, I do not think that this is working correctly
SELECT *
FROM [KOANS]
WHERE (ISNULL(NUMDAYS, 0) = 0)
OR (
REPAY NOT IN (
'SIMPLE'
, 'REDUCING'
)
)
AND IDNO = 'Jon'
AND LOANUMBER = '005'
What are you trying to bring back from the Select statement?
3.Replace your variables with values, then run it. you should have two records one that would not exist in your select statement and one that would exists and return your error message.
这篇关于需要更正select语句以使错误消息正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
