SQL Example - Lookup table for Zip to Zone, and lookup table for Zone/Weight to Rate

If you have a complex rate lookup, or if you just prefer to write your rate calculation as a SQL Server stored procedure, Shipping Director has the ability to do SQL queries and call stored procedures. 

If an expression starts with one of these keywords, then the expression is evaluated as a SQL query:

            SELECT

            ?SELECT"

            EXECUTE"

            ?EXECUTE

Note that there are two forms of SELECT and EXECUTE; one form is prefixed with a “?”.  Without the leading “?” the query is treated as a standard SQL paramaterized query using “@”-prefixed parameters.  For example:

select Zone from ShippingZone_Origin23235 where @p1 between PrefixFrom and PrefixTo; [ZipCode3]

 This default behavior should be used when there is a concern for SQL injection.  (It is also recommended to use ErrorExit to test for for such possibilities.)

With the leading “?” the query can contain zero-based indexed placeholders (format items to be used in String.Format).  For example:

?select Zone from ShippingZone_Origin{0} where '{1}' between PrefixFrom and PrefixTo;[OriginZipCode],[ZipCode3]

This provides the ability to create truly dynamic queries.  In the above example, the table name contains a format specifier.

The result of a SQL expression needs to be a single scalar value (i.e. a String, a Decimal, etc.).  Typically the expression is assigned to a variable.

An example of using SQL queries would be to do a custom rate lookup from a set of Zone/Weight/Rate  tables in the database.  The first table would be used to lookup a Zone base on a shipping destination zip code, and the second table would use the looked up Zone and the total weight of items in the cart to lookup the rate.


ShippingZone_Origin23235

PrefixFrom

PrefixTo

Zone

120

126

4

127

127

3

128

147

4

148

163

3

164

165

4

166

172

3

ShippingZone_Rate

Weight

Zone2

Zone3

Zone4

Zone5

Zone6

Zone7

Zone8

Zone9

1

5.17

5.4

5.51

5.75

6.04

6.12

6.22

19.18

2

5.37

5.72

6.22

6.34

6.75

6.89

7.13

21.3

3

5.45

5.97

6.53

6.73

7.15

7.36

7.88

23.21

4

5.58

6.16

6.86

7.17

7.53

7.85

8.44

25.37

5

5.79

6.25

7.15

7.46

7.83

8.2

8.91

27.63

6

5.96

6.44

7.27

7.65

7.97

8.44

9.1

30.01


The first option is to use parameterized queries (“select” without the “?” prefix).  In this example, a constant Origin Zip Code is provided, but this could instead be a calculation of a specific warehouse based on what is in the cart.

SQL queries using parameterized queries

 

Order

Type

Name

Expression

 Rate

 Expression

200

String

ZipCode3

ShippingAddress.ZipPostalCode.Substring(0,3)

 

300

String

Zone

select Zone from ShippingZone_Origin23235 where @p1 between PrefixFrom and PrefixTo; [ZipCode3]

 

350

Decimal

ZoneRate

select Rate from ShippingZone_Rate where Zone = @p1 and Weight = Ceiling(@p2); [Zone],[$TotalWeight]

 

500

OptionExit

Shipping Cost

true

 [ZoneRate]


 

 

 

 

Use a stored procedure to do the lookup  (replace lines 300 and 350 above with just this line 300)

300

 

Decimal

ZoneRate

EXECUTE ShippingZone_LookupRate @p1, @p2; [ZipCode3], [$TotalWeight]

 

The second option is to use the “?” prefix so that the table name can be dynamically determined.  In this example, a constant Origin Zip Code is provided, but this could instead be a caclulation of a specific warehous based on what is in the cart. 

SQL queries using zero-based index placeholders

Order

Type

Name

Expression

 Rate

 Expression

100

String

OriginZipCode

"23235"

 

200

String

ZipCode3

ShippingAddress.ZipPostalCode.Substring(0,3)

 

300

String

Zone

?select Zone from ShippingZone_Origin{0} where '{1}' between PrefixFrom and PrefixTo;[OriginZipCode],[ZipCode3]

 

350

Decimal

ZoneRate

?select Zone{0} from ShippingZone_Rate where Weight = Ceiling({1}); [Zone],[$TotalWeight]

 

500

OptionExit

Shipping Cost

true

 [ZoneRate]

 

 

 

 

 

Use a stored procedure to do the lookup  (replace lines 300 and 350 above with just this line 300)

300

 

Decimal

ZoneRate

?EXECUTE ShippingZone_LookupRate '{0}',{1}; [ZipCode3], [$TotalWeight]


Here's an example stored procedure.  Note that the SELECT statements only return a single column (and should only return a single row)

CREATE PROCEDURE [dbo].[ShippingZone_LookupRate]
(
    @zip     VARCHAR(9),
    @weight  DECIMAL(8,4)
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @zone VARCHAR(3);
    IF @WEIGHT = 0 
      SET @weight = 1;

    select @zone = Zone from ShippingZone_Origin23235 where SUBSTRING(@zip,1,3) between PrefixFrom and PrefixTo

    IF      @zone = '2' select Zone2 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '3' select Zone3 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '4' select Zone4 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '5' select Zone5 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '6' select Zone6 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '7' select Zone7 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '8' select Zone8 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '9' select Zone9 from ShippingZone_Rate where Weight = Ceiling(@weight)    
END

Tags :  SQLLookupTable
Comments
Leave your comment
:
rick.heidrick@shaw.ca
Created on: 10/16/2012 7:36 PM
I am not able to get this to work. I am checking the zip (Canadian Postal Code) to see if it exists in a table so as to give free shipping:

String   ZipCode3   ShippingAddress.ZipPostalCode

Decimal  ZoneRate  select rate from FreeShippingTbl where (zip = @p1) OR zip = (SUBSTRING(@p1, 1, 3) + ' ' + SUBSTRING(@p1, 4, 7)) AND active = 1; [ZipCode]

OptionExit  Shipping Cost  ZoneRate

The rate will always be 0 if found in the table (tested using 4.50 as a rate)

The error: Sorry, we are unable to calculate your shipping. Please contact the store.
support@nopTools.com
Created on: 10/25/2012 9:43 PM
Whenever you get errors, check the System > Log for the details.  In this case, it's just a typo - you have variable declared as ZipCode3, and are referencing ZipCode.
informatica@comerbal.com
Created on: 2/1/2013 8:11 AM
I am evaluating the product. I get an error when use sql statements.
In log, the error message say:
ShippingDirector Error - Customer=355:aib@jmbusquets.com; Record=89: 500 Portes; Expression=select max(ShippingChargeAmount) from ShippingEnvialia; Error=EvaluateQueryExpression - Field:Portes, Expr=select max(ShippingChargeAmount) from ShippingEnvialia, Error=Index was outside the bounds of the array.

Can help me?
Thanks
support@nopTools.com
Created on: 2/3/2013 12:49 PM
The select expression is expecting a parameter.  Just add something (e.g. a zero) after the semicolon:

   select max(ShippingChargeAmount) from  ShippingEnvialia;0

(This will be fixed in a future release)
sample@email.tst
Created on: 6/26/2023 5:31 PM
1
sample@email.tst
Created on: 6/26/2023 5:31 PM
1