Union query doesnt work. Please help
-
G'd Afternoon to all:
First of all i most say that i'm just a newbie, so please bear with me (and my english :rolleyes
. My problem is as follow: i need to get a dataset, product of the Union of two tables and i really don't know why to make a simple thing is taking me all this time....and work
Due to the imposibility (for me) to sort a union query the datasets in the way i need, i choose to create a temp table (#Mytable) where i store my sorted first dataset, that will be one of the Select statements for my union query. So i ended up with something like this:
Create Procedure sprocAny
CREATE TABLE #MyTempTable
(
tfld1(9) PRIMARY KEY,
tfld2 NVARCHAR(9),
tfld3 datetime,
)
INSERT INTO #MytTempTable (tfld1,tfld2,tfld3)
Select Distinct Top 3 fld1,fld2,fld3
from A
Order by fld3
Select * From #MyTempTable
UNION ALL
Select DISTINCT TOP3 fld1,fld2,fld3 From B
My surprise is that this sproc does not work!!!! I don't understand why! if i create a physical table it works fine, but if i create a temp table its says something like :
".. The sproc did ran succesfully but did not return any records...." .... and i get nothing
I also had tryed building two temp tables, make a union query of those temp tables and physically create a table with the result of that union... and it works
, i mean, everything works with phhysical tables, but not with temporal tables. My problem is that i don't want to overload the server with this kind of procedure, because it will run for each row displayed on the form. Besides i need a temp table for each user and not a table in the server, because i need to avoid more than one user to create/modify/delete the data at the same time.
If some one please can tell me what i'm doing wrong i really will appreciate it.
For any help, thanks in advance
Estuardo
Manifest plainness,Embrace simplicity,Reduce selfishness,Have few desires.
Lao-t'ze. -
This was removed by the editor as SPAM
-
Estuardo,
-
Estuardo,
Not sure if previous reply made it...sorry if this is a repeat.
A few things:
1) You need a datatype for col1 of your #tmp_table.
2) I was able to do a similar sproc to yours successfully.
3) There may not be any rows to return.
4) You may be able to use a table variable instead of a (physical)#tmp_table.
5) #tmp_tables are only visible to the connection that created them. Other users cannot access them. They are destroyed automatically when the sproc completes execution.
Hope this helps!
P
-
To simplify things a little perhaps...
Instead of...
CREATE TABLE #MyTempTable
(
tfld1(9) PRIMARY KEY,
tfld2 NVARCHAR(9),
tfld3 datetime,
)
INSERT INTO #MytTempTable (tfld1,tfld2,tfld3)
Select Distinct Top 3 fld1,fld2,fld3
from A
Order by fld3
You could ...
Select Distinct Top 3 fld1 as tfld1, fld2 as tfld2, fld3 as tfld3
Into #MytTempTable
from A
Order by fld3
This will "auto" make #MytTempTable to have a "compatible" structure to table "A"
Once you understand the BITs, all the pieces come together
-
Schleep & ThomasH:
First of all thank you both for your time and your kind help because due to this help i could solve my problem.
Just a few comments: Schleep, for the reason you mention in your 5th step is that i need this kind of table. Thank you for the your very welcome help.
ThomasH: with slight modifications your approach solved my problem and i get the result as expected.
One more time thank you both for the help
Best regards
Estuardo
Manifest plainness,Embrace simplicity,Reduce selfishness,Have few desires.
Lao-t'ze.
, i mean, everything works with phhysical tables, but not with temporal tables. Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply