Skip to main content
ericl66518797
ericl66518797
Participant
November 27, 2018
Answered

Problem Query aliased name for calculated field

  • November 27, 2018
  • 3 replies
  • 355 views

Hello,

I am hoping that someone else has done this.

I am working on a modification to a ColdFusion site I have inherited and I have to admit that I am more or less a newbie to ColdFusion, although I have worked web applications and Databases for many years.

I am working on a reporting application that output information on requests. The requests can either be completed or delegated. If they are completed the database column comp_time will be populated. If the request is delegated the comp_time column will be null and a column named delegate_date will be populated.

I have come up with the query that will look at both of these fields and and make them into one field. (That part was easy and is highlighted in red)

select sr.request_id,sr.supp_id,sr.entry_date,sr.po_num,(NVL(sr.comp_time, sr.delegate_date)) comp_time,sr.first_article,rd.detail_id,rd.po_line,rd.qty_ready,st.surv_name,rd.qty_expected,rd.qty_accepted,s.supp_name,s.city,sr.delegate_date from source_request sr,request_detail rd,surv_type st,supplier s where 1=1  and rd.surv_type = st.surv_id  and sr.request_id = rd.request_id  and sr.supp_id = s.supp_id  and s.supp_id = 'ZUZ13070'  and sr.entry_date >= to_date('10/01/2017', 'MM/DD/YYYY')  and sr.entry_date <= to_date('01/31/2018 23:59', 'MM/DD/YYYY HH24:MI')  and sr.delegate_date is not null  and sr.cancel_date is null

The problem is when I get to the point where I am outputting this to Excel from within the ColdFusion application. The code that does this is:

When I run this code 2 things happen that are not what I am looking for.  First the column name is not what the alias should be:

Second I am getting this error when I am processing the results of the query:

Until I added the modified column into the query this worked correctly.

I suspect it is some simple syntax issue but I am not sure how to look for it.

    This topic has been closed for replies.
    Correct answer ericl66518797

    With a new day and fresh Eyes I was able to come up with a way to make it do what I want it to do. Since when I am building query string I am using a <cfif> tag to change how the query is built I used the same if  logic to save what the fields should be later when I am outputting the the data I am using the correct data to output the results.  I am now using this code and it is working.  I have solved my own problem.  It may not be the most elegant way of ding it but it does what I need it to do.

      

    3 replies

    ericl66518797
    ericl66518797AuthorCorrect answer
    Participant
    November 28, 2018

    With a new day and fresh Eyes I was able to come up with a way to make it do what I want it to do. Since when I am building query string I am using a <cfif> tag to change how the query is built I used the same if  logic to save what the fields should be later when I am outputting the the data I am using the correct data to output the results.  I am now using this code and it is working.  I have solved my own problem.  It may not be the most elegant way of ding it but it does what I need it to do.

      

    WolfShade
    WolfShade
    Legend
    November 28, 2018

    IGNORE my reply.  Jive (stupidest platform, ever) isn't allowing me to edit.  I now see the findnocase().  You have the correct number of parenthesis.  I'm looking at the code to see what I can find.

    V/r,

    ^ _ ^

    WolfShade
    WolfShade
    Legend
    November 28, 2018

    First, I would never use evaluate for anything.  Ever.

    Second, you have an extra ) at the end of the evaluate; you open with two but close with three.  That's throwing CF off track.

    HTH,

    ^ _ ^

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices