Views dropped automatically when altering tables?

2012-02-07 Thread John English
Hi all, When altering a table the other day I discovered that any views that reference the table get dropped automatically (and silently), as do any views that depend on those views, and so on. This came as a nasty surprise to me when I tried to access one of those views! If the table has

Re: Porting to standard SQL

2012-02-07 Thread Rick Hillegas
On 2/6/12 2:29 PM, TXVanguard wrote: Rick Hillegas-3 wrote: Hope this helps, -Rick Rick, thanks for all your help. The SELECT DISTINCT syntax seems to work. Here's one more wrinkle I discovered: I have several lines that look like this: UPDATE T1 INNER JOIN T2 ON (T1.A= T2.A) SET T2.B =

Re: Views dropped automatically when altering tables?

2012-02-07 Thread Rick Hillegas
Hi John, This is one of many areas where Derby's dependency tracking could be improved. I think that Derby is over-aggressive in dropping views when you drop a column in the underlying table. As I read the SQL Standard (part 2, section 11.19 drop column definition), a RESTRICTed column drop

Re: Porting to standard SQL

2012-02-07 Thread TXVanguard
Rick, I've discovered a few more wrinkles in some of the UPDATE/INNER JOIN states I have to port. Here's an example: UPDATE T1 INNER JOIN (T2a INNER JOIN T2b ON T2a.B = T2b.B) ON T1.B = T2b.B SET T2a.D = true WHERE (T2b.E=true AND NOT (T1.F=2 AND (T2b.G=2 OR T2b.G=1))); Note that the table on

Re: Views dropped automatically when altering tables?

2012-02-07 Thread John English
On 07/02/2012 16:43, Rick Hillegas wrote: Hi John, This is one of many areas where Derby's dependency tracking could be improved. I think that Derby is over-aggressive in dropping views when you drop a column in the underlying table.As I read the SQL Standard (part 2, section 11.19 drop column

Re: Porting to standard SQL

2012-02-07 Thread Dag H. Wanvik
TXVanguard brett.den...@lmco.com writes: I have several lines that look like this: UPDATE T1 INNER JOIN T2 ON (T1.A= T2.A) SET T2.B = T1.B WHERE (T1.C = TRUE AND T2.C = 5) I don't believe Derbys support updating JOINs, which is why you need to rewrite the query as suggested earlier in this

Re: Views dropped automatically when altering tables?

2012-02-07 Thread Dag H. Wanvik
John English john.fore...@gmail.com writes: Hi all, When altering a table the other day I discovered that any views that reference the table get dropped automatically (and silently), as do any views that depend on those views, and so on. This came as a nasty surprise to me when I tried to

Re: Porting to standard SQL

2012-02-07 Thread TXVanguard
Dag H. Wanvik-2 wrote: I don't believe Derbys support updating JOINs, which is why you need to rewrite the query as suggested earlier in this thread. Right. I understand that I need to rewrite the query as suggested; I was trying to find out how to correctly incorporate the WHERE clause

Re: Porting to standard SQL

2012-02-07 Thread Rick Hillegas
On 2/7/12 8:09 AM, TXVanguard wrote: Rick, I've discovered a few more wrinkles in some of the UPDATE/INNER JOIN states I have to port. Here's an example: UPDATE T1 INNER JOIN (T2a INNER JOIN T2b ON T2a.B = T2b.B) ON T1.B = T2b.B SET T2a.D = true WHERE (T2b.E=true AND NOT (T1.F=2 AND (T2b.G=2

Re: Porting to standard SQL

2012-02-07 Thread TXVanguard
Rick, you've been very patient and helpful. I'm still not sure I understand the complexities of what's going on, but you've shed a lot of light on the subject. Thanks for your help. Rick Hillegas-3 wrote: Hm, again I'm not an expert on Access syntax. I think that following the pattern of