EasyCFM.COM ColdFusion Forums / Coding Help! / About array / structure, how to use, which to use?

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: About array / structure, how to use, which to use?

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

Justice
10-23-2002 @ 7:19 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 36
Joined: Oct 2002

First I want to say hello to everyone on this board, and I love the layout and the tutorials!  Smile

OK, here is my dilema.
I have, I would say, a medium amount of coldfusion knowledge, and am running coldFusion MX connected through ODBC to my iSeries AS400 database.
I am trying to generate several differnt report view's with the minimum amount of querying that I can.

Here is my query currently:

<CODE>
<CFQUERY datasource="ITSR5FILE" name="LOADDATE">
SELECT
     *
     FROM LOADDATE, ORDER
     WHERE DIDATE = 2002291
     AND DIDISP = '01'
     AND LOADDATE.DIODR## = ORDER.ORODR##

</CFQUERY>
</CODE>

As you can see, I am pulling information in from 2 files based on order number.  I will need to do a lot with this data, such as providing average miles for the load per customer and total, average revenue by customer and total, revenue by the week, etc.  (I will replace my date selection with a range later, right now I'm just using one date for testing and development)

Should I try and store this data in a structure, or an array??  Or can I do the calculations right from the query, and how should I go about trying to set it up?  i know I can do a SELECT SUM(DIMILES) in my SQL, but I really dont want to have to do 4 different queries on the same date to get my different results I'm looking for.  

Thanks in advance, I hope I wasnt too rambeling!!   Smile  

Some mistakes are just too much fun to make only once...

Justice
10-28-2002 @ 5:42 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 36
Joined: Oct 2002

Am I un-popular or did I not phrase my question well??  Frown  

Some mistakes are just too much fun to make only once...

Webmaster
10-28-2002 @ 9:25 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

Sorry, didn't see this post before Frown


ok, the first thing I noticed is that you are not joining your "tables" anywhere in the query...
WHERE LOADDATE.ID, ORDER.ID (Or something similiar where the references are items that are the same [i.e. CustomerID]).

Let me know if you need further help with this....


I would suggest you go with Array, but not sure why you would need to. You can perform calculations as follows:
<cfset avalue = #Evaluate("#LOADDATE.VALUE + 50 * 255")#>

<cfoutput>#avalue#</cfoutput>

to perform your calculations....


Thanks,
Pablo Varando
EasyCFM.COM, LLC.

Justice
10-28-2002 @ 9:34 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 36
Joined: Oct 2002

Hehe, Thanks for the reply!!   Smile

Well, I have tried some simple table joins in the past, but every reference web page I work from is almost totally dedicated to SQL in some form, and this database is DB2.

Maybe you can help me get the query right, hehe.  

I have a single record in LOADDATE that is indexed by a date field, and I pull several fields as well as an order nummber out of this table.  I also need information based on the order number from the ORDER file, which is also only 1 record worth.  

My problem comes in because in in the BILLING file, I have several records that are associated to a single order nummber, and I need to have each one pulled.
So, I am getting loaddate, order nummber, and customer information from the LOADDATE file, origin, destination, customer code, etc. from the ORDER file, and then I need to get each seperate charge associated with that order from the BILLING file.

I will need to be able to gather statistics from this data, such as total invoiced dollar amount for the week, or month, depending on a date parameter, or be able to pull out a single customer's data based on customer ccode, and present totals from that.  

Am I making sense??? hehehe, and thank you so much for your help!!!!  

Added later this day:

Also, if you do as you suggest last post, will that return to me the total value for that field, or only the value at the time??  I will have probably 1500 or so orders in each day.  Thanks again!

Some mistakes are just too much fun to make only once...

This message was edited by Justice on 10-28-02 @ 12:11 PM


Website Designed and Developed by Pablo Varando.