×

Support for Oracle

For Oracle Do Not add a final ';' in the script.
 
If there is a final ";" or empty lines, the following error may occur:
 
 
Example for replacing Email addresses:
SELECT CUSTOMER.USER_1 AS emailAddress
  FROM CUSTOMER
    INNER JOIN CUSTOMER_ORDER
      ON CUSTOMER.ID = CUSTOMER_ORDER.CUSTOMER_ID
      INNER JOIN SHIPPER
        ON CUSTOMER_ORDER.ID = SHIPPER.CUST_ORDER_ID
  WHERE SHIPPER.PACKLIST_ID = :PACKLIST_ID
 
 
 
Generating Html code to be used within the body of emails:
SELECT '<H2><font color=DarkBlue>SHIPPING CONFIRMATION</H2>' || '<H3><font color=DarkBlue>Dear ' || CUSTOMER.NAME || ', your order <font color=Red>' || CUSTOMER_ORDER.ID || '<font color=DarkBlue> has shipped.</H3>' || '<H3><font color=DarkBlue> <u> Details: </u>' || '<p> <b><font color=DarkBlue>Shipped via: </b> <font color=Red>' || NVL(CUSTOMER_ORDER.SHIP_VIA, '-') || '</p>' || '<p> <b><font color=DarkBlue>Ship to: </b> <font color=Red> ' || NVL(CUST_ADDRESS.NAME, '-') || '</p>' || '<p> <b><font color=DarkBlue>Address: </b> <font color=Red> ' || ( NVL(CUST_ADDRESS.ADDR_1, '-') || CHR(13) || CHR(10) || NVL(CUSTOMER.ADDR_2, '-') || CHR(13) || CHR(10) || NVL(CUSTOMER.ADDR_3, '-') || CHR(13) || CHR(10) || NVL(CUST_ADDRESS.CITY, '-') || CHR(13) || CHR(10) || NVL(CUST_ADDRESS.STATE, '-') || CHR(13) || CHR(10) || NVL(CUST_ADDRESS.ZIPCODE, '-') || CHR(13) || CHR(10) || NVL(CUST_ADDRESS.COUNTRY, '-') )|| '</p>' || '<br>'
||  '<p><button type=button>Track your package</button></p>'
 
|| '<p><font color=DarkBlue>Should you wish TO contact us FOR ANY reason please contact our sales team www.bms365.com</p>'
 
AS HTMLOUTPUT
  FROM CUSTOMER
    INNER JOIN CUSTOMER_ORDER
      ON CUSTOMER.ID = CUSTOMER_ORDER.CUSTOMER_ID
      INNER JOIN SHIPPER
        ON CUSTOMER_ORDER.ID = SHIPPER.CUST_ORDER_ID
        LEFT OUTER JOIN CUST_ADDRESS
          ON CUSTOMER_ORDER.CUSTOMER_ID = CUST_ADDRESS.CUSTOMER_ID
          AND CUSTOMER_ORDER.SHIP_TO_ADDR_NO = CUST_ADDRESS.ADDR_NO
  WHERE SHIPPER.PACKLIST_ID = :PACKLIST_ID
 
The code shown above will create a new column called HTMLOUTPUT which will be available from the module's grid (it can be made not visible if desired). Since the column is now available from the grid, it can be also used to build the email's subject or body. As any other field, it can be referenced by adding "@" as a prefix (see snapshot below)