Solved

SQL Syntax Help Needed

Posted on 2013-02-03
4
354 Views
Last Modified: 2013-02-03
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
Comment
Question by:rwheeler23
  • 2
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
plummet earned 500 total points
ID: 38848939
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
 

Author Comment

by:rwheeler23
ID: 38848963
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
 

Author Closing Comment

by:rwheeler23
ID: 38848965
Perfect. Thanks.
0
 
LVL 10

Expert Comment

by:plummet
ID: 38849015
No problem, sometimes when you're doing more complex stuff other things elude us!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question