Problem in Union All Operator - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem in Union All Operator

Author

Topic

Billa
Starting Member

2 Posts

Posted - 2007-10-09 : 00:36:38

Hi,
Im a newbie to sql.I have used a query like "select * from table1 unionall select * from table2".(for eg.,take table1 contains 5 columns and table2 contains 4 coumns.)after executing this query i got only the results of first table.ie., if i use rs.getString(1)..rs.getString(5),i got corresponding data.But i didnt get the contents of table2.How can i get the value of the table2? I tried using query like rs.getString(6)(table2's first column),it returns nothing.. why? Plz help me as soon as possible.and also those two table does not have similar datatype columns.whether this is the problem?.. help me..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-09 : 00:38:14

You'll need to post all of your relevant code as it's hard to figure out what you are doing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

renu
Starting Member

47 Posts

Posted - 2007-10-09 : 00:50:06

hi to do 'union all' operation there should be equal number of columns in both tables and they should have the same datatype

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-10-09 : 00:51:24

hi,
If u want to know about union and unionall please follow the
the link below.I think it will be helpful to u.
http://www.w3schools.com/sql/sql_union.asp
Happy programing!

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 03:58:23

"select * from table1 unionall select * from table2"

If you have NO space in UNIONALL you will have got two recordsets (and perhaps your program only processed the first one).

If you use "UNION ALL" (with space) you will get an error that the numbers of columns / datatypes don't match.

~~I have no idea why UNIONALL works as a command separator! [but only when there is no WHERE clause; some sort of Bug I reckon]~~

Edit: I had no idea, but Peso did!

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 04:22:09

quote:
Originally posted by Kristen

I have no idea why UNIONALL works as a command separator! [but only when there is no WHERE clause; some sort of Bug I reckon]

UNIONALL (without spaces) becomes an ALIAS for Table1...

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 04:27:06

Of course it does; thanks for pointing that out!

I'll not admit to how long I pondered why it went straight to Error when I put a WHERE clause in

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-09 : 05:04:26

quote:
Originally posted by Kristen

Of course it does; thanks for pointing that out!

I'll not admit to how long I pondered why it went straight to Error when I put a WHERE clause in

Kristen

Thats why I want SQL Server to throw error if alias name is not preceded by AS. Sometimes it becomes tedious to debug if you omit the comma in the select statement


Select emp_id emp_name,DOB from employees

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 06:04:03

Yup, +1 to that.

I long for a Lint type tools for SQL Server, or even just some warning levels, or some sort of "fussy" mode

SET OPTIONS that disallow:

varchar(no size given)

missing AS

Implicit cast

IF without BEGIN

Indentation / Logic mismatch

would be good

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 06:22:46

-1 for the "IF without BEGIN" thingy...

IF EXISTS (SELECT ...)
 SET @A = 1
ELSE
 SET @A = 0

is easier to read than

IF EXISTS (SELECT ...)
  BEGIN
    SET @A = 1
  END
ELSE
  BEGIN
    SET @A = 0
  END

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 06:55:37

Accident waiting to happen when you stick a second statement in there in the future, seeing the indentation and thinking you are in a code block.

There are only a couple of places we allow that:

The


IF (SProc exists)
    DROP Sproc

at the top of our scripts, and the


IF (SProc exists)
    GRANT Execute TO ...

at the bottom. Its never allowed within any "real" code here.

But my preference would be that each of these can be enabled by a Switch, so you can have Fussy-Server

on anything you need alerting to.

You know, "Your turn to put the rubbish out", that type of thing

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-09 : 07:12:55

quote:
Originally posted by Peso

-1 for the "IF without BEGIN" thingy...
IF EXISTS (SELECT ...)
SET @A = 1
ELSE
SET @A = 0
is easier to read than
IF EXISTS (SELECT ...)
BEGIN
SET @A = 1
END
ELSE
BEGIN
SET @A = 0
END

E 12°55'05.25"
N 56°04'39.16"

Agreed but that would result in the following error

Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'else'.

if there are more than one statement for IF though no error for ELSE

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-09 : 07:16:07

quote:
Originally posted by Kristen

Yup, +1 to that.

I long for a Lint type tools for SQL Server, or even just some warning levels, or some sort of "fussy" mode

SET OPTIONS that disallow:

varchar(no size given)

missing AS

Implicit cast

IF without BEGIN

Indentation / Logic mismatch

would be good

Kristen

Also it should throw the following error

Server: Msg 8152, Level 16, State 9, Line 3
String or binary data would be truncated.
The statement has been terminated.

for the following code


declare @v varchar(2)
set @v='test'
select @v

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 07:21:50

Huh?

DECLARE @A INT

IF EXISTS (SELECT * FROM master..spt_values)
  BEGIN
    SET @A = 1
  END
ELSE
  BEGIN
    SET @A = 0
  END

SELECT @A

works perfectly!
Also this works

DECLARE @A INT

IF EXISTS (SELECT * FROM master..spt_values)
    SET @A = 1
ELSE
    SET @A = 0

SELECT @A

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-09 : 07:27:29

quote:

Originally posted by Peso

Huh?

DECLARE @A INT

IF EXISTS (SELECT * FROM master..spt_values)
  BEGIN
    SET @A = 1
  END
ELSE
  BEGIN
    SET @A = 0
  END

SELECT @A

works perfectly!
Also this works

DECLARE @A INT
DECLARE @B INT
IF EXISTS (SELECT * FROM master..spt_values)
    SET @A = 1
    SET @B = 1
ELSE
    SET @A = 0

SELECT @A

E 12°55'05.25"
N 56°04'39.16"

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 07:36:54

[code]
declare @v varchar(2)
set @v='test'
select @v
[/code]
and also Over Long string parameter to an Sproc.

When are MS next running their $1/idea special offer?

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 07:49:38

Ok. When (sometime in the future) you need to add a second statement, then also provide the proper BEGIN/END.
Why should I now prepare for something that might (or might not) happen in the future in the code?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 07:53:06

Its horses-for-course, of course, but the risk is that in Maintenance you don't spot the need, the testing is less than in the original Development phase, and you create an unintended bug.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources