SQL - Create Stored Procedures with optional parameters

If you want to make your code generic, say, sometimes you want to put certain criteria to the code to make it run faster,  and sometimes you just want to see all data no matter how long it will take. You can specify your declared parameter part like this:


ALTER PROCEDURE  dbo.sp_NAWAT_MISC_SpotfireInternalQC
(@state_name nvarchar(255) = NULL,
 @play_name  nvarchar(255) =  NULL
 )

 AS
BEGIN 

    ---- your code
           WHERE         (state_name = @state_name OR state_name is null)
                        AND  (play_name = @play_name OR  play_name is null) ;

END 
GO

or
(reference:SQL - Passing multiple values through one parameter in a stored procedure)

 AS
BEGIN 
  SET @Sql = '

    ---- your code
   

Spotfire - Some properties in Expressions

Properties in Expressions

---

There are three types of properties available in TIBCO Spotfire: document properties, data table properties and column properties. All of these can be used in expressions that control one or many settings. The properties can be interpreted in two different ways, either as actual values or as text values that can be used instead of column names or measures.

Insert as Text

When you want to use a string property in an expression where it should be interpreted as a column name or a measure you must use a specific syntax. The name of the property should be enclosed in the following: “${“ and “}”. So, for a document property this could look something like: ${MyProperty}. For a data table property, the first part should be the data table name within the symbols specified above, followed by a period and the property name also within curly brackets: ${My Data Table}.{MyProperty}. For a column property, the column name is also required: ${My Data Table}.{Column Name}.{MyProperty}.

When the $ sign is encountered in an expression, the property will be replaced by its corresponding value before the actual expression is evaluated (a preprocessing step). This means that if you type an expression like: sum(${Property1}) -${Property1}-${Property2}, and the value of Property1 is the column name Column1, and the value of Property2 is Column2, then the resulting expression will be: sum([Column1]) -[Column1]-[Column2].

If you do not want the property value to be interpreted as a column name but as a literal string value, you should put the property call within quotation marks. For example, if you have a property called "MyProperty" with the value "MyValue", the expression ${MyProperty} would return the resulting expression MyValue, where a column called "MyValue" is retrieved. However, the expression "${MyProperty}" would return the text string "MyValue" instead. If the string value consists of several words (e.g., "My Value"), then you should use $esc() or put brackets around the property expression in order to return a column: [${MyProperty}]. See General Syntax for more information about column names.

Note that it is very important to use the correct syntax for the expression at all times. Otherwise you may end up with a different result than you expected. If a property is inserted as a column where the expression expected a string, the first value of the specified column will be retrieved. For string columns, the result may be a valid expression, but for a column of another data type, you may receive an error.

If a property is inserted using the Insert Properties button in the Insert Calculated Column dialog or in the Custom Expression dialog, the property will automatically be added using the text (preprocessor) syntax.

Spotfire - TERR dealing with date

 It is really hard for TERR to deal with date type in Spotfire. You cannot use as.Date() in TERR which works perfectly fine in R Studio.

Reference:
https://datashoptalk.com/convert-strings-date-time-terr/#comment-3303

Spotfire data functions recognize TERR objects of class “POSIXct” as date/time information. As designed, the Spotfire/TERR data function interface for date/time information does the following:

– Converts a Spotfire value or column whose DataType is “Date”, “Time” or “DateTime” into a TERR object of class “POSIXct”.

– Converts a TERR object of class “POSIXct” into a Spotfire value or column with a DataType of “DateTime”, which can then be formatted in Spotfire to display only the date (or to display only the time) if needed.

This interface does not use any other TERR object classes (such as the “Date” class in TERR) to transfer date/time information between Spotfire and TERR.

You can do this in R Studio, but this doesn't work in TERR:

A way around it is to use strptime()

R - scraping url and download csv from website

SQL - Call Stored Procedure in a view

Typically, you can not directly EXEC a store procedure in a view, but we can use OPENQUERY() to play the trick:

But, you do need data access on the server to run Openquery().

Spotfire - Unpivot JSON data to table

import clr
clr.AddReference('System.Data')
clr.AddReference('System.Web.Extensions')
import System
from System import DateTime
from System.Data import DataSet, DataTable
from System.IO import StreamReader, StreamWriter, MemoryStream, SeekOrigin
from System.Net import HttpWebRequest
from System.Web.Script.Serialization import JavaScriptSerializer
from Spotfire.Dxp.Data import DataType, DataTableSaveSettings
from Spotfire.Dxp.Data.Import import TextFileDataSource, TextDataReaderSettings

myJson = '''[
{"Column 1": 1,"API":"42013342780000","PRODUCTION":[{"MONTH": "2011-03-01","OIL_BOE":5657,"GAS_BOE":1},{"MONTH":"2011-04-01","OIL_BOE":6328,"GAS_BOE":1}]},
{"Column 1": 2,"API":"42013309790000","PRODUCTION":[{"MONTH": "2002-01-01","OIL_BOE": 3,"GAS_BOE":1}, {"MONTH": "2002-02-01","OIL_BOE": 4,"GAS_BOE":1}]},
{"Column 1": 3,"API":"42013309800000","PRODUCTION":[{"MONTH": "2002-01-01","OIL_BOE": 5,"GAS_BOE":1}, {"MONTH": "2002-02-01","OIL_BOE": 6,"GAS_BOE":1}, {"MONTH": "2002-03-01","OIL_BOE": 7,"GAS_BOE":1}]}
]'''


# Deserialize the JSON to a .net object
flowers = JavaScriptSerializer().DeserializeObject(myJson)

strData = ''
for f in flowers:
    for x in f['PRODUCTION']:
        strData += f['API'] + '\t' + str(x['MONTH']) + '\t' + str(x['OIL_BOE'])+ '\t' + str(x['GAS_BOE']) + '\r\n'

def generateDataTableFromString(strData, tblName, colHeaders, colTypes):
    # headers
    headers = '\t'.join(colHeaders) + '\r\n'

    # append data to header
    strData = headers + strData

    # make a stream from the string
    stream = MemoryStream()
    writer = StreamWriter(stream)
    writer.Write(strData)
    writer.Flush()
    stream.Seek(0, SeekOrigin.Begin)

    # set up the text data reader
    readerSettings = TextDataReaderSettings()
    readerSettings.Separator = "\t"
    readerSettings.AddColumnNameRow(0)

    # assign data type
    for i, col in enumerate(colTypes):
        readerSettings.SetDataType(i, col)

    # create a data source to read in the stream
    textDataSource = TextFileDataSource(stream, readerSettings)

    # add the data into a Data Table in Spotfire
    if Document.Data.Tables.Contains(tblName):
        Document.Data.Tables[tblName].ReplaceData(textDataSource)
    else:
        newTable = Document.Data.Tables.Add(tblName, textDataSource)
        tableSettings = DataTableSaveSettings(newTable, False, False)
        Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)


# get parameters to build route details tables
tblName = 'well_data_1'
tblColumnsHeaders = ['API', 'MONTH','OIL_BOE','GAS_BOE']
tblColumnsTypes = [DataType.String , DataType.Date, DataType.Real  , DataType.Real] 

# generate tables
generateDataTableFromString(strData, tblName, tblColumnsHeaders, tblColumnsTypes)

 

SQL - Merge data into one row if ID are the same

Original dataset:   Desired dataset:

This can be used to change data to JSON format:

Or: