Error when consulting with pivot

Navigation
-2

When making the query it generates an error which says that it does not recognize the name of the column t1.codigo or the name of the column t1.examen :

  

the identifier formed by several parts T2.CODIGO, could not be   link

I have reviewed the syntax and I do not give. Any help please.

this is the code 

   SELECT  GENPACIEN.PACNUMDOC AS DOCUMENTO_PACIENTE, 
GENPACIEN.PACPRINOM + ' ' + GENPACIEN.PACSEGNOM + ' ' + GENPACIEN.PACPRIAPE + ' ' + GENPACIEN.PACSEGAPE AS NOMBRES,
ADNINGRESO.AINCONSEC AS INGRESO,
T1.[SIDA ANTICUERPOS VIH  1.], 
T1.[SIDA ANTICUERPOS VIH  2.],
T1.[SIFILIS SEROLOGIA PRESUNTIVA (CARDIOLIPINA O VDRL)],
T1.[VIH 1 Y 2  ANTICUERPOS]
FROM GENPACIEN
INNER JOIN ADNINGRESO
ON ADNINGRESO.GENPACIEN = GENPACIEN.OID
INNER JOIN HCNSOLEXA
ON HCNSOLEXA.ADNINGRESO= ADNINGRESO.OID
INNER JOIN 

(SELECT * FROM
(SELECT GENSERIPS.SIPNOMBRE AS EXAMEN, GENSERIPS.OID AS CODIGO  FROM GENSERIPS WHERE  GENSERIPS.OID IN(1631,1626,1627,7457)) AS T2
PIVOT( SUM (CODIGO) FOR EXAMEN IN( [SIDA ANTICUERPOS VIH  1.], [SIDA ANTICUERPOS VIH  2.], [SIFILIS SEROLOGIA PRESUNTIVA (CARDIOLIPINA O VDRL)], [VIH 1 Y 2  ANTICUERPOS] )  ) AS PIVOTEXAMEN 
) AS T1
ON  T2.CODIGO = HCNSOLEXA.GENSERIPS
INNER JOIN HCNFOLIO
ON HCNFOLIO.GENPACIEN = GENPACIEN.OID
INNER JOIN HCMAIEPI
ON HCMAIEPI.HCNFOLIO = HCNFOLIO.OID
WHERE ADNINGRESO.AINFECING >='01-05-2018 00:00:00.00' AND  ADNINGRESO.AINFECING < '01-06-2018 00:00:00.00'  
GROUP BY T2.EXAMEN  ,ADNINGRESO.AINCONSEC,GENPACIEN.PACNUMDOC, GENPACIEN.PACPRINOM , GENPACIEN.PACSEGNOM , GENPACIEN.PACPRIAPE , GENPACIEN.PACSEGAPE
ORDER BY 3 ASC
    
asked by Ricardo Roa 26.07.2018 в 15:23
source

2 answers

0

I already got the code, thanks. 

SELECT  GENPACIEN.PACNUMDOC AS DOCUMENTO_PACIENTE, 
GENPACIEN.PACPRINOM + ' ' + GENPACIEN.PACSEGNOM + ' ' + GENPACIEN.PACPRIAPE + ' ' + GENPACIEN.PACSEGAPE AS NOMBRES,
ADNINGRESO.AINCONSEC,
T2.[SIDA ANTICUERPOS VIH  1.],
T2.[SIDA ANTICUERPOS VIH  2.],
T2.[SIFILIS SEROLOGIA PRESUNTIVA (CARDIOLIPINA O VDRL)],
T2.[VIH 1 Y 2  ANTICUERPOS]
FROM GENPACIEN
INNER JOIN ADNINGRESO
ON ADNINGRESO.GENPACIEN = GENPACIEN.OID
INNER JOIN 


(SELECT * FROM 

(SELECT HCNSOLEXA.HCSCANTI AS CANTIDAD ,GENSERIPS.SIPNOMBRE AS EXAMEN_PACIENTE,HCNSOLEXA.ADNINGRESO AS INGRESO2 FROM 
HCNSOLEXA INNER JOIN GENSERIPS
ON HCNSOLEXA.GENSERIPS= GENSERIPS.OID
WHERE GENSERIPS.OID IN (1631,1626,1627,7457)
) AS T1


PIVOT 
(
COUNT(CANTIDAD) FOR EXAMEN_PACIENTE IN ([SIDA ANTICUERPOS VIH  1.],[SIDA ANTICUERPOS VIH  2.],[SIFILIS SEROLOGIA PRESUNTIVA (CARDIOLIPINA O VDRL)],[VIH 1 Y 2  ANTICUERPOS])
) AS PV


)T2

ON T2.INGRESO2= ADNINGRESO.OID
INNER JOIN HCNFOLIO
ON HCNFOLIO.GENPACIEN = GENPACIEN.OID
INNER JOIN HCMAIEPI
ON HCMAIEPI.HCNFOLIO = HCNFOLIO.OID
WHERE ADNINGRESO.AINFECING >='01-05-2018 00:00:00.00' AND  ADNINGRESO.AINFECING < '01-06-2018 00:00:00.00'
GROUP BY T2.[SIDA ANTICUERPOS VIH  1.],
T2.[SIDA ANTICUERPOS VIH  2.],
T2.[SIFILIS SEROLOGIA PRESUNTIVA (CARDIOLIPINA O VDRL)],
T2.[VIH 1 Y 2  ANTICUERPOS],ADNINGRESO.AINCONSEC,GENPACIEN.PACNUMDOC, GENPACIEN.PACPRINOM , GENPACIEN.PACSEGNOM , GENPACIEN.PACPRIAPE , GENPACIEN.PACSEGAPE
ORDER BY 3 ASC
    
answered by 26.07.2018 / 19:30
source
0

Try this (replace these lines), and tell me, most likely the error is because you are trying to relate an Alias or a route that you are not reaching: 

  (SELECT * FROM
  (SELECT GENSERIPS.SIPNOMBRE AS EXAMEN, GENSERIPS.OID AS CODIGO  FROM 
  GENSERIPS WHERE  GENSERIPS.OID IN(1631,1626,1627,7457)) T2
  PIVOT( SUM (GENSERIPS.OID) FOR EXAMEN IN( [SIDA ANTICUERPOS VIH  1.], [SIDA 
  ANTICUERPOS VIH  2.], [SIFILIS SEROLOGIA PRESUNTIVA (CARDIOLIPINA O VDRL)], 
  [VIH 1 Y 2  ANTICUERPOS] )  ) AS PIVOTEXAMEN 
  )  T1
  ON  T1.T2.GENSERIPS.OID = HCNSOLEXA.GENSERIPS
    
answered by 26.07.2018 в 15:51
Get protocol and PHP domain Error: java.lang.arrayindexoutofboundsexception: 15