Logo
Zoho Forums
« Back
You are in : Recent Topics » Zoho Reports » Reports Feedback » null values
castle79

null values
by 
castle79
 on 23-Feb-2009 12:42 AM.
  in  Zoho Reports 
» Reports Feedback
I have the following table
Amount   Category              Sub category
$5.00      Entertainment
$5.00      Groceries
$5.00      Entertainment       Entertainment-Lunch


I have a SQL table that tries to spit out a pivot like table
SELECT
CASE "Sub-Category" WHEN NULL THEN "Amount" ELSE 0 END AS "Unclassified",
CASE "Sub-Category" WHEN 'Entertainment-Lunch' THEN "Amount" ELSE 0 END AS "Lunch",
"Amount",
FROM "t_expenses" WHERE "Category" = 'Entertainment'


this should output
Unclassified     Lunch      Amount
$5.00                              $5.00
                        $5.00      $5.00

instead, I get:
Unclassified     Lunch      Amount
                        $5.00      $5.00



Is it possible to recognize a "null" Sub-category?  The empty string '' did not work either.
  • No status
sathyav

Re: null values

by 
sathyav
 on 23-Feb-2009 07:17 AM
Hi

When comparing null values for a column we would recommend you to use the IS NULL function.   Kindly find below the modified query,

SELECT
CASE  WHEN "Sub-Category" IS NULL THEN "Amount" ELSE 0 END AS "Unclassified",
CASE "Sub-Category" WHEN 'Entertainment-Lunch' THEN "Amount" ELSE 0 END AS "Lunch",
"Amount"
FROM "Customer" WHERE "Category" = 'Entertainment'

Believe that the above solves the purpose and please do let us know for any further assistance.

Sathya V
Zoho DB & Reports
an Online Business Intelligence solutions
kovilpillai

Re: null values

by 
kovilpillai
(Employee)
 on 23-Feb-2009 01:54 PM
SELECT
IF("Sub-Category" IS NULL, "Amount", 0) "Unclassified",
IF("Sub-Category" = 'Entertainment-Lunch', "Amount", 0)"Lunch",
"Amount"
FROM "t_expenses" WHERE "Category" = 'Entertainment'

Does this work?

castle79

Re: null values

by 
castle79
 on 27-Feb-2009 08:14 PM
satyav and kovilpillai,

your suggestions have not solved the issue.  any other suggestions?  It really doesn't appear to be pulling the 1st record at all.



sathyav

Re: null values

by 
sathyav
 on 02-Mar-2009 06:15 AM

Hi,

Sorry that it fails for you., however we are not successful in reproducing the same error here in our test environment.  May I request you to share the table and the query you have reported above to support@zohodb.com, as that will help us to resolve the issues (if any) at the earliest.

Thanks & Regards,
Sathya V
Zoho DB & Reports
an Online Business Intelligence solutions


sathyav

Re: null values

by 
sathyav
 on 03-Mar-2009 07:17 AM


Hi ,

Thanks for sharing the required objects.  We have reproduced and analysed the issue.   The issue you have reported occurs only if the column "Sub-Category" (being used in the query)  is a look-up column.  We acknowledge that there is an issue in retrieving the NULL values of a look up column.   However there is a work around, and please find below the same,

Kindly replace the NULL value with some text value ('EMPTY', or 'NONE', or 'NIL', ...) in the respective base table and use it in the look-up column.  Now you can use this text value in your query instead of comparing the column for a NULL value. 

We believe that the helps you to resolve the issue for the time being.  We will keep you posted once we update the service with the fix.  In case, our assumption is not correct, do not hesitate to get in touch with us.

Thanks for your time and assuring the best of our service.

Regards,
Sathya V
Zoho Reports
rajeshkota

Re: null values

by 
rajeshkota
 on 11-Nov-2009 09:54 AM

Hi,


Thanks for your patience. Previously Zoho Reports doesn't give the NULL value records in the query table if the query has lookup columns. Now we have fixed the issue and updated the service with the same. Kindly try the above query, which you have tried earlier and let us know for any further clarifications.

Thanks for the co-operation and assuring the best of our service.

 Back
 Top
Post Actions
Statistics
  • 6
     Replies
  • 506
     Views
  • 0
     Followers
Tags for the post
No tags available for this topic.

Edit Link Delete Link

Edit Link Delete Link

LoadingImage