?
Solved

T-SQL: Why is Syntax So Difficult

Posted on 2016-10-05
5
Medium Priority
?
76 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
[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
  • 3
  • 2
5 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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 49

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

762 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