Solved

T-SQL: Why is Syntax So Difficult

Posted on 2016-10-05
5
33 Views
Last Modified: 2016-10-05
For disabled people like me, I really wish that they would make T-SQL syntax easier.

I have tried a billion ways to Sunday to get the syntax for my CTE to be correct.  But, T-SQL is being completely and utterly unreasonable.  

I didn't pay for college much less work hard and graduate from it, just so I could spend two hours trying to figure out how and in which spot to place a parentheses and a comma, in order to get the data that I need.

Below is my code, followed by its results.  I purposely hid the CustomerID and CustomerName columns, as I am not allowed to give out this information.

Anyway, do you see the RowFromCTE field at the very end of the code?  I simply want to say "where RowFromCTE > 1".  That's all!  I can't believe that it is taking me two hours to figure that out!

Please tell me how to do this.

I honestly don't believe it!  Telling T-SQL to give you "where" is not and should not be hard.  I don't get it.

Thank you!

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';

with cte3 as
(
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
RM20201.APTODCNM as [ApplyToDocNo], RM20201.APPTOAMT as [AppliedAmt], RM20101.ORTRXAMT AS [OriginalAmt],
COUNT(*) OVER (PARTITION BY RM20101.CUSTNMBR, RM20201.APFRDCNM) AS Cnt,
SUM(RM20201.APPTOAMT) OVER (PARTITION BY RM20201.CUSTNMBR, RM20201.APFRDCNM) as [CustomerPayment],
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE <= @AGE and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT > @AGE and RM20201.APFRDCDT <= @AGE 
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM
GROUP BY RM20201.CUSTNMBR, RM20201.APFRDCNM, RM20101.ORTRXAMT
HAVING COUNT(RM20201.APFRDCNM) > 1)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
THEN RM20201.APPTOAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.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'))  
GROUP BY RM20101.CUSTNMBR, RM20201.CUSTNMBR, RM20101.RMDTYPAL, RM20101.DOCDATE, RM20201.DATE1, RM20201.APTODCDT, RM20201.APFRDCDT,
RM20101.DOCNUMBR, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20101.DUEDATE, RM20201.APFRMAPLYAMT, RM20201.APPTOAMT,
RM00101.CUSTNAME, RM20201.APTODCNM, RM20201.APFRDCNM
HAVING
CASE WHEN RM20101.RMDTYPAL > 6 and RM20101.DOCDATE <= @AGE and RM20201.DATE1 > @AGE 
and RM20201.APTODCDT > @AGE and RM20201.APFRDCDT <= @AGE 
AND RM20101.DOCNUMBR in (select APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM
GROUP BY RM20201.CUSTNMBR, RM20201.APFRDCNM, RM20101.ORTRXAMT
HAVING COUNT(RM20201.APFRDCNM) > 1)
and RM20101.ORTRXAMT <> RM20101.CURTRXAM AND RM20101.DUEDATE <> ''
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
THEN RM20201.APPTOAMT * -1 ELSE 0 END
<> 0)
select cte3.[CustomerID], cte3.[CustomerName], cte3.[DocumentNumber], cte3.[ApplyToDocNo], cte3.Cnt, cte3.[OPEN A/R],
ROW_NUMBER () OVER(PARTITION BY cte3.[CustomerID], cte3.[ApplyToDocNo] ORDER BY cte3.[OPEN A/R] ASC) as RowFromCTE
from cte3
GROUP BY cte3.[CustomerID], cte3.[CustomerName], cte3.[CustomerPayment], cte3.[OriginalAmt], cte3.[DocumentNumber], 
cte3.[Cnt], 
cte3.[OPEN A/R], cte3.[ApplyToDocNo]
HAVING cte3.CustomerPayment = cte3.OriginalAmt 

Open in new window


I need a refund on my college tuition obviously
0
Comment
Question by:John Ellis
  • 3
  • 2
5 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41830887
:-)
please consider yourself a member of the world-wide club of syntax frustration

there are 2 points of syntax you have "hit"


1. You cannot use a column alias created in the select clause in the where clause
     of the same query, you need another cte or "derived table" to do that

this will NOT work:
select column1 as my_column_alias
from a_table
where my_column_alias = 'x'

This would work:
select my_column_alias
from (
      select column1 as my_column_alias
      from a_table
          ) as derived
where my_column_alias = 'x'

2. a where clause occurs immediately after the from clause

I'm coming back, have to leave my desk .... {+edit}

I think the following will work, look at lines 93 onward:
DECLARE @AGE datetime;
DECLARE @RUN datetime;
SET @AGE = '2015-09-30 00:00:00.000';
SET @RUN = '2016-07-31 00:00:00.000';

WITH cte3
AS (SELECT
                RM20101.CUSTNMBR                                                             AS [CustomerID]
              , RM00101.CUSTNAME                                                             AS [CustomerName]
              , RM20101.DOCNUMBR                                                             AS [DocumentNumber]
              , RM20201.APTODCNM                                                             AS [ApplyToDocNo]
              , RM20201.APPTOAMT                                                             AS [AppliedAmt]
              , RM20101.ORTRXAMT                                                             AS [OriginalAmt]
              , COUNT(*) OVER (PARTITION BY RM20101.CUSTNMBR, RM20201.APFRDCNM)              AS Cnt
              , SUM(RM20201.APPTOAMT) OVER (PARTITION BY RM20201.CUSTNMBR, RM20201.APFRDCNM) AS [CustomerPayment]
              , CASE
                        WHEN RM20101.RMDTYPAL > 6 AND
                                RM20101.DOCDATE <= @AGE AND
                                RM20201.DATE1 > @AGE AND
                                RM20201.APTODCDT > @AGE AND
                                RM20201.APFRDCDT <= @AGE AND
                                RM20101.DOCNUMBR IN (SELECT
                                                APFRDCNM
                                        FROM RM20201
                                        INNER JOIN RM20101 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR
                                                AND RM20101.DOCNUMBR = RM20201.APFRDCNM
                                        GROUP BY
                                                RM20201.CUSTNMBR
                                              , RM20201.APFRDCNM
                                              , RM20101.ORTRXAMT
                                        HAVING COUNT(RM20201.APFRDCNM) > 1) AND
                                RM20101.ORTRXAMT <> RM20101.CURTRXAM AND
                                RM20101.DUEDATE <> '' AND
                                RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT THEN RM20201.APPTOAMT * -1
                        ELSE 0
                END                                                                          
                AS [OPEN A/R]
        FROM RM20101
        INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
        INNER JOIN RM20201 ON RM20101.DOCNUMBR = RM20201.APFRDCNM
                AND RM20101.CUSTNMBR = RM20201.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'))
        GROUP BY
                RM20101.CUSTNMBR
              , RM20201.CUSTNMBR
              , RM20101.RMDTYPAL
              , RM20101.DOCDATE
              , RM20201.DATE1
              , RM20201.APTODCDT
              , RM20201.APFRDCDT
              , RM20101.DOCNUMBR
              , RM20101.ORTRXAMT
              , RM20101.CURTRXAM
              , RM20101.DUEDATE
              , RM20201.APFRMAPLYAMT
              , RM20201.APPTOAMT
              , RM00101.CUSTNAME
              , RM20201.APTODCNM
              , RM20201.APFRDCNM
        HAVING CASE
                WHEN RM20101.RMDTYPAL > 6 AND
                        RM20101.DOCDATE <= @AGE AND
                        RM20201.DATE1 > @AGE AND
                        RM20201.APTODCDT > @AGE AND
                        RM20201.APFRDCDT <= @AGE AND
                        RM20101.DOCNUMBR IN (SELECT
                                        APFRDCNM
                                FROM RM20201
                                INNER JOIN RM20101 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR
                                        AND RM20101.DOCNUMBR = RM20201.APFRDCNM
                                GROUP BY
                                        RM20201.CUSTNMBR
                                      , RM20201.APFRDCNM
                                      , RM20101.ORTRXAMT
                                HAVING COUNT(RM20201.APFRDCNM) > 1) AND
                        RM20101.ORTRXAMT <> RM20101.CURTRXAM AND
                        RM20101.DUEDATE <> '' AND
                        RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT THEN RM20201.APPTOAMT * -1
                ELSE 0
        END
        <> 0)
SELECT
        derived.[CustomerID]
      , derived.[CustomerName]
      , derived.[DocumentNumber]
      , derived.[ApplyToDocNo]
      , derived.Cnt
      , derived.[OPEN A/R]
FROM (
      select
              *
            , ROW_NUMBER() OVER (PARTITION BY cte3.[CustomerID], cte3.[ApplyToDocNo] ORDER BY cte3.[OPEN A/R] ASC) AS RowFromCTE
      from cte3
     ) as derived
where derived.RowFromCTE > 1
and derived.CustomerPayment = derived.OriginalAmt

Open in new window

I have placed the calculation of row_number into a "derived table" that I have given the alias "derived"

This then allows me to use where derived.RowFromCTE > 1
but because we are only now using one line for each partition of the OVER(...) logic, we don't need the group by clause now
so we don't use a having clause either

note. the syntax rules you are hitting are not specific to "T-SQL" they apply to ANSI standard SQL (so the syntax affects many more products )
0
 

Author Comment

by:John Ellis
ID: 41830891
Thanks, PortletPaul!  I'm glad that I'm not alone, in terms of frustration.  :)  That makes me feel better.  I have a mental disability, where I cannot comprehend much.  So, I'm stupid, unfortunately.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41830894
I had to make some edits to the comment above, please ensure you see this comment before using the code above (so my syntax errors are removed)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41830896
[smile]
you are doing very well for such a disability, welcome to the syntax-frustration-club!!
1
 

Author Comment

by:John Ellis
ID: 41830991
Thank you, PortletPaul!  :)

John
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL 2012 and SQL 2014 in memory database 11 28
SQL server 2008 SP4 29 33
t-sql splitting string column 5 25
Sql query for filter 12 21
In this article I will describe the Backup & Restore 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

708 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

20 Experts available now in Live!

Get 1:1 Help Now