Blog posts tagged with 'SQL'

RSS
Option record's RateExpression supports SQL statement- Friday, November 27, 2015

In the Rate Expression of an Option (or OptionExit) type record, put in a SQL statement (or call a stored procedure).  It must return a table of Shipping Options - columns: Name VARCHAR, Description VARCHAR, Rate Decimal. 

For example, if you can set up Warehouses as locations for local Pickup:

SELECT w.Name,
       Address1
         + case when isnull(address2,'') = '' then '' else ' ' + Address2 end
         + ', ' + a.City + ', ' + s.Abbreviation + ' ' + ZipPostalCode  as Description,
       0.00 as Rate
  FROM Warehouse w
  JOIN Address a ON a.Id = w.AddressId
  JOIN StateProvince s on s.Id = a.StateProvinceId;

Type

Name

Expression

Rate Expression

Option

Warehouses for local pickup

true

SELECT w.Name, Address1 + case when isnull(address2,'') = '' then '' else ' ' + Address2 end + ', ' + a.City + ', ' + s.Abbreviation + ' ' + ZipPostalCode as Description, 0.00 as Rate FROM Warehouse w JOIN Address a ON a.Id = w.AddressId JOIN StateProvince s on s.Id = a.StateProvinceId;

The customer sees: 

3

Shipping method

  •  
    10 Main St, Farmingdale, NY 11735
  •  
  •  
    15 Park Ave, New York, NY 10021

Tags :  SQL
Comments (2)
SQL Example - Lookup table for Zip to Zone, and lookup table for Zone/Weight to Rate- Sunday, February 12, 2012

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 (6)