en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
יונ26

Written by: ronen ariely
26/06/2012 18:32 RssIcon

הפרמטר UNION משמש לנו לצורך חיבור שאילתות של שליפת נתונים. ז"א אם יש לנו 2 או יותר שאילתות (בלתי תלויות) מסוג SELECT שמחזירות רשומות, ואנו רוצים לקבל כתוצאה אחת את כל הרשומות של כל אחת מהשאילתות שלנו אז נוכל לעשות שימוש בפרמטר זה.

לדוגמה:

select x,y from table1
union
select Z,T from table2

על מנת לחבר את השאילתות עלינו לעמוד בכמה כללים הגיוניים פשוטים אשר יאפשרו לנו להכניס את התוצאות של כל השאילתות למקבץ תוצאות אחד. התנהגות מסדי נתונים שונים מתנהגים בצורה שונה במקרה של תבנית שונה בתוצאות החוזרות מהשאילתות. יש כאלה שמחייבים תבנית זהה ויש מי שמאפשר סוגים שונים כל עוד ההמרה בינהם אפשרית.

הבעיה:

סוג הטורים שחוזרים בכל השאילתות בכל אחד מהטורים צריך להיות זהה, ומה אם הואט לא זהה?

דוגמה: הקוד הבא יחזיר לנו הודעת שגיאה

select 1 union
select 'f'
GO
/*
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'f' to data type int.
*/

הנסיון להציג בתוצאה טור מסוג מספר ביחד עם טור מסוג טקסט נכשל וזה הגיוני... אחרי הכל מה יהיה בדיוק הסוג של הטור בתוצאה אם הסוג של הנתונים במקור אינו אחיד?

אם נשים לב להודעת השגיאה ניתן כבר לנחש שלמעה השרת מנסה לקבוע בצורה דינאמית מה יהיה הסוג של התוצאה בהתאם לנתונים מכל השאילתות שעליו לחבר. השרת לא קובע איסור מוחלט לכך אלא מודיע לנו שהוא ניסה לבצע פעולה של המרה כדי לקבוע טור מסוג מתאים לקליטת כל הנתונים. במקרה הנוכחי ההמרה נכשלה.

ישנם סוגי נתונים שאנו יכולים לבצע המרה ישירה בינם ולכן השרת לא ימנע מאיתנו להציג אותם בטור ביחד. כך למשל השאילתה הבאה אינה נכשלת:

select 1 union
select getdate()
GO

השרת מנסה את למצוא טור מתאים אם המרה של תוצאה מאחת השאילתות לתוצאה המתאימה לשאילתה אחרת מותרת אז השרת מבצע את ההמרה, ומחזיר לנו את התוצאות אחרי ביצוע ההמרה!

1900-01-02 00:00:00.000
2012-06-26 19:00:59.573

הבעיה:

במקרה מעל ניתן לראות שהשרת בחר להמיר את התוצאה של השאילתה הראשונה שהיתה מסוג INT לסוג DATETIME. מכיוון שהמרה של DATETIME ל INT מותרת וגם ההמרה ההפוכה של INT לסוג DATETIME הרי שאנו מקבלים תוצאה המתאימה לאחת האפשרויות של ההמרה. אבל איזה מהאפשרויות?!? כיצד נוכל לשלוט על התוצאה שאנו מקבלים?

הערה חשוב! תוכנות שונות ומסדי נתונים שונים מתנהגים בצורה שונה במקרים אלו. ישנם יישומים שלא יאפשרו בכלל ביצוע פעולה של המרה אוטומטית ובכל מקרה של טורים שונים יקפיצו לנו הודעת שגיאה וישנם כאלה שיבצעו את ההמרה בצורה אוטומטית ויבחרו את סוג התוצאה בצורה שונה. הדוגמאות כאן מובאות בשרתי SQL.

נציג דוגמה נוספת:

באחז הפורומים עלתה שאלה הנוגעת לחיבור שאילתות שמחזירות נתונים טקסטואליים בתוכנת האקסס. תוכנת האקסס אינה כוללת מנוע מיטוב כשל שרת ה SQL ואינה מטפלת בהמרות האוטומטיות בצורה יעילה. לכן חיבור של נתונים מסוג "תזכיר" עם נתון מסוג "טקסט" עלול להחזיר לנו הודעת שגיאה. כמו כן במסדי נתונים כמו SQL יש לנו מספר רב של נתונים טקסטואליים כגון char.varchar,nvarchar ועלתה השאלה איזה סוג נתון יחזור לנו בתוצאה? האם סדר הנתונים בשאילתת המקור תשפיע על הסוג של התוצאה? למשל אם נחבר שאילתה של בחירה טקסט באורך 20 עם שאילתת בחירה של טקסט באורך 100 אז מה יהיה האורך של הטור בתוצאה?

ננסה לבדוק את הדברים באופן מעשי... זמן לפתוח את ה SSMS ולהתחיל להריץ קודים. ההסברים נמצאים בקוד עצמו:

use QQ
GO
 
/*
select 1 union
select 'f'
GO
 
select 1 union
select getdate()
GO
*/
 
-- לשם ההדגמה נעבוד עם כמה טבלאות זמניות
-- ולכן נבדוק שלא קיימות לנו טבלאות בשם זה לפני תחילת העבודה
IF OBJECT_ID('tempdb..#Tbl') IS NOT NULL
    print 'ERROR!!! you have to drop the temporary table ''#Tbl'' before you continue'
GO
 
-- נגדיר כמה משתנים באורך שונה ונכניס להם תוכן
-- לאחר מכן ננסה להציג את כל הנתונים בטור אחד בעזרת שימוש ב
-- UNION
declare @I1 as char(10) = 'qwertyuio'
declare @I2 as nvarchar(15) = 'qweertqwweertq'
declare @I3 as nvarchar(100) = 'dfsdf fsfsd fasdfsdfsd fdsf gher tg rgs'
select @I1 as 'T1' into #Tbl union
select @I2 union
select @I3
select * from #Tbl
GO
 
-- נבדוק את אורך הטור שנוצר לנו
select Tbl2.column_name 'Column Name',Tbl2.data_type 'Data Type',Tbl2.CHARacter_maximum_length 'Maximum Length'--,*
    from tempdb.dbo.sysobjects Tbl1
    left join tempdb.INFORMATION_SCHEMA.COLUMNS Tbl2 on Tbl1.name = Tbl2.TABLE_NAME
    WHERE Tbl1.id = OBJECT_ID('tempdb..#Tbl')
-- הערה: חומר נוסף על השאילתה הזו ניתן למצוא בבלוג הבא
GO
 
-- קיבלנו אם כן שאורך הטור של התוצאה הוא 100
-- ז"א כל הנתונים של כל השאילתות עבור המרה לאורך 100
-- התוצאה ברורה: אם נסתכל על הנתונים נראה שלא ניתן לבצע המרה לסוג של הנתון הראשון או השני
-- מכיוון שהנתון השלישי ארוך יותר מהמקום שמוגדר לטור הראשון והשני
 
-- ננקה את הטבלה להמשך עבודה
IF OBJECT_ID('tempdb..#Tbl') IS NOT NULL
    drop table #Tbl
GO
 
-- עתה ננסה לבצע פעולה דומה עם נתונים בהם כן ניתן לבצע המרה בכל הכיוונים
-- ונבדוק האם סדר השאילתות שאנחנו מחברים משפיע על התוצאה
declare @I1 as char(10) = 'qweyuio'
declare @I2 as nvarchar(15) = 'qweetq'
declare @I3 as nvarchar(100) = 'dfsdfs'
select @I1 as 'T1' into #Tbl union
select @I2 union
select @I3
select * from #Tbl
GO
 
-- נבדוק את אורך הטור שנוצר לנו
select Tbl2.column_name 'Column Name',Tbl2.data_type 'Data Type',Tbl2.CHARacter_maximum_length 'Maximum Length'--,*
    from tempdb.dbo.sysobjects Tbl1
    left join tempdb.INFORMATION_SCHEMA.COLUMNS Tbl2 on Tbl1.name = Tbl2.TABLE_NAME
    WHERE Tbl1.id = OBJECT_ID('tempdb..#Tbl')
GO
-- ניתן לראות ששוב קיבלנו תוצאה של המרה לטור הארוך ביותר שהיה לנו במקור
 
-- ננקה את הטבלה להמשך עבודה
IF OBJECT_ID('tempdb..#Tbl') IS NOT NULL
    drop table #Tbl
GO
 
-- האם זה נקבע בגלל סדר השאילתות? נבדוק זאת
declare @I1 as char(10) = 'qweyuio'
declare @I2 as nvarchar(15) = 'qweetq'
declare @I3 as nvarchar(100) = 'dfsdfs'
select @I3 as 'T1' into #Tbl union
select @I2 union
select @I1
select * from #Tbl
GO
 
-- נבדוק את אורך הטור שנוצר לנו
select Tbl2.column_name 'Column Name',Tbl2.data_type 'Data Type',Tbl2.CHARacter_maximum_length 'Maximum Length'--,*
    from tempdb.dbo.sysobjects Tbl1
    left join tempdb.INFORMATION_SCHEMA.COLUMNS Tbl2 on Tbl1.name = Tbl2.TABLE_NAME
    WHERE Tbl1.id = OBJECT_ID('tempdb..#Tbl')
GO
-- התוצאה היא שוב זהה ולכן סדר השאילתות שמחברים בשרת SQL אינו מושפיע על הסוג של התוצאה כניראה
 
-- ננקה את הטבלה להמשך עבודה
IF OBJECT_ID('tempdb..#Tbl') IS NOT NULL
    drop table #Tbl
GO
 
 
-- אז כיצד נוכל לקבוע את סוג התוצאה לבד ולא לאפשר למנוע השרת לבצע את ההחלטה בשבילנו?
-- הפתרון פשוט: נבצע המרה לנתונים שאנמחנו שולפים כבר ברמת השאילתות שליפה כך שאל החיבור יגיעו נתונים מסוגים זהים
declare @I1 as char(10) = 'qweyuio'
declare @I2 as nvarchar(15) = 'qweetq'
declare @I3 as nvarchar(100) = 'dfsdfs'
select convert(char(10),@I3) as 'T1' into #Tbl union
select convert(char(10),@I2) union
select convert(char(10),@I1)
select * from #Tbl
GO
 
-- נבדוק את אורך הטור שנוצר לנו
select Tbl2.column_name 'Column Name',Tbl2.data_type 'Data Type',Tbl2.CHARacter_maximum_length 'Maximum Length'--,*
    from tempdb.dbo.sysobjects Tbl1
    left join tempdb.INFORMATION_SCHEMA.COLUMNS Tbl2 on Tbl1.name = Tbl2.TABLE_NAME
    WHERE Tbl1.id = OBJECT_ID('tempdb..#Tbl')
GO
 
-- ננקה את הטבלה להמשך עבודה
IF OBJECT_ID('tempdb..#Tbl') IS NOT NULL
    drop table #Tbl
GO
 
-- אפשרות נוספת היא להכניס את התוצאות ישירות לטלה קיימת כבר שבה מוגדר סוגי הטורים למשל
declare @I1 as char(10) = 'qweyuio';
declare @I2 as nvarchar(15) = 'qweetq';
declare @I3 as nvarchar(100) = 'dfsdfs';
create table #Tbl ([T1] char(10) );
insert #Tbl
select @I1 union
select @I2 union
select @I3;
select * from #Tbl
GO
 

-- ננקה את הטבלה לסיום עבודה
IF OBJECT_ID('tempdb..#Tbl') IS NOT NULL
    drop table #Tbl
GO

קריאה מהנה.

.

.

Tags: SQL , union
Categories: SQL