Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to write "Passthrough" SQL statements and "Range Where" clauses in Microsoft Great Plains Dexterity

How to write "Passthrough" SQL statements and "Range Where" clauses in Microsoft Great Plains Dexterity

View products that this article applies to.

INTRODUCTION

This article describes how to write Passthrough SQL statements and Range Where clauses in Microsoft Dynamics GP Dexterity. 

↑ Back to the top

More Information

When you write Passthrough SQL statements and Range Where clauses in Dexterity, make sure that the Transact SQL code is compatible with all regional settings and sort orders. Also, make sure that field values that are passed to Microsoft SQL Server are handled correctly. To do this, use the following methods:
  • If you want to pass a date value to Microsoft SQL Server, use the Dexterity sqlDate() global function to make sure that the date is in YYYYMMDD format. If you use the Dexterity str() function, the program uses either the MM/DD/YYYY format or the DD/MM/YYYY format, depending on the regional settings. SQL Server does not accept the DD/MM/YYYY format because this format may cause intermittent errors that occur after the twelfth day of each month.

    Note The sqlDate function does not add the single quotation marks that are required when you pass the date to SQL Server. Therefore, enclose the result of the function by using single quotation marks.
  • If you want to pass a string value to SQL Server, use the Dexterity SQL_FormatStrings() global function to make sure that strings are wrapped in single quotation marks. If a single quotation mark exists in the string, you must add a second single quotation mark to stop the premature termination of the string.
  • When a range is selected between the minimum and maximum values on a string field, the maximum value that is used by Dexterity may not be the correct maximum value for SQL Server. The correct maximum value for SQL Server depends on the sort order and the collation of the instance of SQL Server. By using the Dexterity fill command, the program fills the field by using ASCII 255 for the length of the string. However, depending on the sort order, ASCII 255 may not be the highest value. Therefore, the where clause will return no results. The maximum value for ASCII 255 is ÿ (lowercase y with umlaut). This character is treated as a Y by some SQL sort orders. This means that the end of the range is set to Y in MSSQL and that anything that starts with the character Z is not included in the range. The following Dexterity code is an example that uses the system 9600 command to obtain the maximum character value for a system's sort order. The code then uses this value to build a where clause.
    local string l_MaxChar;
    local integerl_Result,l_Length;

    system 9600, table SOP_HDR_WORK, l_MaxChar, l_Result;

    if empty('End Location Code') or filled('End Location Code') then
    fill 'End Location Code';
    l_Length = length('End Location Code');
    clear 'End Location Code';
    'End Location Code' = pad('End Location Code', TRAILING, l_MaxChar, l_Length);
    end if;

    range table SOP_HDR_WORK where physicalname('Location Code' of table SOP_HDR_WORK) + CH_SPACE + CH_GREATERTHAN + CH_EQUAL + CH_SPACE
    + SQL_FormatStrings('Start Location Code') + CH_SPACE + SQL_AND + CH_SPACE
    + physicalname('Location Code' of table SOP_HDR_WORK) + CH_SPACE + CH_LESSTHAN + CH_EQUAL + CH_SPACE
    + SQL_FormatStrings('End Location Code');

↑ Back to the top

Keywords: kbnosurvey, kbmbspartner, kbmbsmigrate, kbhowto, kb

↑ Back to the top

Article Info
Article ID : 910129
Revision : 1
Created on : 3/13/2017
Published on : 12/29/2014
Exists online : False
Views : 192