16 Replies Latest reply: Apr 17, 2012 10:35 AM by Michael Maeuser RSS

    Script Error

    Alex Peasley Apr 13, 2012 1:11 PM

      Anybody understand why I'm getting this error?

       

      I used the same temp table strategy for all three tables, it works for the first two, then fails on the last one. I've attached the whole script, and I have pulled out the problematic piece. Why is it trying to find NodeID? I don't have it in my load statement? Any Ideas.

       

      This is the script

      LoadTime6:

      Load

      Max(DateNum) as LastModifiedDate

      FROM [CPULoad2.qvd](qvd);

      Let vLastExecTime2 = peek('LastModifiedDate');

      Drop Table LoadTime2;

       

       

      I get this error.

       

      Field Not found - <NodeID>

      LoadTime6:

      Load......

       

        • Script Error
          Kaushik Solanki
          Kaushik Solanki Apr 13, 2012 1:20 PM (in response to Alex Peasley )

          Hi ,

           

          Would you please tell us after what statement in script you get this error.

           

          Use Debug to find that statement.

           

          Regards,
          Kaushik Solanki

            • Re: Script Error
              Alex Peasley Apr 13, 2012 1:30 PM (in response to Kaushik Solanki )

              Log file

               

              4/13/2012 12:08:22 PM: 0108  LoadTime6:

              4/13/2012 12:08:22 PM: 0109  Load

              4/13/2012 12:08:22 PM: 0110  Max(DateNum) as LastModifiedDate

              4/13/2012 12:08:22 PM: 0111  FROM (qvd)

              4/13/2012 12:08:26 PM:       Error: Field not found -

              4/13/2012 12:09:51 PM: 0112  Let vLastExecTime2 = peek('LastModifiedDate')

              4/13/2012 12:09:51 PM: 0113  Drop Table LoadTime2

              4/13/2012 12:09:51 PM:       Error: Table not found

              4/13/2012 12:09:52 PM: 0115  CPULoad:

               

              Alex Peasley

              Business Intelligence Architect

              Core BTS

              608-661-7769

                • Re: Script Error
                  Kaushik Solanki
                  Kaushik Solanki Apr 13, 2012 1:41 PM (in response to Alex Peasley )

                  Hi,

                   

                  Correct me if i am wrong. You mean to say that at line 0111 you are getting error as "field not found - NodeID'?

                   

                  Regards,

                  Kaushik Solanki

                    • Re: Script Error
                      Alex Peasley Apr 13, 2012 1:42 PM (in response to Kaushik Solanki )

                      Correct, no idea why it is saying that.

                       

                      Alex Peasley

                      Business Intelligence Architect

                      Core BTS

                      608-661-7769

                        • Re: Script Error
                          Eduardo Correa da Silva Apr 13, 2012 1:54 PM (in response to Alex Peasley )

                          Hi

                           

                          use the return of select statement in UPPERCASE

                           

                           

                          .

                          .

                          .

                          InterfaceTrafficDetail:

                          Load * From [InterfaceTrafficDetail2.qvd](qvd);

                          Concatenate

                          LOAD

                          NODEID,

                          INTERFACEID AS InterfaceID,

                          DATE,

                          Num(floor(DATETIME)) AS DateNum,

                          DATETIME,

                          .

                          .

                          .

                            • Script Error
                              Alex Peasley Apr 13, 2012 2:48 PM (in response to Eduardo Correa da Silva)

                              Thats not where the errors is, its in the temp table.

                                • Re: Script Error
                                  Eduardo Correa da Silva Apr 13, 2012 3:49 PM (in response to Alex Peasley )

                                  "I get this error.

                                   

                                  Field Not found - <NodeID>

                                  LoadTime6:

                                  Load......"

                                   

                                  The return of your SQL query must be read in uppercase. Do You got change to see if the error continues?

                                   

                                  Sorry, but the error that appears is that the field does not exist. And not with respect to temporary table.

                                   

                                  Make a small test:

                                   

                                  TMP_TEST:

                                  LOAD

                                  NodeID;

                                  SQL

                                  SELECT NodeID

                                  FROM CoreNOC.dbo.InterfaceTraffic_Detail

                                  WHERE floor (convert (decimal (12,5), DateTime))> = '$ (vLasExecTime3)';

                                   

                                  This command runs correctly? My SQL queries return correctly when return on upper case:

                                   

                                  TMP_TEST:

                                  LOAD

                                  NODEID;

                                  SQL

                                  SELECT NodeID

                                  FROM CoreNOC.dbo.InterfaceTraffic_Detail

                                  WHERE floor (convert (decimal (12,5), DateTime))> = '$ (vLasExecTime3)';

                                    • Re: Script Error
                                      Alex Peasley Apr 13, 2012 3:52 PM (in response to Eduardo Correa da Silva)

                                      If I actually take out the 1 and 2 temp tables and run the CPULoad table load on its own, it creates the variable fine and successfully loads. That’s why I’m so confused.

                                       

                                      Alex Peasley

                                      Business Intelligence Architect

                                      Core BTS

                                      608-661-7769

                                        • Script Error
                                          Rob Wunderlich
                                          Rob Wunderlich Apr 13, 2012 6:39 PM (in response to Alex Peasley )

                                          Your table label is "LoadTime6" but your drop statement references LoadTime2?

                                           

                                          Can you post yout document log?

                                           

                                          -Rob

                                            • Re: Script Error
                                              Alex Peasley Apr 16, 2012 4:03 PM (in response to Rob Wunderlich )

                                              I was messing with the Temp table to see if anything would work.

                                               

                                              Here is the log.

                                               

                                              Alex Peasley

                                              Business Intelligence Architect

                                              Core BTS

                                              608-661-7769

                                                • Re: Script Error
                                                  Eduardo Correa da Silva Apr 16, 2012 4:46 PM (in response to Alex Peasley )

                                                  Hi

                                                  There is a file "CPULoad2.qvd" when do you run the script?

                                                   

                                                  Try change line 108, 109 ,110, 111 with this:

                                                   

                                                  CPULoad:

                                                  Load

                                                       MAX(DateNum) AS LastModifiedDate

                                                  Load

                                                       DateTime,

                                                       Date,

                                                       NodeID,

                                                       DateNum,

                                                       AvgLoad,

                                                       AvgMemoryUsed,

                                                       AvgPercentMemoryUsed

                                                  From [CPULoad2.qvd](qvd);

                                                    • Re: Script Error
                                                      Jasleen Puri Apr 16, 2012 7:14 PM (in response to Eduardo Correa da Silva)

                                                      line 127 should rather be NodeID. you're storing Nodeid into the qvd.

                                                        • Script Error
                                                          Rob Wunderlich
                                                          Rob Wunderlich Apr 16, 2012 11:08 PM (in response to Jasleen Puri)

                                                          I agree with Jasleen. The message may be appearing out of order, but I think the problem is with the last LOAD stmt. The problem is the mismatch between SQL NodeId and the LOAD NodeID. SQL is not case sensitive, but QV is.

                                                           

                                                          So either change the SQL to NodeID or change the load to "NodeId as NodeID".

                                                           

                                                          -Rob

                                                            • Re: Script Error
                                                              Alex Peasley Apr 17, 2012 10:26 AM (in response to Rob Wunderlich )

                                                              I still don’t think that’s the issue. When I comment out the load script and just try to run the Temp Table. I get the same error.

                                                               

                                                              When I run the temp table all by its self in the script, it works fine. But when I uncomment the first two tables, it errors out. What gives?

                                                               

                                                              LoadTime2:

                                                              Load

                                                              Max(DateNum) as LastModifiedDate

                                                              FROM (qvd);

                                                              Let vLastExecTime2 = peek('LastModifiedDate',0,'LoadTime2');

                                                              Drop Table LoadTime2;

                                                               

                                                              //CPULoad:

                                                              //Load * From (qvd);

                                                              //Concatenate

                                                              //LOAD

                                                              //DateTime,

                                                              //Date,

                                                              //NodeID,

                                                              //Num(floor(DateTime)) AS DateNum,

                                                              //AvgLoad,

                                                              //AvgMemoryUsed,

                                                              //AvgPercentMemoryUsed;

                                                              //SQL SELECT

                                                              //NodeID,

                                                              //Cast(DateTime AS bigint) AS Date,

                                                              //DateTime,

                                                              // floor(convert(decimal(12,5),DateTime)) AS Date,

                                                              //AvgLoad,

                                                              //AvgMemoryUsed,

                                                              //AvgPercentMemoryUsed FROM CoreNOC.dbo.CPULoad_Detail

                                                              //WHERE  floor(convert(decimal(12,5),DateTime))  >= '$(vLastExecTime2)';

                                                               

                                                              //STORE CPULoad INTO CPULoad2.qvd;

                                                               

                                                              DROP Table CPULoad;

                                                              DROP Table AvgUpTime;

                                                               

                                                               

                                                              Alex Peasley

                                                              Business Intelligence Architect

                                                              Core BTS

                                                              608-661-7769