19 Replies Latest reply on Jan 8, 2016 1:43 PM by Rashmi Branched to a new discussion.

    Bad numeric constant

    Rashmi

      Hi,

       

      I'm connecting to PI AF via a powershell script (connectionstring is defined).

      my db query runs fine on PI SQL commander. the name of the column is 100speed. To ahndle this I had put"100speed" and column value is fetching correctly now. The same query I ahve placed inside the powershell script. Which executing teh script from the powershell cmd prompt, I see the error:"bad numeric constant:100  (Parser error). Any suggestion please?

       

      Regards,Rashmi

        • Re: Bad numeric constant
          Roger Palmen

          Could you show the powershell script? or at least the section that is causing issues? It's difficult to assess the issue with just a description.

            • Re: Bad numeric constant
              Rashmi

              Hi,

               

              Please find the powershell script here.

               

              Line number 157 is the problem.

               

              not sure whether any other syntax error I have. I'm very new to the powershell script writing

                • Re: Bad numeric constant
                  Roger Palmen

                  Hi,

                  Looking at the script, you access AF using the OLEDB Enterprise provider.

                   

                  I think it's a syntax issue. Looking at this section:

                      $query3 = "SELECT eh.Name Element, tc.height,
                  
                      case tc."100speed" when -1 then 'NaN' end, "100speed",
                      case tc."90speed" when -1 then 'NaN' end, "90speed", 
                  

                   

                  My suspicion is that this is due to the quotes in the string. I would suggest to format this using here-strings. Try adding @ before the first line, and after the last line. That way you don't need to worry about escaping the qoutes in your SQL query. Such as:

                   

                  Start:

                      $query3 = @"SELECT eh.Name Element, tc.height,
                      case tc."100speed" when -1 then 'NaN' end, "100speed",
                      case tc."90speed" when -1 then 'NaN' end, "90speed", 
                  

                  End:

                      OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)"@
                  

                   

                  The empty line is also not helping here, so remove that one too.

                   

                  Some tips on multi-line strings in PowerShell: Windows PowerShell Tip: Using Windows PowerShell "Here-Strings"

                  1 of 1 people found this helpful
                    • Re: Bad numeric constant
                      Rashmi

                      Hi Roger,

                       

                      Yes, I connect to AF by using OLEDB enterprise provider. As per your suggestion, I added @ at the start and end point of the query, but no luck.

                       

                      Attched the error here.

                       

                      $40371CC884BB3293.bmp

                        • Re: Bad numeric constant
                          Roger Palmen

                          Without spending too much time on finding tour specific issue (end of working day here), i suggestion that might make your powershell easier to manage: Store your SQL statements as view in SQL commander and call the view from your powershell script. That will make it easier to distingish between SQL issues and powershell script issues.

                          • Re: Bad numeric constant
                            Roger Palmen

                            And fixing my own mistake: where strings should use the here-string on a separate line. Thus:

                            Start:

                              $query3 = @"
                            SELECT eh.Name Element, tc.height,  
                                case tc."100speed" when -1 then 'NaN' end, "100speed",  
                                case tc."90speed" when -1 then 'NaN' end, "90speed", 
                            

                             

                            End:

                            OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)
                            "@ 
                            
                              • Re: Bad numeric constant
                                Rashmi

                                Hi Roger,

                                 

                                I've applied your suggestions, but no luck.

                                 

                                The first query I wrote for -1, seems NaN and -1 values are appearing in two different fields. Also the keyword 'Else' is not accepting to state that if not -1, then comun value should populate.

                                 

                                NaN.bmp

                                NaNerror.bmp

                                  • Re: Bad numeric constant
                                    aksnk89

                                    hi rashmi,

                                    try replacing if statement as

                                    if tc.90speed = -1 then 'NaN' else cast (tc.90speed as variant) as "90Speed"

                                    1 of 1 people found this helpful
                                      • Re: Bad numeric constant
                                        Rashmi

                                        Hi Akash,

                                         

                                        Many thanks for your suggestion. It did work well .

                                         

                                        Now the requirement has changed a bit. We are referencing those column values from PI Point. The condition is if those column values are refreshing from PI Tag, then that value should populate, else NaN.

                                          • Re: Bad numeric constant
                                            aksnk89

                                            You  can have an additional join with "ElementAttribute" table on ElementID and play with the columns "DataReferencePlugIn" and "ConfigString" to determine whether the Attribute has an associated PI Tag.

                                              • Re: Bad numeric constant
                                                Rashmi

                                                Hi Akash,

                                                 

                                                I have developed another issue with regards to date format. I wanted the date format should be in mmddyyyy format.

                                                 

                                                I tried thsi query with 'convert' function, but the error is 'varchar' is invalid.

                                                 

                                                SELECT eh.Name, i.Value

                                                  FROM [HYREF].[Asset].[

                                                ElementHierarchy] eh
                                                  INNER JOIN [HYREF].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID,
                                                  [HYREF].[Data].[Archive] i
                                                  WHERE eh.Name = 'F4401'
                                                  AND i.ElementAttributeID = ea.ID
                                                  AND Convert(varchar(20),i.Time,112) = Date('20160106')
                                                  AND ea.Name ='outpower'
                                                  OPTION (FORCE ORDER, EMBED ERRORS) 

                                                 

                                                I also tried with the options :

                                                 

                                                SELECT replace(convert(NVARCHAR, getdate(), 106), ' ', '/')
                                                and
                                                select Convert(varchar(10),CONVERT(date,YourDateColumn,106),103)

                                                but no luck. For all of the above, the error was 'varcahr' is invalid. I did try with NVARCHAR,date. char etc.

                                                Please could you correct me?
                                                  • Re: Bad numeric constant
                                                    aksnk89

                                                    use format function.

                                                    for ex. format(time,'MMddyyyy')

                                                      • Re: Bad numeric constant
                                                        Rashmi

                                                        Does anyone have idea to write the powershell script which will handle the exception handle section if :

                                                         

                                                        1. the sql connection failed

                                                        2. Sql queries did not run correctly and they should raise error with proper error code

                                                        3. what if connection closure not successful

                                                         

                                                        I did a general google search but could not find any tips . I have my powershell script ready, which is running OK and giving me the proper output. But I have to include the above error handling steps. I'm not expert in powershell code. any help is much appreciated.

                                                         

                                                        Regards,Rashmi

                                                          • Re: Bad numeric constant
                                                            Rashmi
                                                            if( ($errors = sqlsrv_errors(int SQLSRV_ERR_ALL) ) != null) {

                                                             

                                                            Hi, can anyone suggest me please what's wrong in above powershell syntax? I get the error "unexpected token '!' in expression or statement

                                                              • Re: Bad numeric constant
                                                                Roger Palmen

                                                                != is not a powershell operand.

                                                                For good measure, separate the IF expression from the assignment. Null is easy to check:

                                                                 

                                                                $errors = sqlsrv_errors(int SQLSRV_ERR_ALL)

                                                                IF $errors {code when $errors is null} ELSE {code when $errors is null}

                                                                  • Re: Bad numeric constant
                                                                    Roger Palmen

                                                                    PS: comparisons in powershell:

                                                                    Equal:           $errors -eq $null

                                                                    Not equal:      $errors -ne $null

                                                                     

                                                                    NOTE: $null is only null if you did not set it...

                                                                      • Re: Bad numeric constant
                                                                        Rashmi

                                                                        Many thanks Roger

                                                                          • Re: Bad numeric constant
                                                                            Rashmi

                                                                            I'm working on another issue to deal with NaN.

                                                                             

                                                                            PI data on SMT tool is refreshed data in every 10 minutes. But, for some occasion the actual data is not there. If so, then those attribute values to be set as NaN,

                                                                             

                                                                            example file of output:

                                                                             

                                                                            @ serial time farmcode generatorcode height speed direction temperature outpower state

                                                                            # 1 2015-12-29 00:00:00 F4401 F4401W0006H0060 58 10 26 23 106 1

                                                                            # 2 2015-12-29 00:00:00 F4401 F4401W0005H0060 58 10 25 23 105 1

                                                                            # 3 2015-12-29 00:00:00 F4401 F4401W0004H0060 58 10 24 23 104 1

                                                                            # 4 2015-12-29 00:00:00 F4401 F4401W0003H0060 58 10 23 23 103 1

                                                                             

                                                                            Each generator code is independent. If data not available for 2nd or 3rd generator(for example), then NaN should set for attributes (speed direction temperature outpower state). I had written some code, but I did not see the right output. Anyone can assist me please? I'm attaching my powershell script here.

                                                                             

                                                                            PS: We have 52 generator codes for Farm F4401 , starting from F4401W0001H0060 to F4401W0052H0060

                                                                             

                                                                            This is my first attempt at scripting and learning phase.

                                                                             

                                                                            my output files looks like this, which is totally incorrect.

                                                                             

                                                                             

                                                                            @ serial time farmcode latlongmap height speed direction temperature outpower state

                                                                             

                                                                            # 1 '2016-01-08 00:00:00' F4401 F4401W0001H0060 58 NaN NaN NaN NaN NaN58 F4401W0001H0060 58 NaN NaN NaN NaN NaN F4401W0001H0060 58 NaN NaN NaN NaN NaN F4401W0001H0060 58 NaN NaN NaN NaN NaN F4401W0001H0060 58 NaN NaN NaN NaN NaN6.85550022125244 F4401W0001H0060 58 NaN NaN NaN NaN NaN F4401W0001H0060 58 NaN NaN NaN NaN NaN

                                                                             

                                                                            I see only one serial (1). I should be able to see 52 lines . here data is appending. unsure what additional codes to be added.