JOIN בין טבלה ל-DYNAMIC SQL

Question

היי.

יש לי שאלה שייתכן מאוד שהתשובה תהיה "אי אפשר", אבל אני אנסה.

יש לי טבלת משתמשים, נקרא לה USERS.

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

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

המטרה שלי היא לייצר VIEW או פונקציה טבלאית (כנראה שפונקציה היא האופציה הריאלית), שתחזיר את כל נתוני המשתמש. המאפיינים הנוספים צריכים להיות בעמודות לכל משתמש (לכל משתמש תחזור שורה אחת).

כדי לקבל את הנתונים מטבלת המשתמשים, לא צריך יותר מ-SELECT פשוט: SELECT * FROM USERS.

כדי לקבל את הנתונים הנוספים, השתמשתי ב-PIVOT ב-DYNAMIC SQL (כאמור, בהרצה לא ידועים המאפיינים ומספרם). משהו כזה:

DECLARE @FieldsTbl table (FieldID int, FieldName nvarchar(500))
INSERT INTO @FieldsTbl  (	FieldID, FieldName )
	SELECT	f.FieldID, f.Name FROM	.....

DECLARE @FieldsList nvarchar(max) = N'', @FieldsColumnsList nvarchar(max) = N''
UPDATE	@FieldsTbl 
SET	@FieldsList = @FieldsList + N'[' + cast(FieldID as nvarchar(20)) + N'],' ,
	@FieldsColumnsList = @FieldsColumnsList + N'[' + cast(FieldID as nvarchar(20)) + N'] as ''' + FieldName + N''','

select	@FieldsList=left(@FieldsList, len(@FieldsList)-1),
		@FieldsColumnsList=left(@FieldsColumnsList, len(@FieldsColumnsList)-1)

DECLARE @Sql nvarchar(max) = ''
SET @Sql = '
; with Vls as
	(select ObjectID, FieldID, Value from MetaDT_Values)
select	ObjectID, 
' + @FieldsColumnsList + ' 
from Vls
PIVOT
(	MIN (Value)
	FOR FieldID IN (' + @FieldsList + ')
) PivotTable
'

exec sp_executesql @sql 

הבעיה: איך אני עושה את החיבור (JOIN) בין 2 התוצאות, כאשר המטרה היא שתחזור טבלה - כאמור מ-VIEW או מפונקציה.

אני לא יכול להריץ את ה-DYNAMIC SQL לתוך משתנה טבלאי או טבלה זמנית, שכן אני לא יכול להגדיר אותם כי אני לא יודע את מספר ושמות העמודות של המאפיינים הנוספים.

יצירת טבלה זמנית בתוך ה-DYNAMIC SQL לא אפשרית שכן הטבלה הזמנית לא מוכרת מחוץ לבלוק הזה.

יצירת טבלה זמנית גלובאלית לא אפשרית שכן יש סיכוי גבוה ששני משתמשים יקראו לה בו זמנית ואז תהיה נפילה (כנראה ב-CREATE).

ראיתי באיזה אתר פתרון שלא היה נראה לי כמשהו עובד (ואכן אצלי הוא לא עובד), אבל נראה שקיבל פידבקים חיוביים. הפתרון הוא בתוך ה-DYNAMIC SQL ליצור טבלה זמנית בפורמט הזה: 

set @query = 'CREATE table #myTempTable AS select....

ואז לעשות JOIN פשוט לטבלה הזמנית. כאמור, אצלי זה לא עבד....

http://stackoverflow.com/questions/2461579/how-to-join-dynamic-sql-statement-in-variable-with-normal-statement

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

אשמח לרעיונות, תודה!

---

itaigitt, http://copypastenet.blogspot.com

Answer 1

איתי שלום

אתה יכול להתחיל מצירוף DDL+DML כדי שיהיה לנו את הכיף להתעמק בבעיה ולא בחלק הטכני של מבנה האובייקטים שלך ונתונים לדוגמה?

בינתיים אני אזרוק תשובה כללית שאין מצב שאי אפשר במחשבים ולכל בעיה יש פתרון (אם לא בדרך אחת אז בדרך שנייה). זה כל מה שקראתי בינתיים בשאלה אחרי שהעתקתי את הקוד ל SSMS ואז הבנתי שהקוד אינו כולל DML :-)

Answer 2

בינתיים הנה כמה נקודות אבל מאוד יקל אם תצרף DDL+DML רלוונטיים. בינתיים אולי הנקודות האלו יעזרו

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

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

* בקישור הבא תוכל לראות dynamic pivot

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

** יצירת טבלה זמנית לא מוכרת מחוץ לבלוק הדינאמי אבל :-) יצירת טבלה זמנית מראש ומילוי שלה בזמן העבודה הדינמית כן מוכרת וככה מבצעים את הדבר

---

Answer 3

היי רונן.

קודם כל תודה על ההשקעה. אבל נראה לי שפספסת את הנקודה.

אני צריך להחזיר VIEW של כל המשתמשים, עם הערכים של כל המאפיינים למשתמש - מטבלת המשתמשים והמאפיינים הנוספים (ואם למשתמש מסוים לא הוגדר ערך למאפיין אז יקבל NULL).

אני יודע להגדיר dynamic pivot, עשיתי כמו שהראיתי בקוד שצרפתי. מזה אני מקבל את כל המאפיינים לכל המשתמשים שהוגדר להם מאפיין אחד לפחות.

את רשימת כל המשתמשים ואת הערכים מטבלת המשתמשים אקבל בצורה מאוד פשוטה: SELECT * FROM USERS.

עכשיו מה שנותר לי זה לחבר ביניהם. העניין הוא שאחד מהם זה SELECT והשני זה sp_executesql - וזה הקושי.

וזה העניין שאולי לא ניתן לעשות.

---

itaigitt, http://copypastenet.blogspot.com

Answer 4

אתה יכול ליצור פקודת Select Into שתשלח את הפלט לתוך טבלה חדשה וכך תפתור את בעיית יצירת הטבלה.
הבעייה היא שטבלה זמנית פרטית (# בודד) תהיה מוכרת רק בתוך הפקודה הדינאמית ולא מחוץ לה ולכן לא תוכל לבצע איתה Join כפי שרצית.
לכך יש שני פתרונות:
1. יצירת טבלת זמנית ציבורית (## כפול) או טבלה רגילה שתהיה מוכרת גם בחוץ.
2. ביצוע ה-Join בתוך הפקודה הדינאמית (ואז ניתן לוותר על יצירת הטבלה הזמנית ולבצע Join עם השליפה עצמה). 

---

Geri Reshef http://gerireshef.wordpress.com

Answer 5

תודה גרי!

הבעיה לגבי טבלה זמנית פרטית ידועה, ואם היא לא הייתה לא היה נוצר הפוסט הזה....

טבלת זמנית ציבורית היא פתרון שחשבתי עליו, אבל הוא לא טוב כי אם יקראו לזה מסר משתמשים בו זמנית, כולם חוץ מהראשון יעופו ב-CREATE TABLE ##T....

אכן עשיתי את ה-JOIN בתוך הפקודה הדינאמית. זה אכן, כצפוי, מחזיר לי את הפלט הרצוי. אגב, את הקוד הזה הכנסתי לתוך פרוצדורה, שהרצה שלה מחזירה לי את ה'VIEW' הרצוי.

אבל.....

אני צריך יכולת לחבר בין הפלט הזה לבין טבלאות\פלטים אחרים.

לדוגמא, דו"ח שמחבר נתונים שונים של משתמשים ב-JOIN לפלט הזה שיצרנו.

אי אפשר לעשות JOIN לפרוצדורה, ואי אפשר להריץ אותה בתוך פונקציה. אי אפשר גם לעשות insert into #t exec MySP כי אנו לא יודעים להגדיר את #t לפני הרצת הפקודה.

זה למעשה המקום שאני תקוע בו.

 

---

itaigitt, http://copypastenet.blogspot.com

Answer 6

אם כך נסה כדלקמן:

Select *

From    OpenRowSet('SQLNCLI','Server=(Local);Trusted_Connection=Yes','Set FmtOnly Off Exec MyDB.dbo.MyProc')

העתק את הקוד כמו שהוא (הוא יתיישר ב-SSMS),
וציין את שם הדטבייס והפרוצדורה הנכונים.

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

SP_Configure 'Ad Hoc Distributed Queries',1;

Go

Reconfigure With Override;

Go

 

שים לב שלא ניתן להריץ קוד דינאמי, ולכן למיטב ידיעתי לא ניתן להעביר פרמטרים לפרוצדורה.

---

Geri Reshef http://gerireshef.wordpress.com

Answer 7

איתי כתבתי לך פתרון גם לבעיה הזו מעל :-)

זה אולי מופיע בצורה לא ברורה אבל תבדוק מעל

הנה הציטוט: אפשרות לעקוף את זה יההי למצוא את מספר השות המקסימלי שיש בין כל המשתמשים ואז לבנות טבלה זמנית עם מספר שדות כזה ואליה להכניס את כל התוצאות של ה PIVOT הדינאמי לפי משתמש

והנה ללא שגיאות הקלדה :-)

אפשרות לעקוף את זה יהיה למצוא את מספר השדות המקסימלי שיש בין כל המשתמשים ואז לבנות טבלה זמנית עם מספר שדות כזה ואליה להכניס את כל התוצאות של ה PIVOT הדינאמי לפי משתמש

בצורה כזו למי שחסר נתון פשוט יהיה NULL בדיוק כמו שרצית (אני מכיר את הבעיה ויישמתי אותה לא פעם אחת וזה פתרון יעיל)

** דרך אגב אם היית מצרף DDL+DML מסודרים (עדיף מייצגים ולא של כל השדות למשל) אולי כבר הייתי מעביר לך פתרון סופי מוכן... בינתיים אני מנסה להעביר את הלוגיקה בתיאורטית

Answer 8

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

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

בלולאה בודדת עוברים על כל המשתמשים (אפשר גם בלי לולאה אבל לשם ההסבר) ועתה עבור כל משתמש מבצעים:

___ תחילת לולאה: הגענו למשתמש ___

1. מוצאים את השורה שמחפשים באמצעות PIVOT דינאמי (עד לכאן אתה סגור על הנושא). ומכאן גם יודעים כמובן כמה שדות יש.

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

F1,F2,F3....Fn

3. עתה ניתן להכניס לטבלה את הרשומה שלנו (יודעים את שמות השדות ומבנה הטבלה תואם לרשומה שלנו)

___ סגירת לולאה: עוברים למשתמש הבא ___

בסיום תקבלו טבלה שמספר השדות שלה כמספר השדות המקסימלי והנתונים הוכנסו לטבלה בצורה מסודרת כשהיה חסר יופיע פשוט NULL

בהצלחה :-)

---

Answer 9

היי איתי,

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

אתה יכול לייצר את הטבלה בתוך המשפט הדינמי אבל גם את ה JOIN תעשה שם ואז זה יעבוד.

 

---

Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

Answer 10

תודה גרי! ה-OpenRowSet זה בדיוק מה שהייתי צריך.

למזלי אין פרמטרים לפרוצדורה. את השאילתא שמשתמשת ב-OpenRowSet שמתי ב-VIEW ועכשיו השאילתא פתוחה ל-JOIN-ים ושרק ירקות לשאר האובייקטים ב-DB.

תודה גם לשאר העונים.

---

itaigitt, http://copypastenet.blogspot.com

Answer 11

תזהר מאוד מהביצועים של קישור שכזה.

אם אין משמעות לביצועים, סבבה.

אם יש - אני ממליץ לך לחפש פתרון אחר..

---

Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

Answer 12

היי עמי, תודה.

אני כמובן אבדוק את עניין הביצועים.

האם יש פתרון אחר?

יצירת ה-PIVOT היא לא הבעיה , אלא החיבור של התוצאה לאובייקטים אחרים. לדוגמא: דוח על ביצועים של מבחן, ואז את עמודת ה-USERID לחבר לנתוני השאילתא על ה-PIVOT. 

את השאילתא במתכונת שהציע גרי שמתי ב-VIEW, ועכשיו היא זמינה לעולם.

---

itaigitt, http://copypastenet.blogspot.com