help with delcaring int and assigning value at run time

[Rkota]

Hello and Good evening

Here's the part of my code where it's failing.  Guess my "declaration" syntax/method is incorrect.

Fails at the point where I'm trying to assign values to each of these variables...@LoopCounter and @MaxLoopCount

Any help is greatly appreciated.

Thanks

-----------------

Insert into #coollist

select a.*, ROW_NUMBER() over(order by a.cool) as coolnum from
(
select DISTINCT cool from xyzw.dbo.Location
) a

SET @LoopCounter = 'select min(coolnum)FROM #coollist
SET @MaxLoopCount = 'select max(coolnum) FROM #coollist

WHILE(@LoopCounter IS NOT NULL -
AND @LoopCounter <= @MaxLoopCount)
BEGIN

...

...

..

....

....
END

[vishsaggi]

Try this may be?

LET @LoopCounter = min(coolnum);

LET @MaxLoopCount = max(coolnum);

[Rkota]

thanks for your note, but unfortunately didn't work...perhaps I'm not conveying my question/issue correctly.

[rahulpawarb]

Hello Raj,

Trust that you are doing well!

Please refer below given draft solution:

TmpCoolList:
LOAD   MIN(coolnum) AS LoopCounter,
       MAX(coolnum) AS MaxLoopCount
Resident #coollist;

Let vLoopCounter = Peek('LoopCounter',0,'TmpCoolList');
Let vMaxLoopCount = Peek('MaxLoopCount',0,'TmpCoolList');

Drop Table TmpCoolList;

For i=$(vLoopCounter) TO vMaxLoopCount
...
...
...
Next

Hope this will help.

NOTE: Use LET instead of SET. LET will evaluate the expression where as SET will accept it as it is.

Regards!

Rahul

[jonathandienst]

This is really a SQL question and you might get more useful help on a SQL forum - unless you want to convert this SQL script to QV load script?

[Rkota]

Yes I understand...and what I have works fine in SQL...just that i'm trying to implement it in qlikview..am trying Rahul's solution above...

Thanks for your comments and help.

[Rkota]

Thanks for your suggestion Rahul....didn't work...hopefully I didn't update my code incorrectly, i'll double check though.

Perhaps you or someone else may have a suggestion based on this information below

I tried to revert back to what I had in originally with some changes based on your LET suggestion

LET vLoopCounter = 'select min(coolNum) from #coolList';  

LET vMaxLoopCount = 'select max(coolNum) from #coolList'; 

------------- #coolList is the table name - no issues with that.

Here's the error I see

---

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near '='.
SQL
WHILE(vLoopCounter IS NOT NULL
      AND vLoopCounter <= vMaxLoopCount)
BEGIN

   Insert into #MnRSumByChassisnPoolFinal

   select top 10 * from #MnRSumByChassisnPool
where PoolName in (SELECT  PoolName
   FROM #coolList WHERE coolNum = vLoopCounter)
   order by TotalCost desc
SET vLoopCounter  = vLoopCounter  + 1       
END

---