Record Count (Nested??)

Question

I have a SQL select statement which works perfectly except I need to embed a count function (line 7 & 8).

select '1' = DYN_PORDERS.ID, '2' = DYN_PORDERS.SupplierID,'3' = 
DYN_PORDERS.Revision2,  
'4' = DYN_PORDERS.ItemID,  '5' = DYN_PORDERS.OrderNumber, '12' = '',
'6' = SYS_SUPPLIERS.SCompany, '7' = SYS_SUPPLIERS.SCode, '8' = 
SYS_SUPPLIERS.SCIPEMail, '9' = SYS_SUPPLIERS.SCIPContact, 
'10' = SYS_SUPPLIERS.SPDRemitType, '11' = SYS_SUPPLIERS.SCIPFax,
(SELECT count(DYN_PORDERS.OrderNumber) FROM DYN_PORDERS where OrderNumber = 
'5') AS column3
from DYN_PORDERS 
inner join  SYS_SUPPLIERS 
on SYS_SUPPLIERS.ID = DYN_PORDERS.SupplierID 
where DYN_PORDERS.OrderStatus1 = 1 and DYN_PORDERS.ItemID = 1
order by DYN_PORDERS.OrderNumber asc, DYN_PORDERS.itemid asc

The nested select count statement returns 0 but we have four records in the table.

I have tried to reference the Variable or Column Name '5' from the first select statement in the embedded one, but it returns 0.

Any help would be much appreciated. Mike

Answer 1

If you want to access ordernumber in the subquery, then use this name. Use table aliases to tell the outer table from the inner one.

select 
  p.id as "1", 
  p.supplierid as "2",
  p.revision2 as "3",  
  p.itemid as "4",  
  p.ordernumber as "5", 
  '' as "12",
  s.scompany as "6", 
  s.scode as "7",
  s.scipemail as "8", 
  s.scipcontact as "9", 
  s.spdremittype as "10", 
  s.scipfax as "11",
  (select count(*) from dyn_porders p2 where p2.ordernumber = p.ordernumber) as column3
from dyn_porders p
inner join sys_suppliers s on s.id = p.supplierid 
where p.orderstatus1 = 1 
  and p.itemid = 1
order by p.ordernumber asc, p.itemid asc;

Please note that single quotes are for string literals and double quotes for names.