Select case



IBM's flagship relational database management system

Re: Select case

Postby itamar rocha » Tue Mar 11, 2014 11:07 pm

Someone can explain this command for me:

ELECT CASE
WHEN COUNT(SX008.NUM_CPF_CNPJ) = 0
THEN 1
ELSE 4
END AS :WS-IND-TP-PREMIO
FROM SX_EXC_COBER SX112
WHERE SX112.COD_PARAM_EXC_COBER = :SX112-COD-PARAM-EXC-COBER
AND SX112.NUM_COBERTURA = :SX112.NUM-COBERTURA
END-EXEC
itamar rocha
 
Posts: 41
Joined: Mon Nov 11, 2013 8:46 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Re: Select case

 

Re: Select case

Postby NicC » Wed Mar 12, 2014 12:01 am

Why not read up in the manual and come back with questions about those parts that you do not understand? And DO NOT EVER make another post like the last one. People here are volunteers - they do not get paid and they do not have to answer anything. And you started 'begging' less than an hour after your previous post.

And you were requested to post using code tags. If you do not know how to use them then make yourself famliar with the forum and the search facility.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Select case

Postby dick scherrer » Wed Mar 12, 2014 2:15 am

Sorry NicC . . . I deleted the repeat post you mentioned . . .

@itamar rocha

What you have is a simple SELECT, that reads a selected column based ion a CASE statement and some selection criteria.

EXEC SQL

SELECT CASE
       WHEN COUNT(SX008.NUM_CPF_CNPJ) = 0
         THEN 1
         ELSE 4
       END AS :WS-IND-TP-PREMIO
  FROM SX_EXC_COBER SX112
            WHERE SX112.COD_PARAM_EXC_COBER = :SX112-COD-PARAM-EXC-COBER
                AND SX112.NUM_COBERTURA = :SX112.NUM-COBERTURA
END-EXEC

When posting Code, JCL, data, etc, you need to use the Code tag to improve readability and retain alignment.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Select case

Postby itamar rocha » Wed Mar 12, 2014 9:29 pm

Hello,
I am relly sorry about my last post.
I know people here are volunteers, and I am very grateful for the answer.
About the tag code I do not know how to use them, Is there a place where I can find about?
Thanks again
itamar rocha
 
Posts: 41
Joined: Mon Nov 11, 2013 8:46 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Select case

Postby itamar rocha » Wed Mar 12, 2014 9:33 pm

Hello,
I know how to use code tags
Thank you
itamar rocha
 
Posts: 41
Joined: Mon Nov 11, 2013 8:46 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Select case

Postby Quasar » Thu Mar 13, 2014 12:17 am

Itmar Rocha,

Assuming its a singleton SELECT, you assign the resultant value in each column to its own host variable using SELECT a,b,c INTO :HV-A,:HV-B,:HV-C. I would have run the SQL after making that change. See if the below code snippet works.

SQL Query
EXEC SQL

SELECT (CASE
       WHEN COUNT(SX008.NUM_CPF_CNPJ) = 0
         THEN 1
         ELSE 4
       END)
INTO :WS-IND-TP-PREMIO
  FROM SX_EXC_COBER SX112
            WHERE SX112.COD_PARAM_EXC_COBER = :SX112-COD-PARAM-EXC-COBER
                AND SX112.NUM_COBERTURA = :SX112.NUM-COBERTURA
END-EXEC


RTFM here, http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_caseexpression.htm
Quasar Chunawala,
Software Engineer, Lives at Borivali, Mumbai
User avatar
Quasar
 
Posts: 102
Joined: Wed Nov 10, 2010 7:11 pm
Location: Borivali, Mumbai
Has thanked: 13 times
Been thanked: 2 times

Re: Select case

Postby itamar rocha » Thu Mar 13, 2014 12:28 am

Hello Quasar
I changed the code here and it is working,
What book do you reccomend to read about db2 querys?
Thanks for the answer,
itamar rocha
 
Posts: 41
Joined: Mon Nov 11, 2013 8:46 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Select case

Postby Quasar » Thu Mar 13, 2014 12:40 am

Itamar,

You can ask this on a separate thread. Nevertheless, read the fine manual, when in doubt. There are IBM Redbook publications that include application programming guides, performance tuning guides for all IBM System software. In my opinion, DB2 Developer's guide authored by Craig S. Mullins is an extremely good book.

About the above thread, replacing AS in the original query with INTO worked for you?
Quasar Chunawala,
Software Engineer, Lives at Borivali, Mumbai
User avatar
Quasar
 
Posts: 102
Joined: Wed Nov 10, 2010 7:11 pm
Location: Borivali, Mumbai
Has thanked: 13 times
Been thanked: 2 times

Re: Select case

Postby dick scherrer » Fri Mar 14, 2014 12:48 am

Hello,

Everything i've heard says the Craig Mullins book is excellent.

There is also a DB2 book at http://murach.com/books/database.htm.

I have several books from Murach and all have been worth the money. That said, i do not have the DB2 book.
Hope this helps,
d.sch.

These users thanked the author dick scherrer for the post:
Quasar (Sat Mar 15, 2014 1:08 am)
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Previous

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post