Solved

SQL Syntax Help Needed

Posted on 2013-02-03
4
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 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