Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

CASE Expression with Multiple Entries

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Thu Jun 30, 2011 1:00 pm    Post subject: CASE Expression with Multiple Entries
Reply with quote

Hi All,

Could you please give me a query for following scenario.

I have a table with Plan information with multiple coverage

Ex;

PLAN1 PAIDAMOUNT1 COVERAGE1
PLAN1 PAIDAMOUNT2 COVERAGE2

I have to derive Coverage Code based on input variable like

Code:
  For  DENEXP - DEN
         VISEXP - VIS
         DENVISEXP - 'DEN','VIS'  etc

I am getting other coverages also..

So I need a which has to sum up piad amount for Plan for a set of coverages or a coverage

So the query looks like

Code:
EXEC SQL
         SELECT SUM(PAIDAMOUNT) FROM
                     WHERE PLAN_NR = :RQ-PLN-NR
                     AND COV_CD IN
                     (CASE
                              WHEN :RQ-COV-CD = 'DENEXP'
                               THEN 'DEN'
                              WHEN :RQ-COV-CD = 'VISEXP'
                               THEN 'VIS'
                              WHEN :RQ--COV-CD = 'DENVISEXP'
                              THEN 'DEN','VIS'   -----------------------------  Can i do this it is not working
                       END)
END-EXEC 


Qtn is

1) Can we have Multiple Constant entry within CASE Statement ?
2) Can we use some thing like below
||'DEN'||','||'VIS'

Best Regard's
Raghu
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Jun 30, 2011 1:31 pm    Post subject:
Reply with quote

a case is supposed to give a scalar result.
The normal way to solve this, is to create two hostvariables or a hostvariable array.
Code:
EVALUATE RQ_COV_CD
WHEN 'DENEXP' then
   move 'DEN' to RQ-COV-CD1
   move 'DEN' to RQ-COV-CD2
WHEN 'VISEXP' then
   move 'VIS' to  RQ-COV-CD1
   move 'VIS' to  RQ-COV-CD2
WHEN 'DENVISEXP' then
   move 'DEN' to  RQ-COV-CD1
   move 'VIS' to  RQ-COV-CD2
WHEN other ?????
END-EVALUATE

EXEC SQL
         SELECT SUM(PAIDAMOUNT) FROM
                     WHERE PLAN_NR = :RQ-PLN-NR
                     AND COV_CD IN (:RQ-COV-CD1,:RQ-COV-CD2)
END-EXEC
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Thu Jun 30, 2011 2:12 pm    Post subject:
Reply with quote

Hi,

I want to derive within CASE Expression becuase i need to derive Coverage code based on input Cobol Variable for around 30 coverages.

Just for augment sake i gave u an example.

Let me know, if you have any alternatives.

Regard's
Raghu
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Jun 30, 2011 2:35 pm    Post subject:
Reply with quote

it is irrelevant if it is for 1 or 30
Or you have a big case in DB2 or a big evaluate in COBOL

unless there is programmable logic in the derivation process.
something like where posstr(:RQ-COV-CD,COV_CD ) > 0
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Thu Jun 30, 2011 2:53 pm    Post subject:
Reply with quote

Hello,

This is how we have designed for our Project. If you know answer

Please Suggest, more over it is SP, i didn't see any fault design in this.

Quote:
it is irrelevant if it is for 1 or 30


Who said there will no coverages.. if you consider Coverages in Critical illness, you will be having coverages for Member, Spouse and again for Rider member & Rider Spouse and think about DEN, VIS...and other..

Let me know if you about CASE Expression else point me some link where i can get some input.

Thanks in advance
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Jun 30, 2011 3:31 pm    Post subject:
Reply with quote

SP is meaningless , SP can be (almost any language)
You seem certain the design is correct, yet you don't find an answer to your requirement but you just know using a CASE instruction will solve it.

somehow I don't think I can help you.
Maybe someone else can.
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Thu Jun 30, 2011 3:43 pm    Post subject:
Reply with quote

Hello...

This is not some kind of Adhoc/small changes Request. We are integrating Front end with Mainframe backend.

We r deing hard to know answere for this..

I appreciate for your suggestion using cobol evaluate but that was my ultimate solution..but i am looking forward to have within CASE expression as Query is running remotly

Thanks your suggestion. let others can help me out

Thank you
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Thu Jun 30, 2011 6:53 pm    Post subject: Reply to: CASE Expression with Multiple Entries
Reply with quote

You're looking for help. Someone of great skill and experience replies to your request.

Instead of taking any notice of the advice, which is your call, you felt the need to stick with your design, and subsequently to justify that by aggrandising your system. It doesn't matter if it is a one-off for a one-record dataset or a system to save the planet - if it doesn't work, it doesn't work.

Maybe get hold of a copy of the manual and explain to that how important your system is. Or call IBM, I'm sure they'll have it fixed in a jiffy, once they know how important it is.

My advice for your system is to use code that works. If you need help to do that, then you'll need to know how to ask for help, and how to respond if you can't understand it, or how it applies.

Just sitting there and letting the world know how important you and your system are will lead nowhere. You choice.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Jun 30, 2011 8:56 pm    Post subject:
Reply with quote

you simply cannot generate an IN List with literals greater than 1 item.

now, you can generate an IN List with a SELECT.
maybe your cobol program
(if you SP is an only sql sp and not a cobol program)
can load the potential IN List items into a table (GTT or regular table) prior to invoking the SP.

to be nice, I will say that part of your design failed to take into consideration technical aspects.

or

i could maintain a tone that you deserve and say that your mouth overloaded your ass.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Changeman get multiple "Browse C... elixir1986 Compuware & Other Tools 4 Fri Jul 14, 2017 1:48 am
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts RFE: DB2 support for mixed case names. Pedro DB2 0 Tue Jul 04, 2017 1:32 am
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. SDSF multiple spool datasets extracte... PJAlarcon CLIST & REXX 1 Fri Apr 21, 2017 10:50 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us