JUSTICE
asked on
Query-Of-Queries question about trying to UPDATE
OK, I have a query that gathers data from my AS400, as follows:
**************************
SELECT
ORDBILJ.ORODR## as OrderNumber,
ORDBILJ.ORCONS as CustCode,
CUSTMAST.CUNAME as CustName,
LOAD.DIUNIT as Unitnum,
ORDBILJ.ORSTAT as Status,
(#TODAY# - ORADDT) as DaysOutstanding,
ORDBILJ.ORBAMT as RatedRev,
ORDBILJ.ORESTR as Estimated,
ORDBILJ.ORMILE as TarrifMiles,
(ORDBILJ.ORESTR - ORDBILJ.ORESTR) as RateStatus
FROM
ORDBILJ INNER JOIN CUSTMAST ON ORDBILJ.ORCUST = CUSTMAST.CUCODE
INNER JOIN LOAD ON ORDBILJ.ORODR## = LOAD.DIODR##
WHERE
ORSTAT IN ('E', 'D')
AND ORINV = ' '
AND LOAD.DIDISP = '01'
AND ORDBILJ.ORSEQ = ' '
AND ORDBILJ.ORODR## NOT LIKE 'D%'
AND ORDBILJ.ORODR## NOT LIKE 'R%'
**************************
Now here is what is tricky. You see my column name I am creating called 'RatedStatus'? I need to set this (inside the query results) to be either Default, Rated, or Estimated, based on the valued in the RatedRev and Estimated values. Here is what I am trying to do:
**************************
<cfquery name="UpdateRecords" dbtype="query">
UPDATE
GetData
SET
RateStatus = 'RATED'
WHERE
RatedRev <> 0
AND Estimared <> 0
</cfquery>
**************************
Of course, ColdFusion does not like doing an UPDATE statement inside a Query-of-queries. I need to know a way I can update the value inside the result set, without having to write to a temp table, to a file, or anything like that. Does anyone have any idea's???
**************************
SELECT
ORDBILJ.ORODR## as OrderNumber,
ORDBILJ.ORCONS as CustCode,
CUSTMAST.CUNAME as CustName,
LOAD.DIUNIT as Unitnum,
ORDBILJ.ORSTAT as Status,
(#TODAY# - ORADDT) as DaysOutstanding,
ORDBILJ.ORBAMT as RatedRev,
ORDBILJ.ORESTR as Estimated,
ORDBILJ.ORMILE as TarrifMiles,
(ORDBILJ.ORESTR - ORDBILJ.ORESTR) as RateStatus
FROM
ORDBILJ INNER JOIN CUSTMAST ON ORDBILJ.ORCUST = CUSTMAST.CUCODE
INNER JOIN LOAD ON ORDBILJ.ORODR## = LOAD.DIODR##
WHERE
ORSTAT IN ('E', 'D')
AND ORINV = ' '
AND LOAD.DIDISP = '01'
AND ORDBILJ.ORSEQ = ' '
AND ORDBILJ.ORODR## NOT LIKE 'D%'
AND ORDBILJ.ORODR## NOT LIKE 'R%'
**************************
Now here is what is tricky. You see my column name I am creating called 'RatedStatus'? I need to set this (inside the query results) to be either Default, Rated, or Estimated, based on the valued in the RatedRev and Estimated values. Here is what I am trying to do:
**************************
<cfquery name="UpdateRecords" dbtype="query">
UPDATE
GetData
SET
RateStatus = 'RATED'
WHERE
RatedRev <> 0
AND Estimared <> 0
</cfquery>
**************************
Of course, ColdFusion does not like doing an UPDATE statement inside a Query-of-queries. I need to know a way I can update the value inside the result set, without having to write to a temp table, to a file, or anything like that. Does anyone have any idea's???
I see that you are setting RateStatus initially to ORDBILJ.ORESTR - ORDBILJ.ORESTR
Won't this just give you 0 for all results?
Also what is the logic behind doing an update query as a query of queries?
Cold Fusion does not support INSERT DELETE or UPDATE on query of queries.
If you explain the logic perhaps we can come up with an alternative for you
Won't this just give you 0 for all results?
Also what is the logic behind doing an update query as a query of queries?
Cold Fusion does not support INSERT DELETE or UPDATE on query of queries.
If you explain the logic perhaps we can come up with an alternative for you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oops. First sentence should read "You can NOT do and update on a query object."
shooksm showed one example of an alternative method like I was talking about.
Or there are ways to set the query cells at a later point if you don't know at the DB query time what the value should be.
As I mentioned if you explain your logic we can come up with a solution that will suit your needs since the UPDATE QofQ's is not an option.
Or there are ways to set the query cells at a later point if you don't know at the DB query time what the value should be.
As I mentioned if you explain your logic we can come up with a solution that will suit your needs since the UPDATE QofQ's is not an option.
Actually, my first example is of how to use the QuerySetCell function to change the value of a cell after the initial query. The second example is how to do it using strictly SQL.
a cfquery returns an array so you can set the value as shooksm said width QuerySetCell or directly in the array width
<cfset GetData.RateStatus[GetData
hope this helps
Mause
<cfset GetData.RateStatus[GetData
hope this helps
Mause
ASKER
Shooksm,
Both your examples will do what I needed them too!
Thank you very much for the help everyone!!
The reason I was trying to get the data correct inside the query was that I need to further perform query-of-queries after the fact, and I dont want to have to go to my DB again and again. I have to determine a total dollar value based on daysoutstanding, as well as by RATED, Default ,etc. status totals.
Again, thanks everyone for your help!
Both your examples will do what I needed them too!
Thank you very much for the help everyone!!
The reason I was trying to get the data correct inside the query was that I need to further perform query-of-queries after the fact, and I dont want to have to go to my DB again and again. I have to determine a total dollar value based on daysoutstanding, as well as by RATED, Default ,etc. status totals.
Again, thanks everyone for your help!
The most efficient approach will likely be to include this logic in your original query using the appropriate database-specific conditional code.
Take Care,
Seth