nextaxtion
Yak Posting Veteran
54 Posts |
Posted - 2013-11-08 : 07:07:47
|
i have a query that accept some parameters and i need to check that if any row contain more op_amt greater than 0 in record set then all rows should be seleced otherwise if all rows of op_amt column are 0 then need not to show any record . Kindly help select * from (SELECT BR_ID, BR_CODE, BR_NAME, FA_ID, FA_CODE, FA_NAME, NULL AS GENERAL_LEDGER_ID, NULL AS GL_NAME, NULL GL_CODE, DEBIT - CREDIT AS OP_AMT,'A - OPENING' OPENING FROM ( SELECT BR_ID,BR_CODE, BR_NAME, FA_ID,FA_CODE,FA_NAME, DEBIT_OPENING+DEBIT AS DEBIT,CREDIT_OPENING+CREDIT AS CREDIT FROM ( SELECT VW_FA.BR_ID,VW_FA.BR_CODE, VW_FA.BR_NAME, VW_FA.FA_ID,VW_FA.FA_CODE,VW_FA.FA_NAME, SUM(DEBIT_OPENING) DEBIT_OPENING, SUM(CREDIT_OPENING) CREDIT_OPENING, SUM(trndr) DEBIT, SUM(trnCr) CREDIT FROM VW_FA_BR VW_FA LEFT JOIN (SELECT O.BRANCH_ID, O.GL_ID GENERAL_LEDGER_ID ,FA_ID FUNDING_AGENCY_ID , IFNULL(SUM(CASE WHEN O.OP_BAL_TYPE='D' THEN O.OP_DR_BAL END),0)AS DEBIT_OPENING, IFNULL(SUM(CASE WHEN O.OP_BAL_TYPE='C' THEN O.OP_CR_BAL END),0)AS CREDIT_OPENING, 0 trndr, 0 trncr FROM QM_BALANCE O WHERE O.BRANCH_ID IN (@gx_BRANCH_ID) AND O.FA_ID IN (@gx_P_FA_ID) AND FY_ID =(SELECT F.ID FROM QM_FINANCIAL_YEAR F WHERE (@gx_P_FROM_DATE) BETWEEN F.FY_START_DATE AND F.FY_END_DATE AND BRANCH_ID = (@gx_BRANCH_ID) ) AND BRANCH_ID = (@gx_BRANCH_ID) AND FA_ID in (@gx_P_FA_ID) GROUP BY BRANCH_ID , FA_ID UNION ALL SELECT T.BRANCH_ID, T.GL_ID GENERAL_LEDGER_ID ,FA_ID FUNDING_AGENCY_ID , SUM(IFNULL(T.TTL_TRN_DR_AMT ,0))AS DEBIT_OPENING, SUM(IFNULL(T.TTL_TRN_CR_AMT,0))AS CREDIT_OPENING, 0 trndr, 0 trncr FROM QM_BALANCE T WHERE BRANCH_ID IN (@gx_BRANCH_ID) AND FA_ID in (@gx_P_FA_ID) AND FY_ID=(SELECT ID FROM QM_FINANCIAL_YEAR WHERE BRANCH_ID IN (@gx_BRANCH_ID) AND @gx_P_FROM_DATE >= FY_START_DATE AND @gx_P_FROM_DATE <= FY_END_DATE) AND START_DATE >= (SELECT FY_START_DATE FROM QM_FINANCIAL_YEAR WHERE BRANCH_ID IN (@gx_BRANCH_ID) AND @gx_P_FROM_DATE >= FY_START_DATE AND @gx_P_FROM_DATE <= FY_END_DATE ) AND END_DATE <=CONCAT(LAST_DAY(DATE_FORMAT(@gx_P_FROM_DATE,'%Y-%m-%d') - INTERVAL '1' MONTH)) GROUP BY T.BRANCH_ID,T.FA_ID ) A prithvi nath pandey |
|