DBTable.CursorLocation

SVB Example

Advanced queries using the Statistica OLE DB provider:

Sub Main

    'Connect to the dataset "Cat Clinic"
    Dim streamingDB As New DBTable
    streamingDB.ConnectionString = "Provider=STATISTICA.StaOLEDB.1;Data Source=" & Path + _
        "\examples\datasets\cat clinic.sta" & ";Integrated Security=SSPI;"
    'Here, we will query the columns "Weight (2000)" and "GENDER",
    'but will then augment the data and column names to be returned in German.
    'The returned column names are altered using the AS command.
    'The text labels are altered by using a "CASE WHEN...THEN...ELSE" statement.
    streamingDB.QueryString = "SELECT CASE WHEN GENDER = 'MALE' THEN 'Männlich' ELSE 'Weiblich' END AS 'Geschlecht', ""Weight (2000)"" AS 'Gewicht (2000)' FROM 'cat clinic'"
    streamingDB.AsyncQuery = False
    streamingDB.CursorLocation = scServerSideCursor
    streamingDB.CursorType = scForwardOnlyCursor
    streamingDB.LockType = scLockReadOnly
    streamingDB.RunQuery

    'Create a regular spreadsheet from the query
    Dim spr As Spreadsheet
    Set spr = streamingDB.Subset("*")
    spr.Header.Value = "Cat Clinic: Auf Deutsch"
    spr.Visible = True

    'Query only the average of a column (using the AVG function),
    'based on a subset of female patients (using a WHERE clause).
    streamingDB.QueryString = "SELECT AVG(""Weight (1998)"") AS WEIGHT98_AVG FROM 'cat clinic' WHERE GENDER='FEMALE' GROUP BY GENDER"
    streamingDB.RunQuery
    Set spr = streamingDB.Subset("*")
    spr.Header.Value = "Cat Clinic: Average (means) of ""Weight (1998)"" for female patients."
    spr.Visible = True

    'Create a new categorical column with labels based on the values of another column.
    'In this example, we are assigning the label "Obese" to cats that are 15 or more pounds,
    '"Overweight" to cats 11 or more pounds, and "Normal" for the rest. This is achieved using
    'the "CASE WHEN...THEN...ELSE" statement.
    streamingDB.QueryString = "SELECT ""Patient ID"", CASE WHEN ""Weight (2000)"" >= 15 THEN 'Obese' WHEN ""weight (2000)"" >= 11 THEN 'Overweight' ELSE 'Normal' END AS WEIGHT_CLASS FROM 'cat clinic'"
    streamingDB.RunQuery
    Set spr = streamingDB.Subset("*")
    spr.Header.Value = "Cat Clinic: Weight classifications of patients."
    spr.Visible = True

    'Create a list of unique hair colors using the DISTINCT command.
    'We will also sort them using the ORDER BY command.
    streamingDB.QueryString = "SELECT DISTINCT ""Hair Color"" FROM 'cat clinic' ORDER BY ""Hair Color"""
    streamingDB.RunQuery
    Set spr = streamingDB.Subset("*")
    spr.VariableType(1) = scInteger 'Use text labels
    spr.Header.Value = "Cat Clinic: Unique hair colors."
    spr.Visible = True

    'Query only records where the hair color contains black. This can be achieved using
    'the LIKE command.
    streamingDB.QueryString = "SELECT ""Patient ID"", ""Hair Color"" FROM 'cat clinic' WHERE ""Hair Color"" LIKE '%black%'"
    streamingDB.RunQuery
    Set spr = streamingDB.Subset("*")
    spr.Header.Value = "Cat Clinic: Hair colors containing black."
    spr.Visible = True

    'Close the DBTable
    Set spr = Nothing
    streamingDB.Close

End Sub