Optimize SQL

Hello All!
Is there a way to write this SQL without a UNION, because where i have to
set one field to null to match the number of field of the second sql :(
SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
AgenciaID, 'Caixa' AS Tipo
FROM Caixa
UNION
SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
Agencia.ID AS AgenciaID, 'Banco' AS Tipo
FROM Agencia JOIN
BANCO ON Banco.ID = Agencia.ID
Thank you all!!!
Bruno NThere probably are other solutions but if you want the same columns in
there then you will presumably have to populate the AgenciaID column
with NULLs anyway. I'm not sure what it is you want to do differently.
You should consider using UNION ALL instead of UNION unless it's
required to eliminate duplicates. UNION ALL will typically perform
better.
If you need more help, please post some more info as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Nope, unless you want to return 2 separate result sets. Then you can do
without the Tipo column as well.
There's nothing wrong really with setting one column to NULL if it doesn't
exist in that part of the unioned set.
Jacco Schalkwijk
SQL Server MVP
"Bruno N" <nylren@.hotmail.com> wrote in message
news:eHHIkgSNFHA.3788@.tk2msftngp13.phx.gbl...
> Hello All!
> Is there a way to write this SQL without a UNION, because where i have to
> set one field to null to match the number of field of the second sql :(
> SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
> AgenciaID, 'Caixa' AS Tipo
> FROM Caixa
> UNION
> SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
> Agencia.ID AS AgenciaID, 'Banco' AS Tipo
> FROM Agencia JOIN
> BANCO ON Banco.ID = Agencia.ID
> Thank you all!!!
> Bruno N
>|||Thanks guys!!
"Bruno N" <nylren@.hotmail.com> escreveu na mensagem
news:eHHIkgSNFHA.3788@.tk2msftngp13.phx.gbl...
> Hello All!
> Is there a way to write this SQL without a UNION, because where i have to
> set one field to null to match the number of field of the second sql :(
> SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
> AgenciaID, 'Caixa' AS Tipo
> FROM Caixa
> UNION
> SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
> Agencia.ID AS AgenciaID, 'Banco' AS Tipo
> FROM Agencia JOIN
> BANCO ON Banco.ID = Agencia.ID
> Thank you all!!!
> Bruno N
>

Optimistic Concurrency Control Error

Hi,

I have a table X:
ID (PK, int, not null)
cstID(FK, int, not null)
Name( nvarchar(100),not null)
Desc( ntext, null)

I am using the table view in Enterprise manager, if I manually type in a new row, then I edit that row, setting "Desc" = NULL, then I delete that row (from within the table view) I get the error:

Data has changed since the results pane was last retrieved. Do you want to save your changes now? (Optimistic Concurrency Control Error)

Things to note:
There was a FTI on this table, I deleted it, didn't help.
No other process or users are editing/viewing this table
The error doesn't occur if edit any other column, just setting the "Desc" to NULL creates this error.

Some other tables in my DB exhibit this same behavior, but not all......I can't figure out what the heck is going on...can you?

Can't anyone take a stab at this?

Some more information, I using SQL server 2005 so I am using MS not EM.

I created a duplicate table and that dup table doesn't have the same issue. So, I scripted both the bad table and the dup table, both scripts look identical sans the table names. I ran a trace and it doesn't look like anything different is happening between the original table and the dup table.

The table doesn't have any triggers.

Please help!

|||I get a similar message when I try to update any tables that contain fields of type bit, ntext, text, or image. But I only get it occasionally and cannot find a reason. I changed my ntext field to varchar and that eliminated the problem.|||

I can help you out here.

The fundamental cause of the problem is that Management Studio is rubbish.

It breaks down like this:

Management Studio can't handle edits on rows with char/varchar/text fields with more than 4000 characters of data (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266305&SiteID=1)

One side -effect of this is that in optimistic mode Management Studio thinks that the textual data has changed in between loading it and saving your changes to other fields so it warns you. Fortunately it doesn't go so far as to actually destroy your text data.

Optimistic Concurrency Control Error

Hi,

I have a table X:
ID (PK, int, not null)
cstID(FK, int, not null)
Name( nvarchar(100),not null)
Desc( ntext, null)

I am using the table view in Enterprise manager, if I manually type in a new row, then I edit that row, setting "Desc" = NULL, then I delete that row (from within the table view) I get the error:

Data has changed since the results pane was last retrieved. Do you want to save your changes now? (Optimistic Concurrency Control Error)

Things to note:
There was a FTI on this table, I deleted it, didn't help.
No other process or users are editing/viewing this table
The error doesn't occur if edit any other column, just setting the "Desc" to NULL creates this error.

Some other tables in my DB exhibit this same behavior, but not all......I can't figure out what the heck is going on...can you?

Can't anyone take a stab at this?

Some more information, I using SQL server 2005 so I am using MS not EM.

I created a duplicate table and that dup table doesn't have the same issue. So, I scripted both the bad table and the dup table, both scripts look identical sans the table names. I ran a trace and it doesn't look like anything different is happening between the original table and the dup table.

The table doesn't have any triggers.

Please help!

|||I get a similar message when I try to update any tables that contain fields of type bit, ntext, text, or image. But I only get it occasionally and cannot find a reason. I changed my ntext field to varchar and that eliminated the problem.|||

I can help you out here.

The fundamental cause of the problem is that Management Studio is rubbish.

It breaks down like this:

Management Studio can't handle edits on rows with char/varchar/text fields with more than 4000 characters of data (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266305&SiteID=1)

One side -effect of this is that in optimistic mode Management Studio thinks that the textual data has changed in between loading it and saving your changes to other fields so it warns you. Fortunately it doesn't go so far as to actually destroy your text data.