Query with conditions operating with ALIAS in SQL

Navigation
1

Keeping in mind that A and C are subqueries ... that represent the stipulated articles and the counted articles 

COUNT(OSZE.X_OrgSubZonaElem_ID) CANT_ELEMENTOS,
COUNT (A.X_OrgSubZonaElem_ID) CANT_ASIG,
COUNT (C.X_OrgSubZonaElem_ID) CANT_CONT,


     CASE WHEN CANT_ELEMENTOS != 0 AND CANT_ELEMENTOS IS NOT NULL AND CANT_CONT != 0 AND CANT_CONT IS NOT NULL 
     THEN (CANT_CONT*100)/CANT_ELEMENTOS ELSE 0 END AS PCTJ_AVANCE, 

     SUM (CANT_ELEMENTOS) AS TOTAL_ELEMENT,

     SUM (CANT_CONT) AS TOTAL_COUNTED,

     CASE WHEN TOTAL_ELEMENT !=0 AND TOTAL_ELEMENT IS NOT NULL  AND TOTAL_COUNTED IS NOT NULL AND TOTAL_COUNTED !=0 
     THEN (TOTAL_COUNTED*100)/TOTAL_ELEMENT   ELSE 0 END AS TOTAL_AVANCE

Is it valid to use an ALIAS to operate? - it is marking me an error: it starts at CASE WHEN CANT_ELEMENTS says that the global variable CANT_ELEMENTS is not found

Responding to the comments .. I must also take out the totals, they would be: SUM (C.CampoElemento_ID) AS TOTAL_CONT and SUM (A.CampoElemento_ID) AS TOTAL_ASIG true? I saw no, because it is an ID and I can not add your ID to a beastly number ... and I can not make a SUM (COUNT) :( How do I do it?

    
asked by HeckDan 06.03.2018 в 18:16
source

1 answer

1

I would go more or less like this ..

Ah! and the SUM you redundan are practically the same as you have above ... just that you put another name I imagine that to operate. But you can not operate with ALIS of the SAME LEVEL. They would have to be from another subquery or in this way: 

CASE WHEN COUNT(OSZE.CampoElemento_ID) != 0 AND COUNT(OSZE.CampoElemento_ID) IS NOT NULL 
    AND COUNT (C.CampoElemento_ID) != 0 AND COUNT (C.CampoElemento_ID) IS NOT NULL 
    THEN (COUNT (C.CampoElemento_ID)*100)/COUNT(OSZE.CampoElemento_ID) ELSE 0 
    END AS PCTJ_AVANCE,
    CASE WHEN COUNT(OSZE.CampoElemento_ID) IS NOT NULL AND COUNT(OSZE.CampoElemento_ID) !=0
    AND COUNT (C.CampoElemento_ID) IS NOT NULL AND COUNT (C.CampoElemento_ID) !=0 
    THEN (COUNT (C.CampoElemento_ID)*100)/COUNT(OSZE.CampoElemento_ID)  ELSE 0 
    END AS TOTAL_AVANCE

(if I'm wrong, please correct my fault, I accept criticism.)

    
answered by 06.03.2018 в 20:08
Geometric Data MvcHtmlString.Create () returns a semicolon ";"