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
--
Showing posts with label mycolumn. Show all posts
Showing posts with label mycolumn. Show all posts
Monday, March 19, 2012
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
--
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
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
--
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
--
Wednesday, March 7, 2012
Conditional IF in a derived column transform
HI, I was wondering if there is a possibility to use a confitional if like this:
IF(ISNULL(mycolumn value, "new value if null", mycolumnvalue)
into a derived column transform to infer a value to a null column value. I do know I can do it using a script component by it would be simpler to do by using an expression.
Thank you,
Ccote
Yes, you can do this. Look here in BOL for conditional operator: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/d38e6890-7338-4ce0-a837-2dbb41823a37.htm
-Jamie
|||
Thank you Jamie. I always forget to look in BOL.
Thank you again
Ccote
Sunday, February 12, 2012
Concatenation in query
Hi all,
I am using concatenation in Query in Sql Server like,
Select Column1 + ' bla bla ' + Column2 as MyColumn from MyTable
So, here any secruity issure occur or not... because some one tell to me.. d'not use Concetenation in query bcz it is not secure, worst in performance and helpfull in SQL injection......
any idea about that ??
Thanks
Sajjadno security issue, performance is fine, and sql injection is irrelevant
:)|||thanx ;)
plz tell me about sql injection|||you may find out more about sql injection here (http://google.ca/search?q=sql+injection)|||...sql injection is irrelevantCare to elaborate?|||irrelevant in the context of the given question
there's no way that this --Select Column1 + ' bla bla ' + Column2 as MyColumn from MyTablewill pose an sql injection threat, since the values are already in the table
i usually try to restrict myself to answering questions always within the context of the question -- for example, replication and backup are irrelevant here, too|||Depends upon whether 'blah blah' is passed as a variable.|||' bla bla ' is a constant string in this context, isn't it
:)|||I think it is unclear from his original post, which is why I was concerned that your response would be misconstrued.
Hard-coded dynamic sql = Injection free.
Concatenated parameters = Injection warning.|||ooh, i like it when you get concerned -- don't stop ;)
you are right, sql injection is serious business, and perhaps it's a good idea to mention it in every situation where it might poke its ugly little snoot|||thanx for all :)
I am using concatenation in Query in Sql Server like,
Select Column1 + ' bla bla ' + Column2 as MyColumn from MyTable
So, here any secruity issure occur or not... because some one tell to me.. d'not use Concetenation in query bcz it is not secure, worst in performance and helpfull in SQL injection......
any idea about that ??
Thanks
Sajjadno security issue, performance is fine, and sql injection is irrelevant
:)|||thanx ;)
plz tell me about sql injection|||you may find out more about sql injection here (http://google.ca/search?q=sql+injection)|||...sql injection is irrelevantCare to elaborate?|||irrelevant in the context of the given question
there's no way that this --Select Column1 + ' bla bla ' + Column2 as MyColumn from MyTablewill pose an sql injection threat, since the values are already in the table
i usually try to restrict myself to answering questions always within the context of the question -- for example, replication and backup are irrelevant here, too|||Depends upon whether 'blah blah' is passed as a variable.|||' bla bla ' is a constant string in this context, isn't it
:)|||I think it is unclear from his original post, which is why I was concerned that your response would be misconstrued.
Hard-coded dynamic sql = Injection free.
Concatenated parameters = Injection warning.|||ooh, i like it when you get concerned -- don't stop ;)
you are right, sql injection is serious business, and perhaps it's a good idea to mention it in every situation where it might poke its ugly little snoot|||thanx for all :)
Subscribe to:
Posts (Atom)