• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1256
  • Last Modified:

How to write a conditional statement

I am working in MVS 2005. I am writing a report and I am unsure how to create a conditional statement within this module.

What I want to do is to vary what table I populate a field with dependant on specific criteria.

I have a gl transaction table that holds all of the transactions that hit our gl. When the posting is an AP transaction I want to populate the description field with the vendors name from the vendors table. When the transaction is from the AR module I want to populate the description field with the customers name from the customers table.

Example:
If gl_transactions.source_module= AP 
 gl_ gl_transactions.source_reference =venders.vendor _number

If gl_transactions.source_module= AR 
gl_ gl_transactions.source_reference =customers. customers_number

Thanks in advance!
0
GregJaroch
Asked:
GregJaroch
  • 2
1 Solution
 
derekkrommCommented:
select case gl_transactions.source_module
when 'AP' then vendors.vendor_number
when 'AR' then customers.customers_number
end as description
from gl_gl_transactions
0
 
GregJarochAuthor Commented:
derekkromm, this is my current sql statement, how do  I incorperate a case statement into this

SELECT    
 GL_TRAN_LINES.ACCOUNT_NUMBER, GL_TRAN_LINES.GL_ELEMENT_04, GL_TRAN_LINES.CURRENT_YEAR,   GL_TRAN_LINES.JOURNAL_PERIOD,  SUM(GL_TRAN_LINES.GL_JOURNAL_VALUE_DR - GL_TRAN_LINES.GL_JOURNAL_VALUE_CR) AS Value,
GL_ACCOUNTS.ACCOUNT_DESCRIPTION, GL_TRAN_LINES.JOURNAL_NUMBER, GL_TRAN_LINES.JOURNAL_LINE,  GL_TRAN_LINES.GL_ELEMENT_03,  GL_TRANSACTIONS.SOURCE_MODULE, GL_TRANSACTIONS.SOURCE_REFERENCE,
 GL_TRANSACTIONS.JOURNAL_DATE, VENDORS.VENDOR_NAME
FROM      
FULL OUTER JOIN
GL_TRAN_LINES ON GL_TRANSACTIONS.JOURNAL_PERIOD = GL_TRAN_LINES.JOURNAL_PERIOD AND  GL_TRANSACTIONS.CURRENT_YEAR = GL_TRAN_LINES.CURRENT_YEAR AND
 GL_TRANSACTIONS.COMPANY_CODE = GL_TRAN_LINES.COMPANY_CODE AND
GL_TRANSACTIONS.JOURNAL_NUMBER = GL_TRAN_LINES.JOURNAL_NUMBER
 FULL OUTER JOIN
 GL_ACCOUNTS ON GL_TRAN_LINES.GL_ELEMENT_04 = GL_ACCOUNTS.GL_ELEMENT_04 AND
GL_TRAN_LINES.GL_ELEMENT_03 = GL_ACCOUNTS.GL_ELEMENT_03 AND
GL_TRAN_LINES.GL_ELEMENT_02 = GL_ACCOUNTS.GL_ELEMENT_02 AND
 GL_TRAN_LINES.GL_ELEMENT_01 = GL_ACCOUNTS.GL_ELEMENT_01 AND
GL_TRAN_LINES.ACCOUNT_NUMBER = GL_ACCOUNTS.ACCOUNT_NUMBER

GROUP BY
GL_TRAN_LINES.ACCOUNT_NUMBER, GL_TRAN_LINES.GL_ELEMENT_04, GL_TRAN_LINES.CURRENT_YEAR, GL_TRAN_LINES.JOURNAL_PERIOD,
GL_ACCOUNTS.ACCOUNT_DESCRIPTION, GL_TRAN_LINES.JOURNAL_NUMBER, GL_TRAN_LINES.JOURNAL_LINE,
 GL_TRAN_LINES.GL_ELEMENT_03, GL_TRANSACTIONS.SOURCE_MODULE, GL_TRANSACTIONS.SOURCE_REFERENCE,
GL_TRANSACTIONS.JOURNAL_DATE, VENDORS.VENDOR_NAME

HAVING    
 (GL_TRAN_LINES.GL_ELEMENT_03 = '300') AND (GL_TRAN_LINES.CURRENT_YEAR = 2007) AND (GL_TRAN_LINES.GL_ELEMENT_04 <> '1043') AND
(GL_TRAN_LINES.GL_ELEMENT_04 <> '2220') AND (GL_TRAN_LINES.JOURNAL_PERIOD > 5)
0
 
derekkrommCommented:
just put this at the end of the select, and it will be treated as a column w/ the name of Description:

case gl_transactions.source_module
when 'AP' then vendors.vendor_number
when 'AR' then customers.customers_number
end as Description
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now