SQL syntax FAQs
The SQL syntax that is supported varies according to the OLE DB Provider that you are using. This topic applies to the UNICOM Intelligence OLE DB Provider (which is part of the UNICOM Intelligence Data Model) only.
Is it possible to use multiple SQL statements in an update query in the InputDataSource and OutputDataSource sections?
The UNICOM Intelligence Developer Documentation Library- November 2002 said that this was possible. However, it is not currently possible to use more than one SQL statement in an update query when you are accessing the case data through the UNICOM Intelligence Data Model.
If you are using another OLE DB provider to access data in another format, you might be able to use multiple SQL statements in an update query if this supported by the OLE DB provider you are using.
I am getting unexpected results when I use non-US English formatting of date, time, and numeric values.
Different formatting conventions are used in different countries for dates and times and for the decimal and thousands separators used in numeric data. The UNICOM Intelligence Data Model uses the locale setting to parse and format date, time, and numeric data. This handling is generally invisible. For example, when your locale is set to Danish, the UNICOM Intelligence Data Model will accept and format data using the Danish formatting customs and when your locale is set to US English, it will accept and format data according to the US English formatting customs. (You select the locale using Regional Options in the Windows Control Panel.)
However, this handling does not apply to literals in SQL queries and mrScriptBasic. A literal is a string of characters that represents a value. Literals are often used in scripts and SQL queries when you want to compare values. For example, the following SQL query uses a literal to select respondents whose income is above a specified value:
SELECT Respondent.Serial, Income FROM vdata WHERE Income > 10000.00
You must always specify literals using US English formatting. For example, if you specify the literal in this query using Danish formatting (10000,00), a syntax error occurs. However, you can use the
Conversion functions to convert locale-specific date, time, and numeric values. For example, if you are working in a Danish locale, you could use the following query:
SELECT Respondent.Serial, Income FROM vdata WHERE Income > CDouble(10000,00)
When I am using UNICOM Intelligence Professional to transfer data from a .sav file, why are the category value IDs that I need to specify in the WHERE clause different from the value IDs used in the .sav file? For example, in my .sav file, the gender variable is defined like this:
Name: gender
Type: numeric
Width: 10
Decimals: 0
Label: Gender
Values: 1 = "Male"
2 = "Female"
Missing: None
Columns: 8
Align: Right
Measure: Scale
However, when I want to specify one of the categories in the WHERE clause, I need to specify IDs of 13 and 14. For example:
SELECT * FROM vdata WHERE gender = 13
Why is this?
The UNICOM Intelligence Data Model gives a unique value to each category in the MDM Document. This is known as the MDM mapped category value. When you read a .sav file in a DMS file, you are reading it through the UNICOM Intelligence Data Model. This means that you are using the select query through the UNICOM Intelligence Data Model and it is looking at these mapped values. However, you can also specify the category in the WHERE clause using the category names, which you might find easier to use. The UNICOM Intelligence Data Model bases the category names on the value labels in the .sav file. For example:
SELECT * FROM vdata WHERE gender = {Male}
The advantage of this is that you specify the queries in the same way regardless of the underlying format of the data. For example, this query would be equally valid when reading data stored in a Quanvert or relational MR database through the UNICOM Intelligence Data Model.
For more detailed information about how the UNICOM Intelligence Data Model interprets the data in the .sav file, see the SPSS Statistics SAV DSC documentation in the Available DSCs section of the UNICOM Intelligence Developer Documentation Library.
See also