1. 

   Steve44, Member

   I have created the stored procedure shown below. I want to look up certain values in a type table and assign them to variables like AmexType then use those values in a report query. I know one solution is to join the type tables to the report query, but I don't like that solution for readability and maintainability. When I run this report the Type varialbles are empty and only the last column has values. The value column in xlattable is a varchar that I am trying to CAST to an integer. Here are the 2 problems I am trying to solve:

   1 - The first four type queries appear as empty tables in the report. I would like to hide them.
   2 - The type values are empty so the first four columns have values of zero. I want to correct the query to show the values as integers.

   DROP PROCEDURE IF EXISTS sp_DBR_payasr_Payments_By_Method $$ CREATE PROCEDURE sp_DBR_payasr_Payments_By_Method( StartDate Date, EndDate Date ) BEGIN

   DECLARE AmexType INT; DECLARE CreditCardType INT; DECLARE AchType INT; DECLARE InvoiceType INT;

   SELECT AmexType = CAST(value AS UNSIGNED) FROM payasr.xlattable WHERE field_name = 'card_type' AND descr = 'Amex'; SELECT CreditCardType = CAST(value AS UNSIGNED) FROM payasr.xlattable WHERE field_name = 'payment_method_type' AND descr = 'CreditCard'; SELECT AchType = CAST(value AS UNSIGNED) FROM payasr.xlattable WHERE field_name = 'payment_method_type' AND descr = 'ACH'; SELECT InvoiceType = CAST(value AS UNSIGNED) FROM payasr.xlattable WHERE field_name = 'payment_method_type' AND descr = 'Invoice';

   SELECT DATE_FORMAT(lc.payment_dt,'%Y-%m-%d') AS 'Date', SUM(CASE lc.card_type WHEN AmexType THEN COALESCE(lc.amount, 0) ELSE 0 END) AS 'AMX', SUM(CASE WHEN lc.payment_method_type = CreditCardType AND lc.card_type <> AmexType THEN COALESCE(lc.amount, 0) ELSE 0 END) AS 'Credit Cards', SUM(CASE WHEN lc.payment_method_type = AchType THEN COALESCE(lc.amount, 0) ELSE 0 END) AS 'ACH', SUM(CASE WHEN lc.payment_method_type = InvoiceType THEN COALESCE(lc.amount, 0) ELSE 0 END) AS 'Invoice', SUM(COALESCE(lc.amount, 0)) AS 'Total' FROM payasr.loan_collection lc WHERE lc.payment_dt >= StartDate AND lc.payment_dt <= EndDate AND lc.process_code = 'SUCCESS' GROUP BY lc.payment_dt ORDER BY lc.payment_dt;

   END $$
2. 

   myDBR Team, Key Master

   Steve,
   MySQL uses bit different syntax when assigning query result to variable. Use following format:

   SELECT CAST(value AS UNSIGNED) into vAmexType
   FROM payasr.xlattable
   WHERE field_name = 'card_type' AND descr = 'Amex';

   This will fix both of your problems.

   If the value is already a number you do not need to use CAST.

   Couple of recommendations:

   • Try not to use DATE_FORMAT-function as it turns the date into a string and myDBR treats it like a string instead of a date. If lc.payment_dt is not a date (for example if it is a datetime), use CAST instead.
   • Use of COALESCE(lc.amount, 0) is unnecessary as if the lc.amount is null then sum aggregate function will just ignore that row.
   • You can use "lc.payment_dt between StartDate and EndDate" in where-clause.
   • You might want to differentiate the parameters and variables with some prefix (like inStartDate and vAmexType) so that they would not accidentally be mixed with columns if table happens to have a column with same name.
   
   

   
   --
   myDBR Team
3. 

   Steve44, Member

   myDBR Team

   Thank you so much. That worked perfectly. I think I had seen the INTO usage once in searching your documentation, but I had forgotten about it. Your other suggestions look fantastic, too. I have a question about your suggestion regarding the use of DATE_FORMAT. I used that function because I only want to see the date displayed, and not the time portion. Is there a better way of doing that?

   Steve
4. 

   myDBR Team, Key Master

   Yes,
   you can use "cast( lc.payment_dt as date)" instead. This will keep the datatype as date so myDBR can format the date to user based on the user preference and also treat it as a date (sorting etc.).

   --
   myDBR Team
5. 

   Steve44, Member

   Thanks. I'll give that a try. That sounds pretty clever.

   Steve

Reply

You must log in to post.