If I have such a SQL query using WITH (CTE) can I get the same results without it?

Question

If I have such a SQL query using WITH (CTE) can I get the same results without it?

Navigation

#1 by (1 votes)

1

I was doing a view that uses WITH, and it turns out that the Web Client Compiere does not get along with the views with WITH .. (they told me)

Then I wanted to know if I can reach the same results in the data without using WITH.

If you have an example, or if you can not and because, I would appreciate it!

As requested here the nice example query : (this query is functional, can be used in BIRT, remember that in the symbols '?' go the parameters (values) ...

However, to work it inside the Compiere does not work for me. :(

WITH ASIGNADOS AS(
SELECT CC.X_RolConteo_ID, CC.X_OrgSubZonaELEMENT_ID
FROM M_ABCAnalysisGroup A
INNER JOIN M_ABCRank R ON (A.M_ABCAnalysisGroup_ID = R.M_ABCAnalysisGroup_ID)
INNER JOIN M_ABCProductAssignment PA ON (R.M_ABCRank_ID = PA.M_ABCRank_ID)
INNER JOIN XX_CN_ABCProductCycleCounting CC ON (CC.M_ABCProductAssignment_ID = PA.M_ABCProductAssignment_ID)
WHERE R.M_ABCRank_ID = ?
AND CC.X_RolConteo_ID IS NOT NULL 
AND CC.X_RolConteo_ID != 100
GROUP BY CC.X_OrgSubZonaELEMENT_ID, CC.X_RolConteo_ID
),
CONTADOS AS(
SELECT CC.X_RolConteo_ID, CC.X_OrgSubZonaELEMENT_ID
FROM M_ABCAnalysisGroup A
INNER JOIN M_ABCRank R ON (A.M_ABCAnalysisGroup_ID = R.M_ABCAnalysisGroup_ID)
INNER JOIN M_ABCProductAssignment PA ON (R.M_ABCRank_ID = PA.M_ABCRank_ID)
INNER JOIN XX_CN_ABCProductCycleCounting CC ON (CC.M_ABCProductAssignment_ID = PA.M_ABCProductAssignment_ID)
WHERE R.M_ABCRank_ID = ?
AND CC.X_RolConteo_ID IS NOT NULL 
AND CC.X_RolConteo_ID != 100
AND PA.ISCOUNTED = 'Y'
GROUP BY CC.X_RolConteo_ID, CC.X_OrgSubZonaELEMENT_ID
)
SELECT  
RC.X_RolConteo_ID,
BP.NAME||' - #'|| BP.ficha BPNameFicha,
OSZL.X_OrgSubZonaLINE_ID,
SZ.Name SubZonaName,
SZ.Name||' '||OSZL.LOCATORVALUE SUBZONA,
CASE WHEN UPPER(OSZL.LOCATORVALUE) = LOWER (OSZL.LOCATORVALUE) THEN INTEGER(OSZL.LOCATORVALUE) ELSE 0 END AS SUBZONA_NUM,
CASE WHEN UPPER(OSZL.LOCATORVALUE) != LOWER (OSZL.LOCATORVALUE) THEN OSZL.LOCATORVALUE ELSE '' END AS SUBZONA_STR,
COUNT(OSZE.X_OrgSubZonaElement_ID) CANT_ELEMENTOS,
COUNT (A.X_OrgSubZonaElement_ID) CANT_ASIG,
COUNT (C.X_OrgSubZonaElement_ID) CANT_CONT
FROM X_OrgSubZonaLine OSZL
INNER JOIN X_OrgSubZonaElement OSZE ON (OSZL.X_OrgSubZonaLINE_ID = OSZE.X_OrgSubZonaLINE_ID)
INNER JOIN X_OrgSubZona OSZ ON (OSZL.X_OrgSubZona_ID = OSZ.X_OrgSubZona_ID)
INNER JOIN X_OrgElement OE ON (OSZE.X_OrgElement_ID = OE.X_OrgElement_ID AND OSZ.X_OrgSubZona_ID = OE.X_OrgSubZona_ID)
INNER JOIN X_Subzona SZ ON (OSZ.X_Subzona_ID = SZ.X_Subzona_ID)
INNER JOIN X_Element E ON (OE.X_Element_ID = E.X_Element_ID)

LEFT JOIN ASIGNADOS A ON (OSZE.X_OrgSubZonaELEMENT_ID = A.X_OrgSubZonaELEMENT_ID)
LEFT JOIN CONTADOS C ON (OSZE.X_OrgSubZonaELEMENT_ID = C.X_OrgSubZonaELEMENT_ID)

INNER JOIN X_RolConteo RC ON (A.X_RolConteo_ID = RC.X_RolConteo_ID)
LEFT JOIN C_BPARTNER BP ON (RC.C_BPARTNER_ID = BP.C_BPARTNER_ID)
WHERE OSZL.AD_ORG_ID = ?
GROUP BY RC.X_RolConteo_ID, OSZL.X_OrgSubZonaLINE_ID, 
OSZL.LOCATORVALUE,
SZ.Name,OSZL.LOCATORVALUE, BP.NAME, BP.ficha
ORDER BY BP.NAME, SZ.NAME, SUBZONA_NUM, SUBZONA_STR

An important thing .. what I show in the report is: Worker's name Subzone (where you are counting - "location") Assigned elements (number of items in the product account assigned to a worker) Items Counted (number of counted products) the advanced% ... and the totals of those assigned, counted and progress. but in this the BIRT does it alone, in this case I would have to put the calculation inside the query sql ..

sql db2

asked by HeckDan 23.02.2018 в 15:31

source

1 answer

Activate the BroadcastReceiver after a certain time Nested rows with bootstrap grid [closed]

score 1 · Answer 1

This change is not really complicated ...

Suppose this Query:

CREATE VIEW V_TOTAL_VENTAS(    SUC_CODIGO,    VENTAS2011,    VENTAS2012) AS
     WITH TotalVentasAnuales AS (
      SELECT
         MVC_CODSUC,
         EXTRACT(YEAR FROM MVC_FECHAX) AS Ano,
         SUM(MVC_TOTALX * MVC_COTIZA) AS VentasAnuales
      FROM
         MOVIMCAB
      GROUP BY
         1, 2    )
     SELECT
      S.SUC_CODIGO,
      V2011.VENTASANUALES AS VENTAS2011,
      V2012.VENTASANUALES AS VENTAS2012    FROM
      SUCURSALES S    LEFT JOIN
      TotalVentasAnuales V2011
         ON S.SUC_CODIGO = V2011.MVC_CODSUC AND
            V2011.Ano = 2011    LEFT JOIN
      TotalVentasAnuales V2012
         ON S.SUC_CODIGO = V2012.MVC_CODSUC AND
            V2012.Ano = 2012;

Now a view with WITH, and it does not work for us according to what you work with, it does not like the CTE ... oooww .. BUT THERE IS NO PROBLEM!

We see that the CTE (WITH) TotalVentasAnuales abbreviated "V2012" is called by a LEFT JOIN , well now c we set TotalVentasAnuales by a AS , if and now after left join we place the internal query of WITH , and we already get rid of our friend !! :) here goes:

SELECT
      S.SUC_CODIGO,
      V2011.VENTASANUALES AS VENTAS2011,
      V2012.VENTASANUALES AS VENTAS2012    FROM
      SUCURSALES S    LEFT JOIN
      (
       SELECT
         MVC_CODSUC,
         EXTRACT(YEAR FROM MVC_FECHAX) AS Ano,
         SUM(MVC_TOTALX * MVC_COTIZA) AS VentasAnuales
      FROM
         MOVIMCAB
      GROUP BY 1, 2
      )
        AS V2011 ON S.SUC_CODIGO = V2011.MVC_CODSUC AND V2011.Ano = 2011 
        LEFT JOIN TotalVentasAnuales V2012
        ON S.SUC_CODIGO = V2012.MVC_CODSUC AND V2012.Ano = 2012;

Try to follow these steps and try your tables! I think I made it understandable for any case, the last thing you would have to do would be to see what extra fields you would need to Compiere to generate the table (view) that will help you create the window ...

I recommend these links are true treasures of the web (stack .. would be the treasure planet XD hehe):

link