GeeCON Prague 2014*
The moose likes JDBC and the fly likes multiple Select doesn't work. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "multiple Select doesn Watch "multiple Select doesn New topic
Author

multiple Select doesn't work.

Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Hi Guys,

I get this message:

...[SELECT - 0 row(s), 0.000 secs] [Error Code: 1248, SQL State: 42000] Syntax error or access violation message from server: "Every derived table must have its own alias"


This works:



this (below) doesn't work. I want to SUM the content in all D's


Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1747
    
    2
this (below) doesn't work. I want to SUM the content in all D's

Your outer select needs a GROUP BY CITY too, in order to make this approach work:

select city, SUM(D1+D2+D3+D4+D5) as DS from ( ... ) GROUP BY CITY

But, as I'll show below, you can do this without the inline view.

First though, get rid of all those DISTINCTs:

count(distinct case when type = 'D(1)' then '1' end ) "D1"

Do you realize that this expression will only ever resolve to 0 or 1? If there are no rows where type = 'D1' it resolves to 0. If there is 1 or more row where type = 'D1' it resolves to 1, regardless of the number of rows. Think of it this way: you're essentially saying something like:

select count( distinct 'constantExpression' ) from all_tables

I'm guessing that's not what you intend.

---

Ok, back to easier ways to sum the Ds. You have numerous syntax choices, stll using case when...end.



Or,



Or,



Or, what I proposed in the original thread on this topic



---

Now, having said all that, using case when...end is great for throwing together ad hoc queries based on codes. But the fact that you want to report on "all the Ds" suggests to me that you've imbedded logic into your codes: D(1), D(2), T(1), T(2), etc.

A classy way of reporting on data like this is to encode these relationships into a table and join to that table in your query:



Then you use the values of reportingType instead of building those groups in the query.
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
thanks Michael,

"Do you realize that this expression will only ever resolve to 0 or 1? If there are no rows where type = 'D1' it resolves to 0. If there is 1 or more row where type = 'D1' it resolves to 1, regardless of the number of rows. Think of it this way: you're essentially saying something like:"

Yes, I did that intentionally.

I got your answer and it helped me.
got it.

thanks a lot
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: multiple Select doesn't work.