IBM Mainframe Forum

by **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

by **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.

by **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.

Select all

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.

by **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

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

Hello,
I know how to use code tags
Thank you

by **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

Select all

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

by **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,

by **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?

by **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.

IBM Mainframe Forum

Select case

Re: Select case

Re: Select case

Re: Select case

Re: Select case

Re: Select case

Re: Select case

Re: Select case

Re: Select case

Re: Select case