Professional Web Applications Themes

Query of Queries problem - Coldfusion - Advanced Techniques

I believe there to be issues with the subquery's use of the word 'Order' (reserved)... so I used the hard brackets like the good manual said I should. Here's the error message from the CFMX Scheduler: Error Executing Database Query.<br><b>Query Of Queries runtime error.</b><br> Unsupported type comparison. The specific sequence of files included or processed is: Z:\wwwroot\intranet\www\confirmations\orderconfirm .cfm I posted this last week, but proposed changes didn't yield the correct results. So I thought I'd throw it back out there to see if any of you can lend a hand. Many thanks in advance. Nate <cfquery name="getCurrentOrder" datasource="intranet"> SELECT Order ...

  1. #1

    Default Query of Queries problem

    I believe there to be issues with the subquery's use of the word 'Order'
    (reserved)... so I used the hard brackets like the good manual said I should.
    Here's the error message from the CFMX Scheduler:

    Error Executing Database Query.<br><b>Query Of Queries runtime error.</b><br>
    Unsupported type comparison. The specific sequence of files included or
    processed is: Z:\wwwroot\intranet\www\confirmations\orderconfirm .cfm

    I posted this last week, but proposed changes didn't yield the correct
    results. So I thought I'd throw it back out there to see if any of you can
    lend a hand. Many thanks in advance.

    Nate



    <cfquery name="getCurrentOrder" datasource="intranet">
    SELECT Order
    FROM OrdConfInd
    </cfquery>

    <cfquery name="getMomOrders" datasource="momlink">
    SELECT Cms.Order, Cms.Sales_ID, Cust.Custnum, Cust.FirstName, Cust.LastName,
    Cust.Email
    FROM Cms, Cust
    WHERE Cms.Custnum = Cust.Custnum AND Cust.Email IS NOT NULL
    </cfquery>

    <cfquery name="getOrdersMMConfirm" dbtype="query">
    SELECT getMomOrders.[Order], getMomOrders.Sales_ID, getMomOrders.Custnum,
    getMomOrders.FirstName, getMomOrders.LastName, getMomOrders.Email
    FROM getCurrentOrder, getMomOrders
    WHERE getMomOrders.[Order] > getCurrentOrder.[Order] AND
    getMomOrders.Sales_ID <> 'EA'
    </cfquery>

    <cfquery name="getOrdersCSConfirm" dbtype="query">
    SELECT getMomOrders.[Order], getMomOrders.Sales_ID, getMomOrders.Custnum,
    getMomOrders.FirstName, getMomOrders.LastName, getMomOrders.Email
    FROM getCurrentOrder, getMomOrders
    WHERE getMomOrders.[Order] > getCurrentOrder.[Order] AND
    getMomOrders.Sales_ID = 'EA'
    </cfquery>


    <cfif getOrdersMMConfirm.RecordSet gt 0>
    <cfloop query="getOrdersMMConfirm">
    <CFMAIL to="#email#" from="com" subject="Order
    Confirmation" TYPE="HTML">
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <title>Blah - Order Confirmation</title>
    <meta http-equiv="Content-Type" content="text/html; cht=iso-8859-1">
    </head>

    <body>
    <table width="564" border="0" cellspacing="0" cellpadding="0">
    <tr><td>
    Dear #FirstName# #LastName#,
    <br><br>
    This email is to confirm that your order is being processed. Your Order
    Number is [#Order#]. Your Customer Number is #Custnum#.
    <br><br>
    WE TRY TO SHIP EVERY ORDER WITHIN 2 DAYS FROM THE DATE WE RECEIVE IT, but the
    coin business can be unpredictable at times and in-stock merchandise may
    fluctuate, causing delays in shipping. If your order is a Special Purchase
    item, please allow up to 2-6 weeks from the date of purchase for delivery.
    If you would like to inquire about your order please send an email to <a
    href="mailto:com">com</a> and include your Order Number
    and your Customer Number. As stated above, please allow up to 14 days (and up
    to 2-6 weeks for Special Purchase items) for delivery of your order before
    sending an inquiry.
    <br><br>
    If you have any questions, please DO NOT RESPOND TO THIS EMAIL. Please
    contact our Customer Service team at: <a
    href="mailto:com">com.</a>
    Thank you again for shopping with us! We appreciate your business and will
    ship your order as soon as possible.
    <br><br>
    Sincerely,
    <br>
    Blah
    <br><a href="http://www.blah.com">http://www.blah.com</a>
    </td></tr>
    </table>
    </body>
    </html>
    </CFMAIL>
    <cfset Shipped = False>
    <CFQUERY name="InsertOrdConfirm" DATASOURCE="intranet">
    INSERT INTO OrdConfSent
    (Order,Custnum,FirstName,LastName,Email,Sales_ID,S hipped)

    VALUES ('[#Order#]','#Custnum#','#FirstName#','#LastName#','#Email#' ,'#Sales_I
    D#','#Shipped#')
    </cfquery>
    </cfloop>
    <cfset UpdateOrder = Order>
    </cfif>

    <cfif getOrdersCSConfirm.RecordSet gt 0>
    <cfloop query="getOrdersCSConfirm">
    <CFMAIL to="#email#" from="com" subject="Order
    Confirmation" TYPE="HTML">

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <title>Blah - Order Confirmation</title>
    <meta http-equiv="Content-Type" content="text/html; cht=iso-8859-1">
    </head>

    <body>
    <table width="564" border="0" cellspacing="0" cellpadding="0">
    <tr><td>
    Dear #FirstName# #LastName#,
    <br><br>
    This email is to confirm that your order is being processed. Your Order
    Number is [#Order#]. Your Customer Number is #Custnum#.
    <br><br>
    If you would like to inquire about your order please send an email to <a
    href="mailto:com">com</a> and
    include your Order Number and your Customer Number. As stated above, please
    allow up to 14 days (5 weeks for international shipments sent by USPS standard
    Air Mail) for delivery of your order before sending an inquiry.
    <br><br>
    If you have any questions, please DO NOT RESPOND TO THIS EMAIL. Please
    contact our Customer Service team at: <a
    href="mailto:com">com.</a>
    Thank you again for shopping with us! We appreciate your business and will
    ship your order as soon as possible.
    <br><br>
    Sincerely,
    <br>
    Company
    <br><a href="http://www.blah.com">http://www.blah.com</a>
    </td></tr>
    </table>
    </body>
    </html>
    </CFMAIL>
    <cfset Shipped = False>
    <CFQUERY name="InsertOrdConfirm" DATASOURCE="intranet">
    INSERT INTO OrdConfSent
    (Order,Custnum,FirstName,LastName,Email,Sales_ID,S hipped)

    VALUES ('[#Order#]','#Custnum#','#FirstName#','#LastName#','#Email#' ,'#Sales_I
    D#','#Shipped#')
    </cfquery>
    </cfloop>
    <cfset UpdateOrder = Order>
    </cfif>

    <cfquery name="UpdateCurrentOrder" datasource="intranet">
    UPDATE OrdConfInd
    SET Order = #UpdateOrder#
    </cfquery>

    Natesac Guest

  2. #2

    Default Re: Query of Queries problem

    If your column name is an sql keyword, such as order, give it an alias.
    <cfquery name="getCurrentOrder" datasource="intranet">
    SELECT Order as ThisOrder
    FROM OrdConfInd
    </cfquery>

    That's all the code I read. Once I started to see stuff not related to the
    error, I stopped reading.

    Originally posted by: Natesac
    I believe there to be issues with the subquery's use of the word 'Order'
    (reserved)... so I used the hard brackets like the good manual said I should.
    Here's the error message from the CFMX Scheduler:

    Error Executing Database Query.<br><b>Query Of Queries runtime error.</b><br>
    Unsupported type comparison. The specific sequence of files included or
    processed is: Z:\wwwroot\intranet\www\confirmations\orderconfirm .cfm

    I posted this last week, but proposed changes didn't yield the correct
    results. So I thought I'd throw it back out there to see if any of you can
    lend a hand. Many thanks in advance.

    Nate





    Dan Guest

  3. #3

    Default Re: Query of Queries problem

    Ok... I made a few changes... and I'm getting this error now:

    Error Executing Database Query.Data type mismatch in criteria expression. The
    specific sequence of files included or processed is:
    Z:\wwwroot\intranet\www\confirmations\orderconfirm .cfm

    Thanks again in advance.

    Nate

    <cfquery name="getCurrentOrder" datasource="intranet">
    SELECT Ordernum
    FROM OrdConfInd
    </cfquery>

    <cfquery name="getMomOrders" datasource="momlink">
    SELECT Cms.Order as ThisOrder, Cms.Sales_ID, Cust.Custnum, Cust.FirstName,
    Cust.LastName, Cust.Email
    FROM Cms, Cust
    WHERE Cms.Order > 2000000 AND Cms.Custnum = Cust.Custnum AND Cust.Email IS
    NOT NULL
    </cfquery>

    <cfquery name="getOrdersMMConfirm" dbtype="query">
    SELECT getMomOrders.ThisOrder, getMomOrders.Sales_ID, getMomOrders.Custnum,
    getMomOrders.FirstName, getMomOrders.LastName, getMomOrders.Email
    FROM getCurrentOrder, getMomOrders
    WHERE getMomOrders.ThisOrder > getCurrentOrder.Ordernum AND
    getMomOrders.Sales_ID <> 'EA'
    </cfquery>

    <cfquery name="getOrdersCSConfirm" dbtype="query">
    SELECT getMomOrders.ThisOrder, getMomOrders.Sales_ID, getMomOrders.Custnum,
    getMomOrders.FirstName, getMomOrders.LastName, getMomOrders.Email
    FROM getCurrentOrder, getMomOrders
    WHERE getMomOrders.ThisOrder > getCurrentOrder.Ordernum AND
    getMomOrders.Sales_ID = 'EA'
    </cfquery>


    <cfif getOrdersMMConfirm.RecordCount gt 0>
    <cfloop query="getOrdersMMConfirm">
    <CFMAIL to="#email#" from="com" subject="Order
    Confirmation" TYPE="HTML">
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <title>blah- Order Confirmation</title>
    <meta http-equiv="Content-Type" content="text/html; cht=iso-8859-1">
    </head>

    <body>
    <table width="564" border="0" cellspacing="0" cellpadding="0">
    <tr><td>
    Dear #FirstName# #LastName#,
    <br><br>
    This email is to confirm that your order is being processed. Your Order
    Number is #ThisOrder#. Your Customer Number is #Custnum#.
    <br><br>
    WE TRY TO SHIP EVERY ORDER WITHIN 2 DAYS FROM THE DATE WE RECEIVE IT, but the
    coin business can be unpredictable at times and in-stock merchandise may
    fluctuate, causing delays in shipping. If your order is a Special Purchase
    item, please allow up to 2-6 weeks from the date of purchase for delivery.
    If you would like to inquire about your order please send an email to <a
    href="mailto:com">com</a> and include your Order Number
    and your Customer Number. As stated above, please allow up to 14 days (and up
    to 2-6 weeks for Special Purchase items) for delivery of your order before
    sending an inquiry.
    <br><br>
    If you have any questions, please DO NOT RESPOND TO THIS EMAIL. Please
    contact our Customer Service team at: <a
    href="mailto:com">com.</a>
    Thank you again for shopping with us! We appreciate your business and will
    ship your order as soon as possible.
    <br><br>
    Sincerely,
    <br>
    blah
    <br><a href="http://www.blah.com">http://www.blah.com</a>
    </td></tr>
    </table>
    </body>
    </html>
    </CFMAIL>
    <cfset Shipped = False>
    <CFQUERY name="InsertOrdConfirm" DATASOURCE="intranet">
    INSERT INTO OrdConfSent
    (Ordernum,Custnum,FirstName,LastName,Email,Sales_I D,Shipped)

    VALUES ('#ThisOrder#','#Custnum#','#FirstName#','#LastNam e#','#Email#','#Sales
    _ID#','#Shipped#')
    </cfquery>
    </cfloop>
    <cfset UpdateOrder = ThisOrder>
    </cfif>

    <cfif getOrdersCSConfirm.RecordCount gt 0>
    <cfloop query="getOrdersCSConfirm">
    <CFMAIL to="#email#" from="com" subject="Order
    Confirmation" TYPE="HTML">

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <title>blahblah- Order Confirmation</title>
    <meta http-equiv="Content-Type" content="text/html; cht=iso-8859-1">
    </head>

    <body>
    <table width="564" border="0" cellspacing="0" cellpadding="0">
    <tr><td>
    Dear #FirstName# #LastName#,
    <br><br>
    This email is to confirm that your order is being processed. Your Order
    Number is #ThisOrder#. Your Customer Number is #Custnum#.
    <br><br>
    If you would like to inquire about your order please send an email to <a
    href="mailto:com">com</a> and include
    your Order Number and your Customer Number. As stated above, please allow up to
    14 days (5 weeks for international shipments sent by USPS standard Air Mail)
    for delivery of your order before sending an inquiry.
    <br><br>
    If you have any questions, please DO NOT RESPOND TO THIS EMAIL. Please
    contact our Customer Service team at: <a
    href="mailto:com">com.</a>
    Thank you again for shopping with us! We appreciate your business and will
    ship your order as soon as possible.
    <br><br>
    Sincerely,
    <br>
    blah
    <br><a href="http://www.blah.com">http://www.blah.com</a>
    </td></tr>
    </table>
    </body>
    </html>
    </CFMAIL>
    <cfset Shipped = False>
    <CFQUERY name="InsertOrdConfirm" DATASOURCE="intranet">
    INSERT INTO OrdConfSent
    (Ordernum,Custnum,FirstName,LastName,Email,Sales_I D,Shipped)

    VALUES ('#ThisOrder#','#Custnum#','#FirstName#','#LastNam e#','#Email#','#Sales
    _ID#','#Shipped#')
    </cfquery>
    </cfloop>
    <cfset UpdateOrder = ThisOrder>
    </cfif>

    <cfquery name="UpdateCurrentOrder" datasource="intranet">
    UPDATE OrdConfInd
    SET Ordernum = #UpdateOrder#
    WHERE ID = 1
    </cfquery>

    Natesac Guest

Similar Threads

  1. Query of Queries with constructed query using QueryNew()and QueryAddRow()
    By LeadFoot in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: August 4th, 02:00 PM
  2. Problem with Query of Queries and "In" condition
    By wein in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: July 11th, 11:05 PM
  3. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  4. Queries Of Queries Single Quote Problem
    By TimH2O in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 1st, 07:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139