Conditional Union!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible'
A basic example would be great!!
Cheers,
Adam.Adam Knight wrote:
> Hi all,
> I have a query that if it returns data i want to perform a union on
> it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
> A basic example would be great!!
> Cheers,
> Adam.
IF EXISTS (Select * FROM myTable WHERE myColumn = 'a')
Select NEWID(), <explicitly specify columns> FROM myTable WHERE
myColumn = 'a'
UNION ALL -- Use a UNION ALL in most cases
SELECT NEWID(), <explicitly specify columns> FROM myTable 2 WHERE
myColumn = 'b'
ORDER BY 1
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Answered in microsoft.public.sqlserver.programming.
Help others to help you. Please do not multi-post!
David Portas
SQL Server MVP
--

Conditional UNION!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible'
A basic example would be great!!
Cheers,
Adam."Adam Knight" <adam@.pertrain.com.au> wrote in message
news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have a query that if it returns data i want to perform a union on it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
>
Would this work?
Select *
FROM myTable
WHERE myColumn = 'a'
UNION
SELECT *
FROM myTable
WHERE myColumn = 'b'
and exists(
Select *
FROM myTable
WHERE myColumn = 'a')
Regards,
John|||Adam
Is there any reason to use UNION instead of UNION ALL? Do you want to
eliminate duplications?
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have a query that if it returns data i want to perform a union on it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
> A basic example would be great!!
> Cheers,
> Adam.
>
>|||Yes!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edBylb%23wFHA.3000@.TK2MSFTNGP12.phx.gbl...
> Adam
> Is there any reason to use UNION instead of UNION ALL? Do you want to
> eliminate duplications?
>
> "Adam Knight" <adam@.pertrain.com.au> wrote in message
> news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
>|||Try:
SELECT DISTINCT *
FROM MyTable
WHERE mycolumn IN ('A','B')
AND EXISTS
(SELECT *
FROM MyTable
WHERE mycolumn = 'A') ;
ORDER BY NEWID() fails under UNION or DISTINCT unless you also add NEWID()
to the SELECT list (in which case duplicates would not be eliminated).
Apparently your table doesn't have a key. I suggest you fix that problem
first but I don't see how this query helps you do that.
If the above doesn't help, please post DDL, sample data and required results
as suggested here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

Conditional Union!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible?
A basic example would be great!!
Cheers,
Adam.
Adam Knight wrote:
> Hi all,
> I have a query that if it returns data i want to perform a union on
> it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible?
> A basic example would be great!!
> Cheers,
> Adam.
IF EXISTS (Select * FROM myTable WHERE myColumn = 'a')
Select NEWID(), <explicitly specify columns> FROM myTable WHERE
myColumn = 'a'
UNION ALL -- Use a UNION ALL in most cases
SELECT NEWID(), <explicitly specify columns> FROM myTable 2 WHERE
myColumn = 'b'
ORDER BY 1
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Answered in microsoft.public.sqlserver.programming.
Help others to help you. Please do not multi-post!
David Portas
SQL Server MVP

Conditional Union!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible'
A basic example would be great!!
Cheers,
Adam.Adam Knight wrote:
> Hi all,
> I have a query that if it returns data i want to perform a union on
> it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
> A basic example would be great!!
> Cheers,
> Adam.
IF EXISTS (Select * FROM myTable WHERE myColumn = 'a')
Select NEWID(), <explicitly specify columns> FROM myTable WHERE
myColumn = 'a'
UNION ALL -- Use a UNION ALL in most cases
SELECT NEWID(), <explicitly specify columns> FROM myTable 2 WHERE
myColumn = 'b'
ORDER BY 1
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Answered in microsoft.public.sqlserver.programming.
Help others to help you. Please do not multi-post!
--
David Portas
SQL Server MVP
--

Concatenation and NTEXT

Hi everyone,
I have a problem trying to update an NTEXT column by enclosing it
between two strings, as shown in the SQL below:
UPDATE MyTable SET NTextField = N'<pre>' + NTextField + N'</pre>'
The error I get back is this:
Invalid operator for data type. Operator equals add, type equals ntext.
Please help! I'm using SQL Server 2000.
Thanks,
JonoWhy would you want to update every column in the table with the exact same
enclosure? You can't concatenate to an NTEXT column, and I don't see any
value in doing it the exact same way for every row anyway.
Anyway, since we are talking about HTML, this is something you have the
presentation layer do. For example, it is easier in ASP to say:
<pre><%=rs("NTextField")%></pre>
...than to do what you are proposing. If you really want to do this, see
the UPDATETEXT function in Books Online, but I still recommend against the
approach.
"Jono" <jono.pare@.gmail.com> wrote in message
news:1143110984.030632.152630@.g10g2000cwb.googlegroups.com...
> Hi everyone,
> I have a problem trying to update an NTEXT column by enclosing it
> between two strings, as shown in the SQL below:
> UPDATE MyTable SET NTextField = N'<pre>' + NTextField + N'</pre>'
> The error I get back is this:
> Invalid operator for data type. Operator equals add, type equals ntext.
> Please help! I'm using SQL Server 2000.
> Thanks,
> Jono
>|||u need to use UPDATETEXT for this . Use the following example
-- CREATE TABLE TextExample (i int identity(1,1), text1 text, text2 text,
text3 text)
-- INSERT INTO TextExample SELECT REPLICATE('a',7998), REPLICATE('b',7998),
NULL
DECLARE @.txtPtr1 Varbinary(16)
DECLARE @.txtPtr2 Varbinary(16)
DECLARE @.txtPtr3 Varbinary(16)
SELECT @.txtPtr1 = TEXTPTR(text1)
FROM TextExample
SELECT @.txtPtr2 = TEXTPTR(text2)
FROM TextExample
UPDATE TextExample
SET Text3 = Text1
WHERE i = 1
SELECT @.txtPtr3 = TEXTPTR(text3)
FROM TextExample
WHERE i =1
SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1
UPDATETEXT TextExample.Text3 @.txtPtr3 NULL 0 ' '
SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1
UPDATETEXT TextExample.Text3 @.txtPtr3 NULL 0 TextExample.Text2 @.txtPtr2
SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1
"Jono" <jono.pare@.gmail.com> wrote in message
news:1143110984.030632.152630@.g10g2000cwb.googlegroups.com...
> Hi everyone,
> I have a problem trying to update an NTEXT column by enclosing it
> between two strings, as shown in the SQL below:
> UPDATE MyTable SET NTextField = N'<pre>' + NTextField + N'</pre>'
> The error I get back is this:
> Invalid operator for data type. Operator equals add, type equals ntext.
> Please help! I'm using SQL Server 2000.
> Thanks,
> Jono
>