Клубове

Connection was lost to database

2 отговора 291 прегледа
Poceн #1

Connection was lost to database

Ползвам FireBird 1.5 и при изпълнение на заявка ме дисконектва от базата, която се намира на моя компютър. В заевката няма нищо съмнително и не е много сложна.
Благодаря за мнения и идеи.

Отговор
mr.vain в отговор на Poceн #2

Re: Connection was lost to database

Ами като за начало дай заявката...

Отговор
Poceн в отговор на mr.vain #3

Re: Connection was lost to database

Това е заявката, вече реших проблема, като не използвам вю-та, но не знма на какво се дължи, ако махна COUNT от последните два SELECTA- работи.


SELECT /*"Active"*/
CAST(1 AS INTEGER),
COUNT(CRA."IDDCrg"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
/* COUNT(C."IDDCrg"),
COUNT(CM."IDDCrg"),
COUNT(CY."IDDCrg")*/
FROM DCRG CRA
/*LEFT JOIN DCRG C ON (C."Date" = CURRENT_DATE)
LEFT JOIN DCRG CM ON (CM."IsActive" = 'Y' AND CM."Date" < CURRENT_DATE AND CM."Date" > CURRENT_DATE - 31)
LEFT JOIN DCRG CY ON (CY."IsActive" = 'Y' AND C."Date" < CURRENT_DATE AND CY."Date" > CURRENT_DATE - 366)*/
WHERE
(CRA."IsActive" = 'Y')

UNION ALL

SELECT /*"Working"*/
CAST(1 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(CRW."IDDCrg"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
FROM DCRG CRW
LEFT JOIN YCRGVALID YV ON (YV."IDDCrg" = CRW."IDDCrg")
WHERE
(YV."IsValid" = 'N')

UNION ALL

SELECT /*"UnActive"*/
CAST(1 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(CRU."IDDCrg"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
FROM DCRG CRU
LEFT JOIN YCRGVALID YV ON (YV."IDDCrg" = CRU."IDDCrg")
WHERE
((CRU."IsActive" = 'N') AND (YV."IsValid" = 'Y'))

UNION ALL

SELECT /*"Finished"*/
CAST(1 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(CRF."IDDCrg"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
FROM DCRG CRF
LEFT JOIN YCRGVALID YV ON (YV."IDDCrg" = CRF."IDDCrg")
WHERE
(CRF."IsFinished" = 'Y')

UNION ALL

SELECT /*"ALLtoday"*/
CAST(1 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(CRF."IDDCrg"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
FROM DCRG CRF
WHERE
(CRF."Date" = CURRENT_DATE)

UNION ALL

SELECT /*"AVGMonth"*/
CAST(1 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(CM."IDDCrg"),
CAST(0 AS INTEGER)
FROM DCRG CM
WHERE
(CM."Date" < CURRENT_DATE AND CM."Date" > CURRENT_DATE - 31)

UNION ALL

SELECT /*"AVGYear"*/
CAST(1 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(CY."IDDCrg")
FROM DCRG CY
WHERE
(CY."Date" < CURRENT_DATE AND CY."Date" > CURRENT_DATE - 366)



/* Dokymentite po transport*/
UNION ALL

SELECT /*"Active"*/
CAST(2 AS INTEGER),
COUNT(TRA."IDDTrn"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
/* COUNT(T."IDDTrn"),
COUNT(TM."IDDTrn"),
COUNT(TY."IDDTrn")*/
FROM DCRG CRA
LEFT JOIN DTRN TRA ON (TRA."IDDCrg" = CRA."IDDCrg" AND CRA."IsActive" = 'Y')
/*LEFT JOIN DTRN T ON (TRA."IDDCrg" = CRA."IDDCrg" AND T."Date" = CURRENT_DATE)
LEFT JOIN DTRN TM ON (CRA."IsActive" = 'Y' AND TM."Date" < CURRENT_DATE AND TM."Date" > CURRENT_DATE - 31)
LEFT JOIN DTRN TY ON (CRA."IsActive" = 'Y' AND TY."Date" < CURRENT_DATE AND TY."Date" > CURRENT_DATE - 366)*/
WHERE
(CRA."IsActive" = 'Y')

UNION ALL

SELECT /*"Working"*/
CAST(2 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(TRW."IDDTrn"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
FROM DCRG CRW
LEFT JOIN DTRN TRW ON (TRW."IDDCrg" = CRW."IDDCrg")
LEFT JOIN YCRGVALID YV ON (YV."IDDCrg" = CRW."IDDCrg")
WHERE
(YV."IsValid" = 'N')

UNION ALL

SELECT /*"UnActive"*/
CAST(2 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(TRU."IDDTrn"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
FROM DCRG CRU
LEFT JOIN DTRN TRU ON (TRU."IDDCrg" = CRU."IDDCrg")
LEFT JOIN YCRGVALID YV ON (YV."IDDCrg" = CRU."IDDCrg")
WHERE
((CRU."IsActive" = 'N') AND (YV."IsValid" = 'Y'))

UNION ALL

SELECT /*"Finished"*/
CAST(2 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(TRF."IDDTrn"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
FROM DCRG CRF
LEFT JOIN DTRN TRF ON (TRF."IDDCrg" = CRF."IDDCrg")
LEFT JOIN YCRGVALID YV ON (YV."IDDCrg" = CRF."IDDCrg")
WHERE
(CRF."IsFinished" = 'Y')
/*
UNION ALL

SELECT /*"ALLtoday*/
/* CAST(2 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(TRF."IDDTrn"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
FROM DTRN TRF
WHERE
(TRF."Date" = CURRENT_DATE)

*/
/*Dolymenti po korektiv spediciq*/

UNION ALL

SELECT /*"Active"*/
CAST(3 AS INTEGER),
COUNT(CRCHNGA."IDDCrgChng"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(CC."IDDCrgChng"),
COUNT(CCM."IDDCrgChng"),
COUNT(CCY."IDDCrgChng")
FROM DCRGCHNG CRCHNGA
LEFT JOIN DCRGCHNG CC ON (CC."Date" = CURRENT_DATE)
LEFT JOIN DCRGCHNG CCM ON (CCM."Date" < CURRENT_DATE AND CCM."Date" > CURRENT_DATE - 31)
LEFT JOIN DCRGCHNG CCY ON (CCY."Date" < CURRENT_DATE AND CCY."Date" > CURRENT_DATE - 366)
LEFT JOIN DCRG CRA ON (CRA."IDDCrg" = CRCHNGA."IDDCrg")
LEFT JOIN YCRGVALID YV ON (YV."IDDCrg" = CRA."IDDCrg")
WHERE
(CRA."IsActive" = 'Y')

UNION ALL

SELECT /*"Working"*/
CAST(3 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(CRCHNGW."IDDCrgChng"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
FROM DCRGCHNG CRCHNGW
LEFT JOIN DCRG CRW ON (CRW."IDDCrg" = CRCHNGW."IDDCrg")
LEFT JOIN YCRGVALID YV ON (YV."IDDCrg" = CRW."IDDCrg")
WHERE
(YV."IsValid" = 'N')

UNION ALL

SELECT /*"UnActive"*/
CAST(3 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(CRCHNGU."IDDCrgChng"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
FROM DCRGCHNG CRCHNGU
LEFT JOIN DCRG CRU ON (CRU."IDDCrg" = CRCHNGU."IDDCrg")
LEFT JOIN YCRGVALID YV ON (YV."IDDCrg" = CRU."IDDCrg")
WHERE
((CRU."IsActive" = 'N') AND (YV."IsValid" = 'Y'))

UNION ALL

SELECT /*"Finished"*/
CAST(3 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(CRCHNGF."IDDCrgChng"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
FROM DCRGCHNG CRCHNGF
LEFT JOIN DCRG CRF ON (CRF."IDDCrg" = CRCHNGF."IDDCrg")
LEFT JOIN YCRGVALID YV ON (YV."IDDCrg" = CRF."IDDCrg")
WHERE
(CRF."IsFinished" = 'Y')

/*Dokymenti koretiv po transport*/

UNION ALL
SELECT /*"Active"*/
CAST(4 AS INTEGER),
COUNT(TRCA."IDDTrnChng"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(TC."IDDTrnChng"),
COUNT(TCM."IDDTrnChng"),
COUNT(TCY."IDDTrnChng")
FROM DTRNCHNG TRCA
LEFT JOIN DTRNCHNG TC ON (TC."Date" = CURRENT_DATE)
LEFT JOIN DTRNCHNG TCM ON (TCM."Date" < CURRENT_DATE AND TCM."Date" > (CURRENT_DATE - 31))
LEFT JOIN DTRNCHNG TCY ON (TCY."Date" < CURRENT_DATE AND TCY."Date" > (CURRENT_DATE - 366))
LEFT JOIN DTRN TRA ON (TRA."IDDTrn" = TRCA."IDDTrn")
LEFT JOIN DCRG CRA ON (CRA."IDDCrg" = TRA."IDDCrg")
LEFT JOIN YCRGVALID YV ON (YV."IDDCrg" = CRA."IDDCrg")
WHERE
(CRA."IsActive" = 'Y')

UNION ALL

SELECT /*"Working"*/
CAST(4 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(TRCW."IDDTrnChng"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
FROM DTRNCHNG TRCW
LEFT JOIN DTRN TRW ON (TRW."IDDTrn" = TRCW."IDDTrn")
LEFT JOIN DCRG CRW ON (CRW."IDDCrg" = TRW."IDDTrn")
LEFT JOIN YCRGVALID YV ON (YV."IDDCrg" = CRW."IDDCrg")
WHERE
(YV."IsValid" = 'N')

UNION ALL

SELECT /*"UnActive"*/
CAST(4 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(TRCU."IDDTrnChng"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
FROM DTRNCHNG TRCU
LEFT JOIN DTRN TRU ON (TRU."IDDTrn" = TRCU."IDDTrn")
LEFT JOIN DCRG CRU ON (CRU."IDDCrg" = TRU."IDDCrg")
LEFT JOIN YCRGVALID YV ON (YV."IDDCrg" = CRU."IDDCrg")
WHERE
((CRU."IsActive" = 'N') AND (YV."IsValid" = 'Y'))

UNION ALL

SELECT /*"Finished"*/
CAST(4 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
COUNT(TRCF."IDDTrnChng"),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER),
CAST(0 AS INTEGER)
FROM DTRNCHNG TRCF
LEFT JOIN DTRN TRF ON (TRF."IDDTrn" = TRCF."IDDTrn")
LEFT JOIN DCRG CRF ON (CRF."IDDCrg" = TRF."IDDCrg")
LEFT JOIN YCRGVALID YV ON (YV."IDDCrg" = CRF."IDDCrg")
WHERE
(CRF."IsFinished" = 'Y');

Интересен проблем, ако има някои идея, ще се радвам ако я сподели.

Отговор
Отговор в темата