Page 2 of 2

Re: Select case

PostPosted: Tue Mar 11, 2014 11:07 pm
by itamar rocha
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

Re: Select case

PostPosted: Wed Mar 12, 2014 12:01 am
by NicC
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.

Re: Select case

PostPosted: Wed Mar 12, 2014 2:15 am
by dick scherrer
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.

Re: Select case

PostPosted: Wed Mar 12, 2014 9:29 pm
by itamar rocha
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

Re: Select case

PostPosted: Wed Mar 12, 2014 9:33 pm
by itamar rocha
Hello,
I know how to use code tags
Thank you

Re: Select case

PostPosted: Thu Mar 13, 2014 12:17 am
by Quasar
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

Re: Select case

PostPosted: Thu Mar 13, 2014 12:28 am
by itamar rocha
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,

Re: Select case

PostPosted: Thu Mar 13, 2014 12:40 am
by Quasar
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?

Re: Select case

PostPosted: Fri Mar 14, 2014 12:48 am
by dick scherrer
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.