Solved

Query-Of-Queries question about trying to UPDATE

Posted on 2004-04-05
8
503 Views
Last Modified: 2013-12-24
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???
0
Comment
Question by:JUSTICE
8 Comments
 
LVL 5

Expert Comment

by:Seth_Bienek
ID: 10761308
What database platform are you on?  Oracle or DB2?

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
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10761447
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
0
 
LVL 9

Accepted Solution

by:
shooksm earned 500 total points
ID: 10761625
You can do a SQL update on a query object.  But you can use some of the query functions to manipulate the data inside of a query.  So you could use the QuerySetCell function:

<cfloop query="GetData">
     <cfif GetData.RatedRev NEQ 0 AND GetData.Estimated NEQ 0>
          <cfset QuerySetCell(GetData, "RateStatus", "RATED", GetData.CurrentRow)>
     </cfif>
</cfloop>

My other suggestion would be to see if AS400 SQL supports the ANSI SQL CASE statement.  Then you could do something like this with your rated field:

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,
     CASE WHEN ORDBILJ.ORBAMT <> 0 AND ORDBILJ.ORESTR <> 0 THEN 'RATED' ELSE 'NOT RATED' END 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%'

CASE statements allow for multiple WHEN statements so you can create several different outcomes for your RateStatus column.

Hope this helps.
0
 
LVL 9

Expert Comment

by:shooksm
ID: 10761631
Oops.  First sentence should read "You can NOT do and update on a query object."
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 35

Expert Comment

by:mrichmon
ID: 10761656
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.

0
 
LVL 9

Expert Comment

by:shooksm
ID: 10762179
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.
0
 
LVL 10

Expert Comment

by:Mause
ID: 10764626
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.CurrentRow] = 'RATED'>

hope this helps
Mause
0
 
LVL 1

Author Comment

by:JUSTICE
ID: 10764971
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!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
tomcat7  URL keeps resolving to https instead of HTTP 4 54
cloud web Service looking for a home... 3 100
Configure IIS to process JSON 10 74
Domino Website - Redirection 12 50
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now