SqlDataSource CASE in SELECT

Smart GWT Technical Q&A
1

I’m using SmartGWT Power 2.3.

I’m wondering about best practice for this situation; I need to analyze a column value and bump an associated counter by 1 whenever it equals specific values. I’ve accomplished it via <customSql> (complete select), but wondered if there is an alternative way where I don’t lose the paging benefits.

Here’s the SQL snippet.

SELECT 
  SUM(CASE WHEN lc.response = 'complete' THEN 1 ELSE 0 END) AS complete,
  SUM(CASE WHEN lc.response = 'not qualified' THEN 1 ELSE 0 END) AS dq,
  SUM(CASE WHEN lc.response = 'failed screen' THEN 1 ELSE 0 END) AS screened
FROM 
  LeadCalc lc
WHERE 
  dt.createdAt between '2010-11-10' and '2010-11-10'

Thanks in advance.

2

Seems like you can just customize <selectClause>, <fromClause> and <whereClause> and still get paging - did you try that?

3

Thanks for the quick reply.

I tried it, and it should work, but it doesn’t. The SQL provided in my original post was simplified, and once I added all data & tables, both <customSql> and the individual <clause> approached produce the same error. It’s a SQL Server error, but it comes from the WHERE clause produced by $defaultWhereClause.

The error: “The multi-part identifier “Dimension.DateTimes.createdAtDayPacific” could not be bound.” It’s referring to this where clause:

WHERE (((Dimension.DateTimes.createdAtDayPacific >= '2010-11-10' 
 AND Dimension.DateTimes.createdAtDayPacific IS NOT NULL)  
 AND (Dimension.DateTimes.createdAtDayPacific <= '2010-11-10' 
     OR Dimension.DateTimes.createdAtDayPacific IS NULL)))

I ran the generated SQL, and this is the corrected where clause:

WHERE (((createdAtDayPacific >= '2010-11-10' 
 AND createdAtDayPacific IS NOT NULL) 
 AND (createdAtDayPacific <= '2010-11-10' 
     OR createdAtDayPacific IS NULL)))

I have a DateRangeItem in the UI which drives the criteria. It defaults to YESTERDAY.

DateRangeItem rangeItem = new DateRangeItem("createdAtDayPacific", "Date Range");

The datasource field definition is:

 <field name="createdAtDayPacific"   type="datetime" 
                  tableName="Dimension.DateTimes"             />

I’ve attached the datasource, the ListGrid and the console output. If you could please give me a head’s up as to what I’ve incorrectly configured, I’d appreciate it.

AccountsDashboardSummaryGrid.java (7.83 KB)

SmartGwt_DSErrorLog.txt (17.8 KB)

LeadCompleted.ds.xml (3.76 KB)

4

You’ve set tableName to “Dimension.DateTimes” in your .ds.xml file, so that’s what the SQL generator is using. Is this value wrong, or perhaps not needed here? What if you just remove that setting?

5

The schema.tablename is correct. But not needed. Removing the table name, such as below, does the trick.

<field name="createdAtDayPacific"   type="datetime" />

Thank you!