6 Replies Latest reply on Jun 26, 2006 8:15 AM by flooker

    Query Issue

    flooker Level 1
      I have a Table with huge volume of records . table structure 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 coldfusion 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 Dallas Destination on lets say yesterday will make another File .

      Writing Data to File is next Part but what will be the shape of Query : DB is Oracle
        • 1. Re: Query Issue
          coderWil Level 1
          Flooker,
          Please write a query that you believe should work. If it doesn't, then post it here and we can try to help you wth it.

          • 2. Re: Query Issue
            flooker Level 1
            some thing like

            SELECT A.FAC_LOC_NR , A.EFF_DT , A.ORG_OGZ_ABR_NA , A.PKG_SML_PRC_QY ,A.ULO_SML_PER_BAG_QY,A.PKG_TOT_QY,A.ULO_NR
            FROM thacDat A , ThacDat B
            WHERE B.fac_loc_nr (+) = A.FAC_LOC_NR

            Some thing like this i Guess
            • 3. Re: Query Issue
              paross1 Level 2
              Just curious, why would you do an OUTER JOIN on ThacDat when you aren't selecting anything from that table?

              Phil
              • 4. Query Issue
                flooker Level 1
                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 bcoz trailor number is different in it from ID 1 & 2
                ID 4 will now create another file because Date is different from ID 1 & 2

                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 .
                eventually what i have to do is during the creation of file i need to count the number of total bags in the trailor , # of small bags in the trailor and information regarding Zip codes and fill it with the text file .

                Help will be really appreciated
                • 5. Re: Query Issue
                  paross1 Level 2
                  You didn't answer my question. Why are you joining thacDat with itself, especially as an OUTER JOIN? Also, you probably want to put an ORDER BY clause in your query so that you can sort on the fields that you will end up grouping by n in your CFOUTPUT.

                  Phil
                  • 6. Re: Query Issue
                    flooker Level 1
                    Phil that was not a correct Query , that what i started out with it was just a thought, but i think there is no need to do the Outer Join at all .