Professional Web Applications Themes

case statement in QoQ - Coldfusion - Advanced Techniques

here is my select statement: <cfquery name="finalQuery" dbtype="query"> select o.partNumber, o.partNumber as assembly, o.orderDate, o.woNumber, o.onHand, 'qty' = CASE WHEN upper(left(o.poNumber,2)) like 'PO' THEN o.qty ELSE -1 * o.qty END, o.net, o.poNumber, o.poLine, o.clearDate, o.poDate, o.supplierID, o.partXreference, o.boyerCode, o.vmiMrpProgram, w.productClass, w.released, p.isc, p.userDefined2, p.desctext from orderQuery o, getWoInfo w, getPartMaster p where o.woNumber = w.woNumber and o.partNumber = p.partNumber </cfquery> and here's the error: Query Of Queries syntax error. Encountered "=" at line 0, column 0. Incorrect Select Statement, Expecting a 'FROM', but encountered '=' instead, A select statement should have a 'FROM' construct. Thanks in advance....

  1. #1

    Default case statement in QoQ

    here is my select statement:

    <cfquery name="finalQuery" dbtype="query">
    select o.partNumber, o.partNumber as assembly, o.orderDate, o.woNumber,
    o.onHand,
    'qty' = CASE
    WHEN upper(left(o.poNumber,2)) like 'PO'
    THEN o.qty
    ELSE
    -1 * o.qty
    END,
    o.net, o.poNumber, o.poLine, o.clearDate, o.poDate, o.supplierID,
    o.partXreference, o.boyerCode, o.vmiMrpProgram,
    w.productClass, w.released,
    p.isc, p.userDefined2, p.desctext
    from orderQuery o, getWoInfo w, getPartMaster p
    where o.woNumber = w.woNumber
    and o.partNumber = p.partNumber
    </cfquery>

    and here's the error:

    Query Of Queries syntax error. Encountered "=" at line 0, column 0. Incorrect
    Select Statement, Expecting a 'FROM', but encountered '=' instead, A select
    statement should have a 'FROM' construct.

    Thanks in advance.

    ego-adam Guest

  2. #2

    Default Re: case statement in QoQ

    http://livedocs.macromedia.com/coldfusion/7/htmldocs/00001266.htm

    CASE ain't supported, I'm afraid.

    The SQL implementation QoQ uses is... "sp"... to say the least.
    --
    Adam
    Adam Guest

  3. #3

    Default Re: case statement in QoQ

    You can use 2 seperate qoq, one to pull positive values and one to pull
    negative values. More work, but QoQ is not a 'true' SQL platform since it
    doesn't connect to the dB itself, only the query results.

    coderWil Guest

Similar Threads

  1. Case Statement
    By tsetliff webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 9
    Last Post: February 5th, 11:26 PM
  2. How can I use case statement in my case, if I can??
    By Ricky in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 14th, 05:44 PM
  3. CASE statement and CONTAINS
    By steve in forum Macromedia Director Lingo
    Replies: 2
    Last Post: July 31st, 12:12 AM
  4. Case statement help
    By Meenal in forum Microsoft SQL / MS SQL Server
    Replies: 8
    Last Post: July 24th, 12:48 AM

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