CodeIgniter nested query issue

Question

I have tried to convert the code below to codeigniter active record without any success. I searched through here for any related question but the one I found here codeigniter active record nested query is not working for me.

Below is the sql am trying to convert

SELECT "BCB_ID","BCB_COST_CENTRE_NUMBER","BCB_TRANSFER_DATE","BCB_ARRIVAL_SEQUENCE","BCB_LOCATION_TYPE", "BCB_REFERENCE",
SUM(cfb) as CFB, SUM(ctb) as CTB, SUM(cts) as CTS, SUM(cfs) as CFS 
FROM(SELECT "BCB_ID","BCB_COST_CENTRE_NUMBER", "BCB_DATE", "BCB_ARRIVAL_SEQUENCE","BCB_LOCATION_TYPE", "BCB_TRANSFER_DATE", "BCB_REFERENCE", "BCB_SOURCE", 
    SUM(BCB_CASH_FROM_BANK )as CFB, SUM(BCB_CASH_TO_BANK ) as CTB, SUM(BCB_CASH_TO_SHOPS )as CTS, SUM(BCB_CASH_FROM_SHOPS ) as CFS
    FROM "WHOUSE1"."DLY_BWR_CASH_AND_BANKING"
    WHERE "BCB_COST_CENTRE_NUMBER" =  '1222'
    AND "BCB_TRANSFER_DATE" >= 20130130
    and bcb_source in ('A','C')
    GROUP BY "BCB_ID","BCB_COST_CENTRE_NUMBER", "BCB_DATE", "BCB_ARRIVAL_SEQUENCE", "BCB_LOCATION_TYPE", "BCB_TRANSFER_DATE", "BCB_REFERENCE", "BCB_SOURCE"
    ORDER BY "BCB_TRANSFER_DATE" DESC
    )
GROUP BY  "BCB_ID", "BCB_COST_CENTRE_NUMBER",  "BCB_TRANSFER_DATE", "BCB_ARRIVAL_SEQUENCE", "BCB_LOCATION_TYPE", "BCB_REFERENCE"
ORDER BY  "BCB_TRANSFER_DATE" DESC

Any help will be well appreciated. Thanks

hi Ben <stackoverflow.com/users/458741/ben> your help will be appreciated. Thank you — Rebe24

Denmark Denmark · Accepted Answer · 2013-11-22T06:26:16

$this->db->query("SELECT "BCB_ID","BCB_COST_CENTRE_NUMBER","BCB_TRANSFER_DATE","BCB_ARRIVAL_SEQUENCE","BCB_LOCATION_TYPE", "BCB_REFERENCE",
SUM(cfb) as CFB, SUM(ctb) as CTB, SUM(cts) as CTS, SUM(cfs) as CFS 
FROM(SELECT "BCB_ID","BCB_COST_CENTRE_NUMBER", "BCB_DATE", "BCB_ARRIVAL_SEQUENCE","BCB_LOCATION_TYPE", "BCB_TRANSFER_DATE", "BCB_REFERENCE", "BCB_SOURCE", 
    SUM(BCB_CASH_FROM_BANK )as CFB, SUM(BCB_CASH_TO_BANK ) as CTB, SUM(BCB_CASH_TO_SHOPS )as CTS, SUM(BCB_CASH_FROM_SHOPS ) as CFS
    FROM "WHOUSE1"."DLY_BWR_CASH_AND_BANKING"
    WHERE "BCB_COST_CENTRE_NUMBER" =  '1222'
    AND "BCB_TRANSFER_DATE" >= 20130130
    and bcb_source in ('A','C')
    GROUP BY "BCB_ID","BCB_COST_CENTRE_NUMBER", "BCB_DATE", "BCB_ARRIVAL_SEQUENCE", "BCB_LOCATION_TYPE", "BCB_TRANSFER_DATE", "BCB_REFERENCE", "BCB_SOURCE"
    ORDER BY "BCB_TRANSFER_DATE" DESC
    )
GROUP BY  "BCB_ID", "BCB_COST_CENTRE_NUMBER",  "BCB_TRANSFER_DATE", "BCB_ARRIVAL_SEQUENCE", "BCB_LOCATION_TYPE", "BCB_REFERENCE"
ORDER BY  "BCB_TRANSFER_DATE" DESC");////TRY THIS CODE

---AND THIS LINK FOR GUIDE http://ellislab.com/codeigniter/user-guide/database/results.html

CodeIgniter nested query issue

1 Answers