Solved

T-SQL: Why is Syntax So Difficult

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

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.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 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