Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Dexterity Techniques

Writing Passthrough SQL or Range Where Clauses by winthropdc
Posted: 5 Jul 05

Writing Passthrough SQL or Range Where Clauses

When creating code for Passthrough SQL statements or Range Where clauses, it is important to ensure that this SQL code is compatible with all regional settings and sort orders. It is also important to ensure that when field values are passed SQL that they handled correctly.

Below are some guidelines to follow:

1) Whenever a date value is passed to SQL, use the sqlDate() global function to ensure that the date is in YYYYMMDD format.  If you use str() the date format used will depend on the regional settings and may be either MM/DD/YYYY or DD/MM/YYYY. SQL does not accept DD/MM/YYYY format and this make cause intermittent errors which occur after the 12th day of each month.

2) Whenever a string value is passed to SQL, use SQL_FormatStrings() global function to ensure that strings are wrapped in single quotes and that if a single quote exists in the string that a second single quote is added to stop the premature termination of the string.

3) Whenever a range is selected between a minimum and maximum values on a string field, the maximum value used by Dexterity may not be the correct maximum value for SQL depending on the Sort Order and collation of the SQL server.  Using the Dexterity fill command fills the field with ASCII 255 for the length of the string.  However, depending on the sort order ASCI 255 ( ) will not be the highest value and the where clause will return no results.  Below is an example which uses the system 9600 command to obtain the maximum character value for a system's sort order and then uses this to build a where clause using this value.

local string     l_MaxChar;
local integer    l_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');

David Musgrave [MSFT]
Senior Development Consultant
MBS Services - Asia Pacific

Microsoft Business Solutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.  

Back to Microsoft: Dynamics GP (Great Plains) FAQ Index
Back to Microsoft: Dynamics GP (Great Plains) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close