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
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
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 .
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.