Solved

T-SQL: Why is Syntax So Difficult

Posted on 2016-10-05
5
48 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

863 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

18 Experts available now in Live!

Get 1:1 Help Now