Toad World® Forums

Counters for specific dates

Hi,

How can I create a formula using a specific date for these counters, pmRrcConnEstabAtt and pmRrcConnEstabSucc?

The one I created is having error

SUM(VTC2E6.pmRrcConnEstabAtt) WHERE VTC2E6.DATE_ID = 2016-1-18

Here is the error below

ERROR line 5, column 67, ending line 5, column 68: FOUND ‘AS’: Expecting: end-of-input -or- EXCEPT INTERSECT MINUS UNION -or- CLUSTER DISTRIBUTE FOR GROUP HAVING INTO LIMIT LOCK OFFSET ORDER PROCEDURE SORT UPDATE -or- WITH -or- OR XOR ||

Here is the script

SELECT VTC2E6.DATE_ID
, VTC2E6.ERBS
, SUM(VTC2E6.pmRrcConnEstabAtt)
, SUM(VTC2E6.pmRrcConnEstabSucc)
, SUM(VTC2E6.pmRrcConnEstabAtt) WHERE VTC2E6.DATE_ID = 2016-1-18 AS TEST
FROM VTC2E6_3.8 (dc), dc.dc.DC_E_ERBS_EUTRANCELLFDD_RAW VTC2E6
WHERE ( DC_E_ERBS_EUTRANCELLFDD_RAW.DATE_ID IN (‘2016-1-11’,‘2016-1-18’))
AND ( VTC2E6.ERBS = ‘GNL03402’)
GROUP BY VTC2E6.DATE_ID
, VTC2E6.ERBS
UNION
SELECT VTC2E7.DATE_ID
, VTC2E7.ERBS
, SUM(VTC2E7.pmRrcConnEstabAtt)
, SUM(VTC2E7.pmRrcConnEstabSucc)
FROM VTC2E7_3.8 (dc), dc.dc.DC_E_ERBS_EUTRANCELLFDD_RAW VTC2E7
WHERE ( DC_E_ERBS_EUTRANCELLFDD_RAW.DATE_ID IN (‘2016-1-11’,‘2016-1-18’))
AND ( VTC2E7.ERBS = ‘SCL01304’)
GROUP BY VTC2E7.DATE_ID
, VTC2E7.ERBS

This looks like a cross query. Cross queries use mysql syntax, so that particular formula would fail.

I would suggest using a case statement:

dev.mysql.com/…/case.html

ex.

case VTC2E6.DATE_ID WHEN 2016-1-18 THEN SUM(VTC2E6.pmRrcConnEstabAtt) END AS TEST

Hi Ryan,

I tried to bind the calculated dates but it seems I am having this error.

[Sybase][ODBC Driver][Sybase IQ] Syntax error near ‘=’ on line 1

Here is the query

**SELECT DC_E_ERBS_EUTRANCELLFDD_RAW.DATE_ID = :Current_Date AS Current_Date **

, DC_E_ERBS_EUTRANCELLFDD_RAW.DATE_ID = :Previous_Date AS Previous_Date

FROM dc.DC_E_ERBS_EUTRANCELLFDD_RAW DC_E_ERBS_EUTRANCELLFDD_RAW

Is it possible to use bind with these two calculated dates?

Thank you!!!

I am not sure what you are trying to do here. That does not look like valid syntax. Were you doing a compare between the bind variables and the column values? Then you would need to put them in a where clause

Ex.

SELECT DC_E_ERBS_EUTRANCELLFDD_RAW.DATE_ID AS Current_Date

, DC_E_ERBS_EUTRANCELLFDD_RAW.DATE_ID AS Previous_Date

FROM dc.DC_E_ERBS_EUTRANCELLFDD_RAW DC_E_ERBS_EUTRANCELLFDD_RAW

WHERE DC_E_ERBS_EUTRANCELLFDD_RAW.DATE_ID = :Current_Date AND DC_E_ERBS_EUTRANCELLFDD_RAW.DATE_ID = :Previous_Date

I tried that before and it’s giving me no results so I was thinking that this one seems correct and just need some tweaking.

Basically, dates are changing and I want to get the difference between the current and previous values. Also, I need this included in the formula.

Thank you!!!

You should be able to do this. Can you open a support ticket? Also have this query in the query builder with focus and generate a support bundle from the Help menu. This will gather the SQL and DDL to help us recreate the issue.