Solved

SQL Syntax Help Needed

Posted on 2013-02-03
4
356 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

623 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