We help IT Professionals succeed at work.
Get Started

SQL Query Syntax: How to Tell SQL to Pull a Record (Once again, this should be easy; but, the app wants to make it hard)

106 Views
Last Modified: 2016-10-14
Hello:

I must have upset someone in a previous life, because this current life is a real purgatory.

Once again, SQL query syntax which should be very easy to code is not working.

Please see the first code block below.

All that I'm flippin' trying to do is pull in a record where the RM20201.APFRMDCNM is not in the RM20101.DOCNUMBR field for the CUSTNMBR field of both the RM20101 and RM20201 tables.  True, as you can see from the embedded screenshot below, the RM20201.APTODCNM field is indeed in the RM20101.DOCNUMBR field, for the CUSTNMBR of either table.  

But, trust me.  In RM20201 table, the APFRMDCNM field is not in the RM20101.DOCNUMBR field for this CUSTNMBR (i.e. this customer).

By the way, the second code block below is what generates what is seen in the screenshot.  RM20101 represents the table of the first record in the screenshot, while RM20201 is the second record.

Again, I simply want to pull the record for the RM20201.APFRMDCNM field where it does not exist in the RM20101.DOCNUMBR field for a CUSTNMBR of both tables.  How do I update my first code block to do so?

Thank you, so much!

John

DECLARE @AGE DATETIME;
DECLARE @RUN DATETIME;
SET @AGE = '2015-09-30 00:00:00.000';
SET @RUN = '2016-07-31 00:00:00.000';

select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20201.APFRDCNM as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE
AND RM20201.APTODCDT < @AGE
AND RM20201.APFRDCNM not in (SELECT RM20101.DOCNUMBR FROM RM20101 INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM
where RM20101.CUSTNMBR = RM20201.CUSTNMBR
and
RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE)
THEN RM20201.APPTOAMT * -1
ELSE 0 END
as [OPEN A/R]
from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APFRDCNM = RM20101.DOCNUMBR
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))     
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20101.RMDTYPAL, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20201.DATE1, RM20101.DUEDATE, RM20201.APTODCDT, 
RM20201.APFRDCTY, RM20201.APFRDCDT, RM20201.APPTOAMT, RM20201.CUSTNMBR, RM20201.APFRDCNM, RM20201.APTODCTY,
RM20201.APFRMAPLYAMT, RM20201.APTODCNM
HAVING 
CASE WHEN RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE
AND RM20201.APTODCDT < @AGE
AND RM20201.APFRDCNM not in (SELECT RM20101.DOCNUMBR FROM RM20101 INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM
where RM20101.CUSTNMBR = RM20201.CUSTNMBR
and
RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE)
THEN RM20201.APPTOAMT * -1
ELSE 0 END
<> 0 

Open in new window


select * from RM20101 where CUSTNMBR  = '179520' and ORTRXAMT = 180

select * from RM20201 where CUSTNMBR = '179520' and APTODCNM = 'G02010305'

Open in new window


another capture
Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE