Skip to content

Embedded Select Statement

 

SQL is a very flexible language which allows you different ways of extracting data. The key is to start small and systematically build your query up. Have a look at the following example...

If you want to create a report grouped by say Cost Centres, always start with a basic Cost Centre select - this example will select all Production Cost Centres - always use Aliases, ‘C’ for CCENTRE in this example :

select 
C.CCENTREID,
C.NAME
from CCENTRE C
where C.ISPRODUCTION = 'Yes'
order by C.NAME

Now you can add any embedded select statement inside this statement - in the example below I’ve inserted a sub-select that will select all the Costs for each Cost Centre:

select 
C.CCENTREID,
C.NAME,

(select sum(SC.TOTALCOST) 
from SOCOSTS SC
where SC.CCID = C.CCENTREID
and SC.INTEXT = 'Internal') as COST

from CCENTRE C
where C.ISPRODUCTION = 'Yes'
order by C.NAME

Let’s take it a step further and set a Date Parameter for the Cost using the :DATESTART and :DATEEND parameters which will enable the Date filter controls - the Date sits in the SOMAIN (Sales Order Header) table so we have to join this table:

select 
C.CCENTREID,
C.NAME,

(select sum(SC.TOTALCOST) 
from SOCOSTS SC 
left join SOMAIN S on SC.SOID = S.SOID
where SC.CCID = C.CCENTREID
and S.INVDATE between :DATESTART and :DATEEND
and SC.INTEXT = 'Internal') as COST

from CCENTRE C
where C.ISPRODUCTION = 'Yes'
order by C.NAME

Next we can filter out Cancelled Sales Orders:

select 
C.CCENTREID,
C.NAME,

(select sum(SC.TOTALCOST) 
from SOCOSTS SC 
left join SOMAIN S on SC.SOID = S.SOID
where SC.CCID = C.CCENTREID
and S.INVDATE between :DATESTART and :DATEEND
and S.STATUS <> 'Cancelled'
and SC.INTEXT = 'Internal') as COST

from CCENTRE C
where C.ISPRODUCTION = 'Yes'
order by C.NAME

Once the Sub-select is working to your satisfaction, you can easily copy it to say select the Estimated Time for each Cost Centre using the same Date parameters, etc:

select 
C.CCENTREID,
C.NAME,

(select sum(SC.TOTALCOST) 
from SOCOSTS SC 
left join SOMAIN S on SC.SOID = S.SOID
where SC.CCID = C.CCENTREID
and S.INVDATE between :DATESTART and :DATEEND
and S.STATUS <> 'Cancelled'
and SC.INTEXT = 'Internal') as COST,

(select sum(SC.ESTTIME) 
from SOCOSTS SC 
left join SOMAIN S on SC.SOID = S.SOID
where SC.CCID = C.CCENTREID
and S.INVDATE between :DATESTART and :DATEEND
and S.STATUS <> 'Cancelled'
and SC.INTEXT = 'Internal') as TIME

from CCENTRE C
where C.ISPRODUCTION = 'Yes'
order by C.NAME

From here it’s a simple procedure to add different Date parameters to select say the Cost from 12 months ago:

select 
C.CCENTREID,
C.NAME,

(select sum(SC.TOTALCOST) 
from SOCOSTS SC 
left join SOMAIN S on SC.SOID = S.SOID
where SC.CCID = C.CCENTREID
and S.INVDATE between :DATESTART1 and :DATEEND1
and S.STATUS <> 'Cancelled'
and SC.INTEXT = 'Internal') as COST_THISMONTH,

(select sum(SC.TOTALCOST) 
from SOCOSTS SC 
left join SOMAIN S on SC.SOID = S.SOID
where SC.CCID = C.CCENTREID
and S.INVDATE between :DATESTART13 and :DATEEND13
and S.STATUS <> 'Cancelled'
and SC.INTEXT = 'Internal') as COST_THISMONTHLASTYEAR,

(select sum(SC.ESTTIME) 
from SOCOSTS SC 
left join SOMAIN S on SC.SOID = S.SOID
where SC.CCID = C.CCENTREID
and S.INVDATE between :DATESTART and :DATEEND
and S.STATUS <> 'Cancelled'
and SC.INTEXT = 'Internal') as TIME

from CCENTRE C
where C.ISPRODUCTION = 'Yes'
order by C.NAME