viking79 Posted January 5, 2012 Share Posted January 5, 2012 Hello,I am writing a report that brings back approved Purchase orders. When someone writes a PO, we have a checkbox they select to indicate that it has been approved. I found the column where this information is and can sort PO's that have a 2 in that column. What I would like to do is if a 2 is in that column, I want it to come back as Approved when the report is ran.Here is the query as it is now:SELECT PO."NUM", CUSTOMFIELDVIEW."INFOFORMATTED", CUSTOMFIELDVIEW."CFTABLEID", CUSTOMFIELDVIEW."CFNAME", PO."ID" AS PO_ID, PO."VENDORCONTACT" AS PO_VENDORCONTACT, CUSTOMFIELDVIEW."CFSORTORDER" AS CUSTOMFIELDVIEW_CFSORTORDER, CUSTOMFIELDVIEW."CFID" AS CUSTOMFIELDVIEW_CFID, CUSTOMFIELDVIEW."CFDESCRIPTION" AS CUSTOMFIELDVIEW_CFDESCRIPTION, CUSTOMFIELDVIEW."CFTYPEID" AS CUSTOMFIELDVIEW_CFTYPEID, CUSTOMFIELDVIEW."CFREQUIRED" AS CUSTOMFIELDVIEW_CFREQUIRED, CUSTOMFIELDVIEW."INFO" AS CUSTOMFIELDVIEW_INFO, CUSTOMFIELDVIEW."RECORDID" AS CUSTOMFIELDVIEW_RECORDIDFROM "CUSTOMFIELDVIEW" CUSTOMFIELDVIEW INNER JOIN "PO" PO ON CUSTOMFIELDVIEW."RECORDID" = PO."ID"WHERE (((CUSTOMFIELDVIEW.CFTABLEID) = 397076832)) and Customfieldview.cfsortorder = 2 and po.num = '10010'Any help would be greatly appreciated, thank you Link to comment Share on other sites More sharing options...
sefas Posted January 9, 2012 Share Posted January 9, 2012 Edit the expression for the field on the report so that if the value is 2, you set the value to "Approved" Link to comment Share on other sites More sharing options...
viking79 Posted January 9, 2012 Author Share Posted January 9, 2012 Hi, thanks for the response. I'm not exactly sure how do edit the expression. Is that a parameter I need to create? Thank you Link to comment Share on other sites More sharing options...
augarte Posted January 10, 2012 Share Posted January 10, 2012 Hi, Not sure if you need to create a variable. I think it depends on the data type of your PO field. If it is a string you just can edit the textfield expression ans put it at follows: ((!F{PO}.equals("2")) ? "Approved" : !F{PO})If PO field is an integer, you should create a string type variable and put the expression as follows:((!F{PO} == 2) ? "Approved" : ""+!F{PO})Hope this helps.Regards. Link to comment Share on other sites More sharing options...
twmoore Posted January 10, 2012 Share Posted January 10, 2012 You could also do a DECODE in the SQL Statement itself. Something like:SELECT PO."NUM", CUSTOMFIELDVIEW."INFOFORMATTED", CUSTOMFIELDVIEW."CFTABLEID", CUSTOMFIELDVIEW."CFNAME", PO."ID" AS PO_ID, PO."VENDORCONTACT" AS PO_VENDORCONTACT, CUSTOMFIELDVIEW."CFSORTORDER" AS CUSTOMFIELDVIEW_CFSORTORDER, CUSTOMFIELDVIEW."CFID" AS CUSTOMFIELDVIEW_CFID, CUSTOMFIELDVIEW."CFDESCRIPTION" AS CUSTOMFIELDVIEW_CFDESCRIPTION, CUSTOMFIELDVIEW."CFTYPEID" AS CUSTOMFIELDVIEW_CFTYPEID, CUSTOMFIELDVIEW."CFREQUIRED" AS CUSTOMFIELDVIEW_CFREQUIRED, CUSTOMFIELDVIEW."INFO" AS CUSTOMFIELDVIEW_INFO, CUSTOMFIELDVIEW."RECORDID" AS CUSTOMFIELDVIEW_RECORDID DECODE(Customfieldview.cfsortorder,'2','APPROVED','')FROM "CUSTOMFIELDVIEW" CUSTOMFIELDVIEW INNER JOIN "PO" PO ON CUSTOMFIELDVIEW."RECORDID" = PO."ID"WHERE (((CUSTOMFIELDVIEW.CFTABLEID) = 397076832)) and Customfieldview.cfsortorder = 2 and po.num = '10010' Link to comment Share on other sites More sharing options...
viking79 Posted January 10, 2012 Author Share Posted January 10, 2012 Hello, thanks for the responses. I tried the DECODE, but it gave me an error. I have included my code with the DECODE line in it, maybe I did it wrong. Any suggestions?Thanks againCode:SELECT PO."NUM", CUSTOMFIELDVIEW."INFOFORMATTED", CUSTOMFIELDVIEW."CFTABLEID", CUSTOMFIELDVIEW."CFNAME", PO."ID" AS PO_ID, PO."VENDORCONTACT" AS PO_VENDORCONTACT, CUSTOMFIELDVIEW."CFSORTORDER" AS CUSTOMFIELDVIEW_CFSORTORDER, CUSTOMFIELDVIEW."CFID" AS CUSTOMFIELDVIEW_CFID, CUSTOMFIELDVIEW."CFDESCRIPTION" AS CUSTOMFIELDVIEW_CFDESCRIPTION, CUSTOMFIELDVIEW."CFTYPEID" AS CUSTOMFIELDVIEW_CFTYPEID, CUSTOMFIELDVIEW."CFREQUIRED" AS CUSTOMFIELDVIEW_CFREQUIRED, CUSTOMFIELDVIEW."INFO" AS CUSTOMFIELDVIEW_INFO, CUSTOMFIELDVIEW."RECORDID" AS CUSTOMFIELDVIEW_RECORDID DECODE(Customfieldview.cfsortorder,'2','APPROVED',")FROM "CUSTOMFIELDVIEW" CUSTOMFIELDVIEW INNER JOIN "PO" PO ON CUSTOMFIELDVIEW."RECORDID" = PO."ID"WHERE (((CUSTOMFIELDVIEW.CFTABLEID) = 397076832)) and Customfieldview.cfsortorder = 2 and po.num = '10010' Link to comment Share on other sites More sharing options...
viking79 Posted January 10, 2012 Author Share Posted January 10, 2012 Hello, Does this go in the query or where you edit the expression itself? Link to comment Share on other sites More sharing options...
svenn Posted January 10, 2012 Share Posted January 10, 2012 Decode only works with Oracle Link to comment Share on other sites More sharing options...
viking79 Posted January 10, 2012 Author Share Posted January 10, 2012 Thanks for everyone's help, I got it using the Augarte's answer of making a variable in the textfield expression.Still I appreciate everyone's response in trying to help me out/tools/fckeditor/editor/images/smiley/msn/regular_smile.gif Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now