How to fix error where "OPEN" is used in SQL query?

Question

While running this query for open it's not working and showing incorrect use of open.

SELECT CAPABILITY, NEW,Open,FIXED,BLOCKED,REJECTED,DEFERRED,REOPEN,RETEST,COALESCE(BLOCKED,0)+ COALESCE(REJECTED,0)+COALESCE(NEW,0)+COALESCE('Open',0)+COALESCE(DEFERRED,0)+COALESCE(REOPEN,0)+COALESCE(FIXED,0)+COALESCE(RETEST,0) AS "Total"
FROM
(SELECT CAPABILITY, MAX(NEW) AS NEW,MAX(Open) AS Open, MAX(FIXED) AS FIXED, MAX(BLOCKED) AS BLOCKED,
MAX(REOPEN) AS REOPEN, MAX(REJECTED) AS REJECTED ,MAX(DEFERRED) AS DEFERRED,MAX(RETEST)AS RETEST
FROM(SELECT DISTINCT CASE WHEN CAPABILITY IS NULL THEN 'Unknown' ELSE CAPABILITY END AS CAPABILITY,
  CASE When Status ='New' Then CNT ELSE 0 END AS NEW,
  CASE When Status ='Open' Then CNT ELSE 0 END AS Open,
  CASE When Status ='Fixed' Then CNT ELSE 0 END AS FIXED,
  CASE When Status ='Blocked'Then CNT ELSE 0 END AS BLOCKED,
  CASE When Status ='Rejected' Then CNT ELSE 0 END AS REJECTED,
  CASE When Status ='Deferred' Then CNT ELSE 0 END AS DEFERRED,
  CASE When Status ='Reopen' Then CNT ELSE 0 END AS REOPEN,
  CASE When Status ='Re-Test' Then CNT ELSE 0 END AS RETEST
FROM  (SELECT  BUG.BG_USER_60 AS CAPABILITY, BUG.BG_STATUS AS Status, Count( BUG.BG_BUG_ID ) AS CNT FROM BUG
       WHERE
(BUG.BG_SEVERITY='2-High' OR BUG.BG_SEVERITY='1-Urgent') AND ( BUG.BG_USER_65='Data Integration' OR BUG.BG_USER_65='Configurable Reporting'OR  BUG.BG_USER_65='Contact Center' OR BUG.BG_USER_65='Strategic Information Delivery'OR  BUG.BG_USER_65='Tech Strategy'OR BUG.BG_USER_65='Web Portals' OR BUG.BG_USER_65='Workflow and Imaging'OR  BUG.BG_USER_65='RS DC Core'  )
GROUP BY BUG.BG_USER_60, BUG.BG_STATUS ) AS YNT ) AS GNS GROUP BY CAPABILITY) AS TBN

`Open` is a reserved term. Try using `[OPEN]` in your select instead. — Octopoid, Sep 21 '15 at 17:13
Are you using MySQL or SQL Server? They are two very different products. What error are you getting? Please add it to your question, by use of the edit feature. Please also explain, in the question again, how QC is involved. — halfer, Sep 21 '15 at 19:43
I am writing query in quality centre to get all the report status ..so while excexuting this code I found open is reserved keyword I need my column name as OPEN please help me to rectify my error if possible . — vivek, Sep 22 '15 at 07:30

score 1 · Answer 1 · answered Sep 22 '15 at 14:40

'Try This...

SELECT 
CAPABILITY,NEW,[Open],FIXED,BLOCKED,REJECTED,DEFERRED,REOPEN,RETEST,
COALESCE(BLOCKED,0) + COALESCE(REJECTED,0) + COALESCE(NEW,0) + COALESCE([Open],0) + 
COALESCE(DEFERRED,0) + COALESCE(REOPEN,0) + COALESCE(FIXED,0) + COALESCE(RETEST,0)AS "Total"

FROM

(SELECT CAPABILITY, MAX(NEW)AS "NEW", MAX([Open])AS "Open", MAX(FIXED)AS "FIXED", MAX(BLOCKED)AS "BLOCKED",
MAX(REOPEN)AS "REOPEN", MAX(REJECTED)AS "REJECTED" , MAX(DEFERRED)AS "DEFERRED", MAX(RETEST)AS "RETEST"

FROM

(SELECT DISTINCT 
  CASE WHEN CAPABILITY IS NULL THEN 'Unknown' ELSE 'CAPABILITY' END AS "CAPABILITY",
  CASE WHEN Status ='New' THEN CNT ELSE 0 END AS "NEW",
  CASE WHEN Status ='Open' THEN CNT ELSE 0 END AS "Open",
  CASE WHEN Status ='Fixed' THEN CNT ELSE 0 END AS "FIXED",
  CASE WHEN Status ='Blocked'THEN CNT ELSE 0 END AS "BLOCKED",
  CASE WHEN Status ='Rejected' THEN CNT ELSE 0 END AS "REJECTED",
  CASE WHEN Status ='Deferred' THEN CNT ELSE 0 END AS "DEFERRED",
  CASE WHEN Status ='Reopen' THEN CNT ELSE 0 END AS "REOPEN",
  CASE WHEN Status ='Re-Test' THEN CNT ELSE 0 END AS "RETEST"

FROM

(SELECT BUG.BG_USER_60 AS "CAPABILITY", BUG.BG_STATUS AS "Status", Count(BUG.BG_BUG_ID)AS "CNT"
 FROM BUG
 WHERE
(BUG.BG_SEVERITY ='2-High' OR BUG.BG_SEVERITY ='1-Urgent') AND ( BUG.BG_USER_65 ='Data Integration' 
OR BUG.BG_USER_65 IN ('Configurable Reporting','Contact Center','Strategic Information Delivery','Tech Strategy','Web Portals'
,'Workflow and Imaging','RS DC Core')

GROUP BY BUG.BG_USER_60, BUG.BG_STATUS)AS "YNT")AS "GNS" GROUP BY CAPABILITY)AS "TBN"

How to fix error where "OPEN" is used in SQL query?

1 Answers1