Query Issue

[Guest]

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

[coderWil]

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.

[Guest]

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

[paross1]

Just curious, why would you do an OUTER JOIN on ThacDat when you aren't selecting anything from that table?

Phil

[Guest]

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

[paross1]

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

[Guest]

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 .