How to call the value of a Subquery within another Subquery and they are in the same View

Question

I have a View with several Subqueries, one of them should be used to generate another Subquery within the View itself but I am receiving error, example:

(SELECT COUNT(0)
   FROM `LICENCAS`
  WHERE (`LICENCAS`.`FK_PEDIDO` = `A`.`FK_PEDIDO`) AND Dia_Corrido_Assinatura > `LICENCAS`.`CICLO`) AS `DownloadCount`,
(SELECT CEILING((TIMESTAMPDIFF(SECOND, `A.``ASSINATURA_DATA_INICIO`, NOW()) / 86400))) AS `Dia_Corrido_Assinatura`,
FROM `N_ASSINATURAS` `A`

I’m having trouble in Subquery DownloadCount for within it I call the value of the Subquery Dia_Corrido_Assinatura but I get the message:

Error Code: 1247. Reference 'Dia_corrido_signature' not supported (forward Reference in item list)

I even understand that since it is a Subquery, it may not even have been generated to be called, there are some options on top of that?

EDIT: I already tried to change order by placing the 'Dia_corrido_signature' sub first, but I continue with the same error.

Answer 1

You can use your daily subselect from. For example:

SELECT 
--esse é seu subselect download
(SELECT COUNT(0)
   FROM `LICENCAS`
  WHERE (`LICENCAS`.`FK_PEDIDO` = `A`.`FK_PEDIDO`) AND tab.dias_ocorridos > `LICENCAS`.`CICLO`) AS `DownloadCount`
  tab.dias_ocorridos
FROM (SELECT referencia_id, 10 dias_ocorridos FROM N_ASSINATURAS) tab, N_ASSINATURAS
WHERE
N_ASSINATURAS.referencia_id = tab.referencia_id

Or make a function that returns that value, or another view separately. You cannot create columns within the same select clause. Like this:

 select 1 um,
           2 dois,
           um+dois as soma
    from dual

Doesn’t work