При создании и использовании Запросов и Представлений, основанных на стандартных операторах SQL SELECT, необходимо учитывать следующие Рекомендации, Соглашения и Ограничения.

Expand imageПользовательские функции в операторах SQL SELECT

В списке выражений выборки данных Select_Item стандартного оператора SQL SELECT вы можете использовать пользовательские функции. Тем не менее, необходимо учитывать следующие рекомендации и ограничения по использованию пользовательских функций в операторах SQL SELECT:

  • Вместо высокоуровневых пользовательских функций (VFP) рекомендуется использовать функции API или пользовательские функции, разработанные с помощью инструментария "C" или Ассемблера. Высокоуровневые пользовательские функции могут замедлять работу рассматриваемых операторов SQL SELECT.

  • При использовании пользовательских функций в операторах SQL SELECT ничего не известно об операциях ввода/вывода системы Visual FoxPro (I/O) или о текущей Среде Данных. В общих чертах, вы не знаете, какая рабочая область является в настоящий момент текущей, имя открытой Таблицы данных, имена требуемых Полей указанной таблицы. Значения конкретных переменных зависят от точного положения указателя Записи, что существенно влияет на выполнение вызываемых функций.

  • При выполнении пользовательских функций в операторах SQL SELECT не рекомендуется выполнять операции ввода/вывода Visual FoxPro (I/O) или изменять Среду Данных. В общих чертах, результаты могут быть непредсказуемыми.

  • Единственным надежным способом параметризации пользовательских функций, вызываемых в операторах SQL SELECT, является передача списка параметров.

  • Понимание термина "forbidden" - правильного разрешения ссылок, обеспечивает получение правильных результатов Запросов в одной версии Visual FoxPro, и совсем других результатов - в другой версии VFP.

Помимо перечисленных Замечаний по правильному использованию пользовательских функций в операторах SQL SELECT. Тем не менее, помните, что использование рассматриваемых функций, в общем случае, замедляет выполнение рассматриваемого оператора SQL SELECT.

Дополнительную информацию о построении пользовательских функций смотри в разделе: Пользовательские Процедуры и Функции.

Expand imageАгрегатные Функции

Вы можете использовать Агрегатные функции:  1) в списке элементов Выборки Select_Item, как Поле или как Выражение, содержащее поле; 2) в составе условия Фильтрации ключевой секции HAVING. Тем не менее, вы не можете использовать вложенные агрегатные функции.

В следующей таблице приводится список агрегатных функций, которые можно использовать в операторах SQL SELECT.

Агрегатная Функция Описание

AVG( )

Вычисление Среднего значения указанного столбца числового типа.

COUNT( ) or CNT( )

Вычисление количества выбранных элементов стобца. COUNT(*) вычисляет количество выбранных Записей Запроса.

MIN( )

Определяет минимальное значение столбца из списка Select_Item.

MAX( )

Определяет минимальное значение столбца из списка Select_Item.

SUM( )

Вычисление общей суммы указанного столбца числового типа.

В следующем примере создается Запрос, в котором имена столбцов определяются соответствующими вычисляемыми функциями.:

  CopyCode imageКопировать Код
CLEAR ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT AVG(TAlias1.order_amt), MIN(TAlias1.order_amt) ;
 FROM Orders AS TAlias1

Дополнительная информация по использованию агрегатных функций в операторах SQL SELECT смотрите в разделах: MIN( ), функция , MAX( ), функция, а также: CALCULATE, команда, которые содержат также информацию о функциях: AVG( ), COUNT( ) ,  CNT( ),  SUM( ).

Expand imageПравила формирования Имен результирующих столбцов

Следующие правила относятся к случаям, когда Имя результирующего столбца Запроса указывается в опции AS:

  • Если элемент Select_Item указывается как имя Поля, данное имя должно быть уникальным в результирующем наборе Запроса.

  • Если несколько элементов Select_Item имеют одинаковое имя, то к имени добавляется символ подчеркивания (_) и некоторый символ (_a, _b,_c...).

    Например, если в операторе SQL SELECT выполняется выборка поля Cust_ID из двух таблиц Customer и Orders, то имена новых столбцов формируются по шаблону: FieldName_a и FieldName_b, или точнее, Cust_ID_a и Cust_Id_b. В следующем примере имена результирующих столбцов формируются по указанным правилам:

      CopyCode imageКопировать Код
    CLEAR ALL
    CLOSE DATABASES
    OPEN DATABASE (HOME(2) + 'Data\TestData')
    SELECT TAlias1.cust_id, TAlias2.cust_id ;
       FROM Customer AS TAlias1, Orders AS TAlias2 ;
       WHERE TAlias1.cust_id = TAlias2.cust_id

    Для свободных таблиц, если в операторе SQL SELECTформируются несколько столбцов с одинаковыми именами, которые не могут иметь длину более 10 символов, сначала имя укорачивается, а за тем добавляется описанный номер повторения. Например, для дублируемого столбца с именем Department новые столбцы имеют имена Department и Departmen2.

  • Если элемент выбираемого списка Select_Item является некоторым Выражением, то в результирующем наборе Запроса появится имя столбца Exp_1. Прочие имена аналогично формируемых столбцов будут иметь вид Exp_2, Exp_3, и так далее. В следующем примере имена результирующих столбцов формируются описанным выше способом:

      CopyCode imageКопировать Код
    CLEAR ALL
    CLOSE DATABASES
    OPEN DATABASE (HOME(2) + 'Data\TestData')
    SELECT TAlias1.postalcode+"-1234", TAlias2.postalcode+"-5678" ;
     FROM Customer AS TAlias1, Orders AS TAlias2 ;
     WHERE TAlias1.cust_id = TAlias2.cust_id

  • Если элементом выбираемого списка является агрегатная функция, например, COUNT( ), то новый столбец будет иметь имя  Cnt_FieldName. Если несколько выбираемых элементов Select_Item представляют из себя агрегатные функции, имена новых столбцов формируются по шаблону AggregateFunction_FieldName, где присутствует аббревиатура соответствующей агрегатной функции. В следующем примере имена столбцов формируются указанным способом:

      CopyCode imageКопировать Код
    CLEAR ALL
    CLOSE DATABASES
    OPEN DATABASE (HOME(2) + 'Data\TestData')
    SELECT COUNT(order_id), SUM(order_net)FROM Orders

    Дополнительная информация содержится в разделе: Агрегатные Функции.

Expand imageОперации Объединения (UNION)

При использовании операций Объединения (UNION) между несколькими операторами SQL SELECT, нужно учитывать следующие предупреждения и ограничения:

  • Все списки выбираемых элементов в нескольких операторах выборки, объединенных ключевой секцией UNION должны содержать одинаковое количество выбираемых элементов (имен столбцов, некоторых выражений, агрегатных функций, и так далее...).

  • Соответствующие столбцы в результирующем наборе Запроса, полученного при объединении UNION, или столбцы из подзапроса в некотором из запросов, должны быть одного типа данных, или допускать преобразование одного типа данных в другой, или представлять из себя буквальное преобразование одного типа данных в требуемый тип. Например, если выполняется объединение (UNION) между столбцом типа DateTime и столбцом одного из бинарных типов (binary data type),  то данное объединение неосуществимо, без явного преобразования одного из указанных типов в другой. Тем не менее, объединение UNION будет работать между столбцом с типом данных (money)  и столбцом типа (integer), так как данные типы взаимно конвертируемы.

    Столбцы типа (xml) должны бать эквивалентными. Все столбцы должны соответствовать одной схеме XML, или быть нетипизированными. Если они типизированы, то должны соответствовать одним типам данных в заданной схеме XML.

  • Имена результирующих столбцов, полученных из нескольких Запросов, объединенных ключевой секцией UNION, определяются первым списком выборки, первым оператором SQL SELECT. Данные имена столбцов результирующего набора могут быть использованы в общих секциях Запроса (к примеру, в секции группировки ORDER BY), ссылка на имя столбца должна соотвентствовать первому оператору SELECT.

  • Соответствующие столбцы в каждом отдельном операторе Выборки, которые объединяются с помощью ключевой секции UNION, должны быть размещены в одинаковом порядке; секция UNION выполняет сравнения столбцов один-к-одному по порядку расположения столбцов в каждом операторе Выборки.

    Когда происходит объединение различных типов данных с помощью секции UNION, расположенных в соответствующих столбцах, преобразование выполняется по правилам Приоритета типов данных. В предыдущем примере выполнялось преобразование типа данных int - в тип данных float, в силу того, что тип float имеет больший приоритет, чем int.

Приоритеты преобразования типов данных в операциях объединения (UNION)

До версии Visual FoxPro 8.0, разработчик был вынужден выполнять явное преобразование несоответствующих типов данных для соответствующих столбцов, объединяемых в секции UNION оператора выборки SQL SELECT. Тем не менее, в настоящей версии Visual FoxPro, существует поддержка автоматической конвертации несоответствующих типов данных.

Явное преобразование типов данных требует использования соответствующих конверсионных функций системы Visual FoxPro, таких как CTOD( ), в тоже время, неявное преобразование типов данных не требует использования конверсионных функций.

Когда система Visual FoxPro выполняет объединение столбцов с различными типами данных в секции UNION, тип данных с меньшим приоритетом конвертируется в тип данных с большим приоритетом. Поля со свойством NULL имеют больший приоритет над полями со свойством NOT NULL.

В следующей таблице представлены все Явные и Неявные преобразования типов данных, выполняемые системой Visual FoxPro:

Тип Данных Неявное Преобразование Явное Преобразование

Character

Character (Binary)

CTOD( ), CTOT( ), VAL( ), CTOBIN( )

Character (Binary)

 

 

Currency

 

MTON( )

Date

DateTime

DTOC( ), DTOS( ), DTOT( )

DateTime

 

TTOC( ), TTOD( )

Double

 

STR( ), VAL( )

Float

Numeric

NTOM( ), STR( ), INT( )

Integer

Numeric, Float, Double, Currency

BINTOC( )

Logical

 

 

Numeric

Float

NTOM( ), STR( ), INT( )

В следующей таблице иллюстрируются результаты Неявного преобразования типов данных при операции объединения (UNION) .

Тип данных 1 Тип данных 2 Ожидаемый тип Данных

Character (N)

Character (X)

Character (MAX(N,X))

Character (N)

Character Binary (X)

Character Binary (MAX(N,X))

Character (N)

Memo

Memo

Character Binary (N)

Character Binary (X)

Character Binary (MAX(N,X))

Character Binary (N)

Memo

Memo

Currency

Currency

Currency

Date

Date

Date

Date

DateTime

DateTime

DateTime

DateTime

DateTime

Double (N)

Float (X,Y)

Float (MAX(MAX(8,Y),2))

Double (N)

Integer

Double (N)

Double (N)

Numeric (X,Y)

Double (MAX(MAX(8,Y),2))

Double (X)

Double (Y)

Double (MAX(X,Y))

Float (N,M)

Double (X)

Float (20, MAX(M,X))

Float (N,M)

Float (X,Y)

Float (MAX(N,M), MAX(X,Y))

Float (N,M)

Numeric (X,Y)

Float (MAX (N,X), MAX(M,Y))

Integer

Currency

Currency

Integer

Double (X)

Double (X)

Integer

Float (X,Y)

Float (MAX(11,X), Y)

Integer

Integer

Integer

Integer

Numeric (X,Y)

Numeric (MAX(11,X), Y)

Logical

Logical

Logical

Numeric (N,M)

Double (X)

Numeric (20, MAX(M,X))

Numeric (N,M)

Float (X,Y)

Float (MAX(N,X), MAX(M,Y))

Numeric (N,M)

Numeric (X,Y)

Numeric (MAX(N,X), MAX(M,Y))

Varchar (X)

Character (Y)

Varchar (MAX(X,Y))

Varchar Binary (X)

Character Binary (Y)

Varchar Binary (MAX(X,Y))

Дополнительную информацию о Типах данных и конверсионных функциях смотри в разделах: Типы Данных и Полей Таблиц и Функции преобразования Данных.

Expand imageСм. также