Solved

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)

Posted on 2016-10-08
6
34 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
0
Comment
Question by:John Ellis
[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
6 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41835466
I think before using this query you need to clean it up first. Because if you work with this format you will not understand what is going, where to look. :)

My first rule - Code should be clean even if it is not working. Check out yourself , Easy to read and understand.

Pls Try below and let me know in case any issues.

--


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 
			  k.CUSTNMBR [CustomerID]
			, b.CUSTNAME [CustomerName]
			, x.APFRDCNM [DocumentNumber]
			, 0 Cnt			
			, CASE 
				WHEN x.APFRDCTY > 6  
				AND x.DATE1 = @AGE 
				AND x.APFRDCDT = @AGE
				AND x.APTODCDT < @AGE
				AND x.APFRDCNM not in (SELECT RM20101.DOCNUMBR FROM RM20101 p WHERE p.CUSTNMBR = x.CUSTNMBR)
				AND x.APFRDCTY > 6  
				AND x.DATE1 = @AGE 
				AND x.APFRDCDT = @AGE)
				THEN 
					x.APPTOAMT * -1
				ELSE 0 
				END [OPEN A/R]

FROM 
	  RM20201 x
INNER JOIN RM20101 k ON x.CUSTNMBR = k.CUSTNMBR AND x.APFRDCNM = k.DOCNUMBR
INNER JOIN RM00101 b on k.CUSTNMBR = b.CUSTNMBR
LEFT OUTER JOIN CN00500 cn ON k.CUSTNMBR = cn.CUSTNMBR

WHERE 
	    k.VOIDSTTS = 0
		AND k.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 
		
		k.CUSTNMBR, k.DOCNUMBR, k.DOCDATE, k.RMDTYPAL, k.ORTRXAMT, k.CURTRXAM
		,x.DATE1, k.DUEDATE, x.APTODCDT
		,b.CUSTNAME, b.PYMTRMID, cn.CRDTMGR, b.COMMENT1, b.COMMENT2
		,x.APFRDCTY, x.APFRDCDT, x.APPTOAMT, x.CUSTNMBR, x.APFRDCNM, x.APTODCTY, x.APFRMAPLYAMT, x.APTODCNM

HAVING 
		x.APFRDCTY > 6  
		AND x.DATE1 = @AGE 
		AND x.APFRDCDT = @AGE
		AND x.APTODCDT < @AGE
		AND x.APFRDCNM NOT IN (SELECT y.DOCNUMBR FROM RM20101 y WHERE y.CUSTNMBR = x.CUSTNMBR)
		

--

Open in new window


--
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41835469
For starters, part of the confusion here is that your T-SQL is pretty butt-ugly.  

To make it MUCH easier to read, please take your T-SQL block above and do this:
  • Use table aliases.  Better to see fewer characters like r, r2, r1 a bunch of times then RM20201.
  • Indent, which means use the tab character on lines other than the main clauses of your query:  SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING.  Indent your CASE blocks as well to make them easier to read.
  • Remove all unnecessary parentheses marks ( ) and square brackets [ ].
  • INNER JOIN can be written as JOIN, LEFT OUTER JOIN can be written as LEFT JOIN.
  • Declare and SET can be performed on the same line as long as it's a straight assignment.
  • Be consistent in using upper and lower case in keywords such as AND, IN, NOT IN, OR.
  • Since variables @AGE and @RUN both have no time component, should date be used instead of datetime?

Please tell us if you're using some kind of GUI query editing application, or typing in SSMS.

Let's consider this as a starting point.  Please copy-paste the below into your SSMS, and let me know if it's easier to read:
Declare @AGE datetime= '2015-09-30 00:00:00.000'
Declare @RUN datetime= '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
				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
	JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APFRDCNM = RM20101.DOCNUMBR
	JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
	LEFT 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
			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

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41835485
You query contains this condition twice
1. in a case expression, and
2 in the where clause

                        AND RM20101.DOCNUMBR = RM20201.APFRDCNM

Can you try REMOVING the highlighted condition from your query?
Does that help?
0
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 

Accepted Solution

by:
John Ellis earned 0 total points
ID: 41836176
Hello:

I figured this out.  Below is my revised code block.  

Instead of focusing on the RM20201.APFRDCNM, I needed to focus on the RM20201.APTODCNM.

By doing so, I was able to pull the amount that I needed, anyway.

Thank you, to all those who chimed in!

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.APTODCNM in (SELECT RM20101.DOCNUMBR FROM RM20101 INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APTODCNM
where RM20101.CUSTNMBR = RM20201.CUSTNMBR
and
RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE
AND RM20101.ORTRXAMT = RM20201.APPTOAMT)
THEN RM20201.APPTOAMT * -1
ELSE 0 END
as [OPEN A/R]
from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APTODCNM = 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.APTODCNM in (SELECT RM20101.DOCNUMBR FROM RM20101 INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APTODCNM
where RM20101.CUSTNMBR = RM20201.CUSTNMBR
and
RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE
AND RM20101.ORTRXAMT = RM20201.APPTOAMT)
THEN RM20201.APPTOAMT * -1
ELSE 0 END
<> 0

Open in new window

0
 

Author Closing Comment

by:John Ellis
ID: 41843345
I figured this out, on my own, by turning my focus to a different field and ultimately deriving the record that I required.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41843356
Great !!
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 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