Sequel IF and Else statements

75 pts.
Tags:
AS/400
Crystal Reports
I’m a newbie to Sequel and I’m trying to recreate some old Crystal Reports using Sequel. My question is: how does the IF, Else statement work in Sequel? This is the statement for Crystal, for a Termination report, we have six companies and if an employee transfers to one of our other companies, we need to indicate the employee as a term/transfer.
if ({PEPTR.TRTRCD} = "TE" and {PEPTR.TRTEC} = "VTRNS") then ToText ({PEPTR.TRTRCD})+", "+ToText({PEPTR.TRTEC}) else if ({PEPTR.TRTRCD} = "TR") then ToText ({PEPTR.TRTRCD})+", "+ToText({PEPTR.TRTEC})


Software/Hardware used:
AS/400

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    To clarify, it looks as if both of those IF-conditions yield the same THEN-result: { ToText ({PEPTR.TRTRCD})+", "+ToText({PEPTR.TRTEC}) ]. That implies that they could form a single compound IF-condition that connects the two with "OR". After that, there is no indication of what any result should be emitted when neither condition is true. . Can you verify if those are correct interpretations of what you wrote? . Also, are you asking about the "Sequel" product from HelpSystems (i.e., from ASC.)? Or are you using "Sequel" as a generic term for "SQL"? . Tom
    125,585 pointsBadges:
    report
  • Gamblersgobroke
    Thanks Tom, I have it working now. We actually use the SEQUEL View Point Software 1.The first statement looks for the transaction code “TE ” and if the Termination code is “VTRNS” The above is an employee who has transferred from one property to another, so they are terminated from the first property and rehired as a transferred to the second property 2.The second statement looks for the transaction code “TR” and the Termination code can be anything which indicates the employee is no longer working anywhere within the company. So, I created a Derived field called PE_ACTION where that holds that information using the following. CASE WHEN TRTRCD.peptr='TE ' AND TRTEC.peptr='VTRNS' THEN TRIM(TRTRCD.peptr)||','||(TRTEC.peptr) WHEN TRTRCD.peptr='TR ' THEN TRTRCD.peptr||TRTEC.peptr END
    75 pointsBadges:
    report
  • TomLiotta
    A CASE structure is what I expected, though I would make a change to what you have:
    CASE
    WHEN TRTRCD.peptr=’TE ‘ AND TRTEC.peptr=’VTRNS’ THEN TRIM(TRTRCD.peptr) concat ’,' concat (TRTEC.peptr)
    WHEN TRTRCD.peptr=’TR ‘ THEN TRTRCD.peptr concat TRTEC.peptr
    ELSE
        // Some default value here...
    END
    I would replace the vertical bars with CONCAT if Sequel accepts it. The use of special characters can cause trouble. And I would add an ELSE clause to ensure that some known value is emitted from the CASE structure when neither condition is true.
    .
    BTW, your CASE statement doesn't include the embedded comma in the second WHEN clause.
    .
    Tom
    125,585 pointsBadges:
    report
  • Gamblersgobroke
    Thanks Tom, I did change the second When statement... at the time i was rushing to get to a meeting and came back to it later. But I will try the "concat". Thanks for the help. Liz
    75 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following