Professional Web Applications Themes

Query Question Please HELP - Coldfusion Database Access

I have a Table with huge volume of records . table structuer is some thing like this country : region : destrict : destination: Activity date : Trailor : T type what i want eventaully is to create a Text file using perl automatically which will contain info based on Acticity Date : Destination: Trailor So for all the trailors which Went to Houston destination on todays date will make One file and then All the trailor which Went to Dalls Destination on lets say yesterday will make another File . Writing Data to File is next Part but what ...

  1. #1

    Default Query Question Please HELP

    I have a Table with huge volume of records . table structuer is some thing like
    this

    country : region : destrict : destination: Activity date : Trailor : T type

    what i want eventaully is to create a Text file using perl automatically which
    will contain info based on Acticity Date : Destination: Trailor
    So for all the trailors which Went to Houston destination on todays date will
    make One file and then All the trailor which Went to Dalls Destination on lets
    say yesterday will make another File .

    Writing Data to File is next Part but what will be the shape of Query ?


    flooker Guest

  2. #2

    Default Re: Query Question Please HELP

    You could use the GROUP attribute of the CFOUTPUT tag to slice and dice the
    query results.

    I assume that you need one file per destination. As a bare framework of what
    to do:

    (Order your CFQUERY by destination, then date descending if you want to show
    latest activity first)
    <cfquery name="myquery" datasource="sql">

    </cfquery>
    <CFOUTPUT query="myquery" GROUP="destination">
    <Cfset outputtext = "">
    <cfset outputtext=myquery.columnlist>
    <cfset outputtext = outputtext & chr(13)& chr(10)>
    <CFOUTPUT>
    <Cfloop index="filefield" list="#myquery.columnlist#">
    <cfset outputtext= outputtext&#evaluate(filefield)#&",">
    </cfloop>

    <Cfset outputtext=outputtext&chr(13)& chr(10)>
    </CFOUTPUT>
    <CFFILE action="write" file="\\mymachine\c$\#destination#.txt"
    output=#outputtext#>
    </CFOUTPUT>

    HTH,

    philh Guest

  3. #3

    Default Re: Query Question Please HELP

    Phil sorry for very late reply.

    Here is the Snippet of Data :

    ID--Destinatio -- Sort -- Date -- Bags/Trailor --
    smalls/trailor -- Trailor Num -- Total_bags_per_trailor -- Zip_code
    1 --Dallas -- M -- 20060202 -- 10 --
    21 -- 1111 -- 34
    -- 4023
    2--Dallas -- M -- 20060202 -- 10 --
    54 -- 1111 -- 34
    -- 4023
    3--Dallas -- M -- 20060202 -- 14 --
    21 -- AAAA -- 24
    -- 1111
    4--Dallas -- M -- 20040202 -- 22 --
    21 -- 1111 -- 34
    -- 4023
    5--Houst -- M -- 20030202 -- 9 --
    31 -- KKKK -- 11
    -- 3456
    6--Houst -- M -- 20060202 -- 10 -- 21
    -- 4567 -- 34
    -- 6755
    7--Houst -- M -- 20060202 -- 10 -- 21
    -- 4567 -- 26
    -- 1232
    8--Moon -- P -- 20040105 -- 99 -- 22
    -- 4567 -- 26
    -- 1232

    so ID 1 and 2 will create one Text File based on Same destination - Sort -
    Date - Trailor Num
    ID 3 will create another text file trailor number is different in it from ID 1
    & 2
    ID 4 will now create another file because Date is different

    So bottom line is for one records having same destination sort date trailor
    number there will be one file . and if any one of the criteria is changed there
    will be another file .

    Help will be really appreciated




    flooker Guest

  4. #4

    Default Re: Query Question Please HELP

    In that case, keep track of the values of your critical data elements, and break out of the loop/reset the values when one of them changes.
    philh Guest

  5. #5

    Default Re: Query Question Please HELP

    Besides, your reply outlines business rules which differ from your original
    request:

    "all the trailors which Went to Houston destination on todays date will make
    One file and then All the trailor which Went to Dallas Destination on lets say
    yesterday will make another File ."

    There's nothing in there about a new file for a different trailer number.

    That said, I think that unequivocal definition of business rules should
    precede any coding whatsoever. If you can't fully and cogently explain what it
    is that you need, it's impossible to make a system that fulfills the
    requirements.

    philh Guest

Similar Threads

  1. Query Question
    By Jason MacKenzie in forum Coldfusion - Advanced Techniques
    Replies: 51
    Last Post: November 18th, 04:58 AM
  2. SOS! Query Question
    By design in progress in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 11th, 05:39 AM
  3. 6.1 Query of Query Question. Ref to own Col
    By smcgovern in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: April 24th, 10:17 PM
  4. SQL query question
    By Joost Kraaijeveld in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: February 3rd, 11:48 AM
  5. ASP SQL query question
    By Brandon in forum ASP Database
    Replies: 6
    Last Post: May 3rd, 04:37 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