Ashkan Ashkan - 3 months ago 11
SQL Question

How to create a WHILE loop in SQL and avoid Subquery error

I am going to find the best coefficient

(Z)
for the following equation:

[Target] = Z * SQRT(Mu_L * POWER(Sigma_D,2) + POWER(Mu_D,2) * POWER(Sigma_L,2))


I have about 100,000 rows in my data-set which do not meet the
Target
level. So, I am going to find the minimum
Z
value to pass the target level. Each row is identical so one row might need
Z=34.5
and another one might need
Z=13.5
. I want to write a code to examine each row through a loop until all rows meet the desired target level and print the optimum
Z
values for all rows.

I wrote code like this:

While (Select [Target] From dbo.product) < 1000
Begin
Update dbo.product
SET [Z] = Z + 0.5

Update dbo.product
SET [Target] = Z * SQRT(Mu_L * POWER(Sigma_D,2) + POWER(Mu_D,2) * POWER(Sigma_L,2))

IF (Select [Target] From dbo.product) > 1000
Break
Else
Continue
End


Once I run this query, I get the following error:


Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


Do you have any idea how can I fix this error? And also do you believe this code could solve my problem? I appreciate your help!

Answer

As you want to make sure all rows with [Target] >= 1000, use MIN to find the row with smallest value.

While (Select MIN([Target]) From dbo.product) < 1000 
Begin
   Update dbo.product 
   SET [Z] = Z + 0.5
   WHERE [Target] < 1000

   Update dbo.product 
   SET [Target] = Z * SQRT(Mu_L * POWER(Sigma_D,2) + POWER(Mu_D,2) * POWER(Sigma_L,2))

   IF (Select MIN([Target]) From dbo.product) > 1000
      Break
   Else
      Continue 
End 
Comments