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
28 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
6 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41843356
Great !!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach 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.
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now