Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Syntax Help Needed

Posted on 2013-02-03
4
Medium Priority
?
357 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 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

705 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