Help with reading SQL Statement

[crmayer]

If this is the wrong place to post this, please let me know and I will move it.

I am trying to take a query from a report writer and move it to our software package and I am having a little difficulty deciphering the sql statement that was built by impromptu.

select "c26" "c1" , "c25" "c2" , "c17" "c3" , "c24" "c4" ,
"c18" "c5" ,"c23" "c6" , "c22" "c7" , "c21" "c8" ,
"c20" "c9" , "c19" "c10" , "c16" "c11"
from (select T1."order_id" "c14" , T1."customer_id" "c15" , sum(T1."amount") "c16" from "open_item" T1
group by T1."customer_id", T1."order_id"
having sum(T1."amount") < 0) D3,
(select T1."order_id" "c17" , T1."customer_id" "c18" ,
T1."source" "c19" , T1."ship_date" "c20" , T1."reference_number" "c21" , T1."record_type" "c22" ,
T1."gl_date" "c23" , T1."bill_date" "c24" ,
T1."amount" "c25" , T1."company_id" "c26"
from "open_item" T1) D2
where ("c18" = "c15" or "c18" is null and "c15" is null)
and ("c17" = "c14" or "c17" is null and "c14" is null)
order by 5 asc , 3 asc , 4 asc

I am not new to sql, but pretty new to more then just the average select statement. From what I can figure out is that the first select statement is running off the resutls of the next select, but I am not sure how the 3rd select statement fits into this. I have tried to find information on multiple subqueries, but not getting very far.
My guess is that it is similar to saying this:
select * from (1st_query, 2nd_query) OR
select * from (table, table)
Is that correct?

Is the where statement at the very end of this for the 1st select statement or the 3rd?

Any help figuring this out is greatly appriciated.

chris

 

[bborissov]

That is terrible query.
Only that:

      ("c18" = "c15" or "c18" is null and "c15" is null)
  and ("c17" = "c14" or "c17" is null and "c14" is null)

make me crazy

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[PatriciaObreja]

I think that this is like a join between 2 tables:

select field1, field2 
from 
(select ...) D3, 
(select ...) D2
where (join conditions)

-----------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[SQLSister]

PAtricia is correct that is what they are doing. This is a very old syntax and should be converted to the more common join syntax.

it wopuld be better to use something like

select field1, field2
from
(select ...) d1
join
(select ...) d2 on (Put where conditions in here if they are part of the join)

now your existing where conditions are terrible. I suspect they do not do what you want them to do. I always use parentheses when I combine and and or to make sure that what I intended as the operator precedence is what happens and to make it easier for people who later maintain my  code to understand what I intended. 

do you want
[code]
  ("c18" = "c15" or ("c18" is null and "c15" is null))
  and ("c17" = "c14" or ( "c17" is null and "c14" is null))

or do you want

  (("c18" = "c15" or "c18" is null) and "c15" is null)
  and (("c17" = "c14" or "c17" is null) and "c14" is null)

you wil get two very different answers depending on which one you really wanted.

Also I would never order by a numbered code. If someone swithces the order of the select columns you get the worng order. Use the field names.


"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

c18" = "c15" or "c18" is null and "c15" is null
"c18" = "c15" or ("c18" is null and "c15" is null)

these two statements are equivalent. You can think of OR as always starting a new block of conditions. Or you can think of AND as having a higher precedence in the order of operations.

Here's your query rewritten for human readability. It seems clear to me that these queries are being written by a business-rules level query generation layer or function that knows how to build joins through abstraction of sources. The extra complexity is just about simple ways to avoid collisions of aliases, by always renaming everything at each level to a known unique alias. The aliasing makes it hard to see what the query is doing but shouldn't affect performance as the optimizer will see right through all that and do the joins as it sees fit, not based on the given select queries individually.

However, I sincerely doubt that there is a valid customer_id of NULL, or that there is a valid order_id of NULL for each customer. A quick check on the nullability of the source columns for these tables will do the trick. I am going to assume they aren't nullable since it just doesn't make any sense otherwise. And here the optimizer could be smart enough to drop the null conditions on non-nullable columns, but maybe not, so the null = null logic is a potential performance damage (especially if the columns are nullable but in practice don't have nulls, whether non-nullness is constrained by the application, triggers, or check constraints).

so here's your query:

SELECT
   I.company_id,
   I.amount,
   I.order_id,
   I.bill_date, 
   I.customer_id,
   I.gl_date,
   I.record_type,
   I.reference_number,
   I.ship_date,
   I.source,
   S.itemsum 
FROM
   open_item T1
   INNER JOIN (
      SELECT
         S.order_id,
         S.customer_id,
         Sum(I.amount) AS itemsum
      FROM open_item S 
      GROUP BY
         S.customer_id,
         S.order_id
      HAVING Sum(S.amount) < 0
   ) S ON I.customer_id = S.customer_id = AND I.order_id = S.order_id
ORDER BY
   I.customer_id,
   order_id,
   bill_date

And if you absolutely have to have the null = null logic, then here's your replacement ON clause:

(
   I.customer_id = S.customer_id
   OR (
      I.customer_id is null
      AND S.customer_id is null
   )
) AND (
   I.order_id = S.order_id
   OR (
      I.order_id is null
      AND S.order_id is null
   )
)

 

[ESquared]

P.S. Here's your original query with nothing more than a little formatting, some implied "AS" keywords, and two pairs of parentheses:

select
   "c26" AS "c1",
   "c25" AS "c2",
   "c17" AS "c3",
   "c24" AS "c4",
   "c18" AS "c5",
   "c23" AS "c6",
   "c22" AS "c7",
   "c21" AS "c8", 
   "c20" AS "c9",
   "c19" AS "c10",
   "c16" AS "c11" 
from
   (
      select
         T1."order_id" AS "c14",
         T1."customer_id" AS "c15",
         sum(T1."amount") AS "c16"
      from "open_item" T1 
      group by
         T1."customer_id",
         T1."order_id"
      having sum(T1."amount") < 0
   ) D3, 
   (
      select
         T1."order_id" AS "c17",
         T1."customer_id" AS "c18",
         T1."source" AS "c19",
         T1."ship_date" AS "c20",
         T1."reference_number" AS "c21",
         T1."record_type" AS "c22", 
         T1."gl_date" AS "c23",
         T1."bill_date" AS "c24",
         T1."amount" AS "c25",
         T1."company_id" AS "c26" 
      from "open_item" T1
   ) D2
where
   (
      "c18" = "c15"
      or (
         "c18" is null
         and "c15" is null
      )
   ) and (
      "c17" = "c14"
      or (
         "c17" is null
         and "c14" is null
      )
   )
order by
   5 asc,
   3 asc,
   4 asc

Maybe that will help you see what was going on and why the query I gave above is identical to this one.

 

[crmayer]

Thanks for all the responses, I will take a look at this when I get home and see if I can make sense of this.

I am not a SQL guru, but I am not going to argue the fact that it is messy either. I am sure it is a combo between the report writer and the person writing the reports

Thanks again

 

[crmayer]

ESquared,
I am reading your posting and I am a little confused on where the "I" comes in to play at?
I see the "S" at the end of the second query (which I am assuming that is a reference to the 2nd query), but where is the "I" coming from?

 

[SQLSister]

I think that I should be T1 which is the alias of the table being joined to.

"NOTHING is more important in a database than integrity." ESquared

 

[crmayer]

That is what I was thinking, but when I edit that to change the I to T1, it still does not run.

What I had to do (not sure why yet) is change the "I" to open_item. Does MSSQL not allow table alias at that level or something?

Once I did that, I can now run that query, again, just not sure why I had to change it to that opposed to a table alias.

 

[ESquared]

Yes I made some mistakes. I missed making T1 be I on the alias name of the main table, and I accidentally used the I alias in the summing derived table. I like to use meaningful aliases that remind me of which table. I chose "I" because of the i in open_Item, and "S" for Sum.

Unless there are more mistakes I can't see now, this should work (see the parts changed in red). I changed the summing derived table to use I2 instead of S, to help avoid any confusion.

SELECT
   I.company_id,
   I.amount,
   I.order_id,
   I.bill_date, 
   I.customer_id,
   I.gl_date,
   I.record_type,
   I.reference_number,
   I.ship_date,
   I.source,
   S.itemsum 
FROM
   open_item [b][red]AS I[/red][/b]
   INNER JOIN (
      SELECT
         [b][red]I2[/red][/b].order_id,
         [b][red]I2[/red][/b].customer_id,
         Sum([b][red]I2[/red][/b].amount) AS itemsum
      FROM open_item [b][red]AS I2[/red][/b]
      GROUP BY
         [b][red]I2[/red][/b].customer_id,
         [b][red]I2[/red][/b].order_id
      HAVING Sum([b][red]I2[/red][/b].amount) < 0
   ) [b][red]AS[/red][/b] S ON I.customer_id = S.customer_id = AND I.order_id = S.order_id
ORDER BY
   I.customer_id,
   [b][red]I[/red][/b].order_id, -- was missing alias here, too, preventing query from running since the S table has this column also
   bill_date

If only I'd built a table to test this out, the syntax errors could have been resolved in a few seconds...

I put ASes in just for clarity about why those extra letters are in there. AS always introduces an alias.

 

[crmayer]

That worked, I did have to clean up a couple things, but it worked perfect, Thanks for all the responses.