troubleshooting Question

SubQuery in View in SQL Server

Avatar of rustypoot
rustypootFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
6 Comments1 Solution1129 ViewsLast Modified:
Hi! I have created a View in SQL server. I need to add a sub query to it to pull just 1 field with different criteria than the view criteria; how can I do this?

Below is the Code for View that pulls all the fields. Now, I need to add the code below as subquery:
SELECT  COUNT(t.effectivedate) as Insertion_Counter
from ocr_invoice t
WHERE t.transtype = 'Invoice' AND t.linetype = 'Ad' AND t.glacctclass = 'Assets'
This sibquery should just return 1 value for Insertion Counter from the view.

Please help. Thanks

create or replace view rpt_monthend_interactive as
select
       i.transdate as calendardate,
       i.effectivedate as runday,
       i.transnum as transnumber,
       i.amount as amt,
       c.acct_name as ADVERTISER,
       c.accountnumber as acctnum,
       i.glacct as glnumber,
       i.glacctdesc as glname,
       i.glacctclass,
       a.productid,
       ac.name as adjcause,
       ac.appliestocommission as Commission_Flag,
       ar.name as adjreason,
       j.username as salesrep,
--       o.seller as salesrep,
       j.salesteam as salesteam,
       o.ordernumber as ordernum,
       nvl(a.product, p.product)  as productname,
       a.productalias,
       a.category as productcategory,
       decode(nvl(s.AdSubTypeId, p.adsubtypeid), 1, 'National', 2, 'National', 4, 'National', 'Retail') as      adsubtype,
       a.placement as placementname,
       s.position,
       a.adcategory,
       decode(t.goalid, 0, 'Account Level Adj', t.name) as goalcategory,
       a.edition,
       c.acct_type as customertype_client,
       decode(i.transtype, 'Invoice', 'INVOICEAMT', 'Adjustment Invoice', 'INVOICEAMT', 'ADJUSTMENT') as entrytype,
       i.linetype,
       i.transtype
      -- o.totalinsertions as ADINSERT
from ocr_invoice i
     join pmt.pmt_customer c ON c.accountid = i.advertisorid
     AND c.acct_type IN ('Commercial', 'Small Business', 'Private Party')
     JOIN pmt.ocr_date d ON i.t_dateid = d.id
         -- AND d.calendardate between   trunc(add_months(sysdate,-1), 'month') and last_day(trunc(add_months(--sysdate,-1), 'month'))
     join pmt.adj_causes ac on i.cdcauseid = ac.id
     join pmt.adj_reasons ar on i.cdreasonid = ar.id
     join newjobuser j on i.jobid = j.jobid
     join goal_category t on t.goalid = i.goalid
     left join pmt.pmt_order o  on o.adorderid = i.adorderid
     left join pmt.pmt_adschedule s on i.adrunschedid = s.id
     left join pmt.pmt_prpschedule p on i.prprunschedid = p.id
     LEFT JOIN pmt.adproducts a on a.productid = decode(i.adrunschedid, 0, nvl(p.productid, 0), nvl(s.productid,0))
               and a.placementid = decode(i.adrunschedid, 0, nvl(p.placementid, 0), nvl(s.placementid, 0))
               and a.editionid = decode(i.adrunschedid, 0, nvl(p.editionid, 0), nvl(s.editionid, 0))
WHERE i.transdate between   trunc(add_months(sysdate,-1), 'month') and last_day(trunc(add_months(sysdate,-1), 'month'))
    and i.glacctclass = 'Revenue'
   -- AND ac.appliestocommission = 1
    and a.adcategory = 'Interactive'
   -- and i.transtype IN ('Invoice', 'Adjustment Invoice', 'Special', 'Debit')
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros