Can you tell me what this code is doing? No opening parenthesis

1

Below is the end of a script. There are a series of does not equal filters followed by a closing parenthesis. There is no opening parenthesis, what is the closing parenthesis accomplishing? Following the closing parenthesis is "AS SQ", what is this applying the 'SQ' alias to?

F4211.SDUORG/100000 AS 'Qty Ordered',
F4211.SDSOBK/100000 AS 'Qty Backordered',
Case When SDNXTR > '570' Then F4211.SDSOQS/100000 Else 0 End as 'Qty Shipped',
(F4211.SDUORG/100000 * F4211.SDUPRC/100000) as 'Total Price',
F4211.SDUORG/100000 * (Select TOP(1) COUNCS/10000 From JDE_PDQUERY.PRODDTA.F4105 Where COMCU=F4211.SDMCU and COITM=F4211.SDITM and COCSIN='I') as 'Total Cost',
Replace((select abalky from JDE_PDQUERY.PRODDTA.f0101 where aban8=(SELECT TOP (1) CMSLSM FROM JDE_PDQUERY.PRODDTA.F42140 AS F42140 WHERE (CMAN8 = SDAN8))),' ','') as 'Salesperson',
SDTORG as 'Originator'
FROM JDE_PDQUERY.PRODDTA.F4211 F4211 (NOLOCK)
WHERE F4211.SDLTTR<>'980' and Substring(F4211.SDGLC,1,2) <> 'FT' and F4211.SDDCTO <> 'SQ' and F4211.SDDCTO <> 'ST' AND SDNXTR>'565') AS SQ Where [Date Shipped] IS NOT NULL AND [Date Shipped]>=GETDATE()-1096

2

It is a table alias for the above query

By the way this is a very oogly query

3

There is an opening paren:

...
F4211.SDUORG/100000 * ( Select TOP(1) COUNCS/10000 From JDE_PDQUERY.PRODDTA.F4105
...

1 Like
4

Your Code = Formatted

F4211.sduorg/100000 AS 'Qty Ordered', f4211.sdsobk/100000 AS 'Qty Backordered',
CASE
WHEN sdnxtr > '570' THEN
  f4211.sdsoqs/100000
  ELSE 0
END
AS 'Qty Shipped', (f4211.sduorg/100000 * f4211.sduprc/100000) AS 'Total Price', f4211.sduorg/100000 *
(
       SELECT TOP(1)
              councs/10000
       FROM   jde_pdquery.proddta.f4105
       WHERE  comcu=f4211.sdmcu
       AND    coitm=f4211.sditm
       AND    cocsin='I') AS 'Total Cost', replace(
(
       SELECT abalky
       FROM   jde_pdquery.proddta.f0101
       WHERE  aban8=
              (
                     SELECT TOP (1)
                                                       cmslsm
                     FROM   jde_pdquery.proddta.f42140 AS f42140
                     WHERE  (
                                   cman8 = sdan8))),' ','') AS 'Salesperson', sdtorg AS 'Originator' FROM jde_pdquery.proddta.f4211 f4211 (nolock) WHERE f4211.sdlttr<>'980'
AND
substring(f4211.sdglc,1,2) <> 'FT'
AND
f4211.sddcto <> 'SQ'
AND
f4211.sddcto <> 'ST'
AND
sdnxtr>'565') AS sq WHERE [Date Shipped] IS NOT NULL
AND
[Date Shipped]>=getdate()-1096
1 Like
5

your code what you put
Parenthesis Diagram

this is a very very common issue .. how do you figure out ? methods ?

image
image757×352 1.62 KB

1 Like
6

much better to do it

in small pieces

temp tables

7

Thank you to those that replied. Very helpful. I don't write SQL and am working on modifying this to my purposes.

8

Often not. I doubt it for the query above.

But you do need to change this WHERE condition:
AND
substring(f4211.sdglc,1,2) <> 'FT'
to:
AND
f4211.sdglc NOT LIKE 'FT%'

Because that can make better use of potential indexes.