Having text come back if condition is met...

[viking79]

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_RECORDID
FROM
     "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

 

[sefas]

Edit the expression for the field on the report so that if the value is 2, you set the value to "Approved"

[viking79]

 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

[augarte]

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.

 

[twmoore]

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'

[viking79]

 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 again

Code:

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'

[viking79]

Hello, Does this go in the query or where you edit the expression itself?

[svenn]

Decode only works with Oracle

[viking79]

 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