Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

SQL Syntax Help Needed

I have this script which does return the correct records, however it is not bringing forward these values: VCHRNMBR,DOCTYPE,DOCDADTE,POSTDATE,DINVPDOF,VOIDPDATE,VOIDSTTS,DOCNUMBR

These fields are in this select statement embedded in the query.

(select t1.VCHRNMBR,t1.DOCTYPE,t1.date1 AS DOCDATE,t1.glpostdt AS POSTDATE,t2.DINVPDOF,t2.VOIDPDATE,t2.VOIDED as VOIDSTTS,T3.DOCNUMBR

What am I missing here and how do I bring these values forward?

-----------------------------------------------------------------------------------------------------------------------
Script:
SELECT (case PATINDEX ('%CMP%',jl.jobnumber) when 0 then 'DYN' else 'CMP' end) as COMPANYID,
       rtrim(jl.jobnumber) as jobnumber,jl.transnmbr,jl.mastertype,jl.masterid,pm.vendname as mastername,jl.debitamt,jl.crdtamnt,      
         left(jl.DISTREF,31) as TRXDSCRN,jl.docsource,jl.actnumst,
       case when gl.ACCATNUM = 31 then case when coalesce(VOIDSTTS,0) = 0 then jl.debitamt - jl.crdtamnt else jl.crdtamnt - jl.debitamt end else 0 end as laboramt,
         case when gl.ACCATNUM = 32 then case when coalesce(VOIDSTTS,0) = 0 then jl.debitamt - jl.crdtamnt else jl.crdtamnt - jl.debitamt end else 0 end as Matlamnt,
       case when gl.ACCATNUM = 33 then case when coalesce(VOIDSTTS,0) = 0 then jl.debitamt - jl.crdtamnt else jl.crdtamnt - jl.debitamt end else 0 end as delivamt,
         case when gl.ACCATNUM = 34 then case when coalesce(VOIDSTTS,0) = 0 then jl.debitamt - jl.crdtamnt else jl.crdtamnt - jl.debitamt end else 0 end as Outsvcamt,
       case when gl.ACCATNUM = 35 then case when coalesce(VOIDSTTS,0) = 0 then jl.debitamt - jl.crdtamnt else jl.crdtamnt - jl.debitamt end else 0 end as Miscamnt,
       case when gl.ACCATNUM = 36 then case when coalesce(VOIDSTTS,0) = 0 then jl.debitamt - jl.crdtamnt else jl.crdtamnt - jl.debitamt end else 0 end as ciasamnt        
      FROM [GSE]..[gl00100] gl, [GSE]..[pm00200] pm, [GSE]..[job_linker] as jl
    inner join (
      SELECT distinct rtrim(PACONTNUMBER) as pacontnumber
      FROM [GSE]..[PA01101]
               ) as P
            on jl.jobnumber = p.PACONTNUMBER
      JOIN (select t1.VCHRNMBR,t1.DOCTYPE,t1.date1 AS DOCDATE,t1.glpostdt AS POSTDATE,t2.DINVPDOF,t2.VOIDPDATE,t2.VOIDED as VOIDSTTS,T3.DOCNUMBR
            from (Select * from PM30300) t1 -- THESE VALUES ARE MISSING
            JOIN (SELECT vendorid,vchrnmbr,doctype,docdate,pstgdate,DOCNUMBR,DINVPDOF,VOIDPDATE,VOIDED
                  FROM PM30200 WHERE DOCTYPE IN (1,2,3)) t2
                  ON T1.VENDORID=T2.VENDORID AND t1.APTVCHNM=t2.VCHRNMBR AND t1.APTODCTY=t2.DOCTYPE
            JOIN (SELECT vendorid,vchrnmbr,doctype,docdate,pstgdate,DOCNUMBR
                  FROM PM30200 WHERE DOCTYPE = 6) t3
                  ON t1.VENDORID=T3.VENDORID AND t1.VCHRNMBR=t3.VCHRNMBR AND t1.DOCTYPE=t3.DOCTYPE
            WHERE t1.DOCTYPE=6) t4
            ON jl.transnmbr = t4.vchrnmbr and jl.docnumbr = t4.docnumbr
            where POSTDATE >= '03/01/2011' and jl.mastertype = 'PAD' and len(rtrim(jl.jobnumber))>0
      and jl.masterid = pm.vendorid and gl.actnumbr_1=jl.actnumst
      and gl.accatnum>=31 and jl.docsource<>'Access_Import'
0
rwheeler23
Asked:
rwheeler23
  • 2
  • 2
1 Solution
 
plummetCommented:
Hi

You need to add them to your select, ie something like this:

SELECT (case PATINDEX ('%CMP%',jl.jobnumber) when 0 then 'DYN' else 'CMP' end) as COMPANYID,
       rtrim(jl.jobnumber) as jobnumber,jl.transnmbr,jl.mastertype,jl.masterid,pm.vendname as mastername,jl.debitamt,jl.crdtamnt,      
         left(jl.DISTREF,31) as TRXDSCRN,jl.docsource,jl.actnumst,
       case when gl.ACCATNUM = 31 then case when coalesce(VOIDSTTS,0) = 0 then jl.debitamt - jl.crdtamnt else jl.crdtamnt - jl.debitamt end else 0 end as laboramt,
         case when gl.ACCATNUM = 32 then case when coalesce(VOIDSTTS,0) = 0 then jl.debitamt - jl.crdtamnt else jl.crdtamnt - jl.debitamt end else 0 end as Matlamnt,
       case when gl.ACCATNUM = 33 then case when coalesce(VOIDSTTS,0) = 0 then jl.debitamt - jl.crdtamnt else jl.crdtamnt - jl.debitamt end else 0 end as delivamt,
         case when gl.ACCATNUM = 34 then case when coalesce(VOIDSTTS,0) = 0 then jl.debitamt - jl.crdtamnt else jl.crdtamnt - jl.debitamt end else 0 end as Outsvcamt,
       case when gl.ACCATNUM = 35 then case when coalesce(VOIDSTTS,0) = 0 then jl.debitamt - jl.crdtamnt else jl.crdtamnt - jl.debitamt end else 0 end as Miscamnt, 
       case when gl.ACCATNUM = 36 then case when coalesce(VOIDSTTS,0) = 0 then jl.debitamt - jl.crdtamnt else jl.crdtamnt - jl.debitamt end else 0 end as ciasamnt,
t4.VCHRNMBR,
t4.DOCTYPE,
t4.DOCDATE,
t4.POSTDATE,
t4.DINVPDOF,
t4.VOIDPDATE,
t4.VOIDSTTS,
t4.DOCNUMBR
from ...

Open in new window

That should fix it - you have them in the subquery but not in the main one.

Hope it helps!
John
0
 
rwheeler23Author Commented:
I am such a maroon! Thanks for pointing this out. I was off rearraning the query with subqueries. The KISS principle definitely applied here.
0
 
rwheeler23Author Commented:
Perfect. Thanks.
0
 
plummetCommented:
No problem, sometimes when you're doing more complex stuff other things elude us!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now