Case study of CASE in DB2 - ilDBA Portal

Case study of CASE in DB2

04/07/2011 | פורסם על ידי

אופציית CASE ב-DB2 אינה חדשה, ובכל זאת השימוש בה אינו רווח.
אופציה זו מאפשרת לנו שליפות אלגנטיות ויפות, ומקלה על העבודה.

ראשית, מהו CASE ?

אופציית CASE מאפשרת לנו לקבל ערכים שונים בהתאם לתוכן הSELECT בעת השליפה.
כלומר, אחרי ששלפנו לפי תנאי ה-WHERE, אנחנו עדיין רוצים להחליט אם להשתמש בערך אחד או אחר – בהסתמך על מה ששלפנו.

בספר זה נראה כך:

CASE WHEN <search-condition> THEN <result-expression>
ELSE <result-expression>
END

בכל שימוש יכולים להופיע כמה וכמה תנאי WHEN. תנאי החיפוש הם לרוב השוואה של משתנה או עמודה כלשהם, ואילו התוצאה תהיה ערך, עמודה, או NULL.
אפשר להשתמש בCASE כערך גם בSELECT Clause וגם ב-WHERE Clause.

בעצם, חלק ממנגנון הCASE כבר מוכר לנו מפונקציות אחרות, כמו COALESCE (שנקראת גם VALUE או IFNULL). פונקציה זו מחזירה את הערך הראשון מרשימת ערכים שאיננו NULL. אפשר לתרגם אותה כך:

COALESCE(E1, E2, E3)   is equivalent to:

CASE WHEN E1 IS NOT NULL THEN E1
     WHEN E2 IS NOT NULL THEN E2
     ELSE E3
END

רצוי, כמובן, לתת שם לעמודת CASE כשאנו משתמשים בה בעזרת AS colname.

במאמר זה אדגים  שימושים יפים ב-CASE, שמקלים על הגישה לDB2 ויוצרים שליפות אלגנטיות ויעילות .

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

CASE STUDY מספר 1

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

EMPNO
PROJCODE
WORKDATE
HOURS

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

השליפה הראשונית היתה בעצם שליפה פשוטה שחזרה 3 פעמים, כל פעם עם תאריך אחר:

SELECT EMPNO, PROJCODE, SUM(HOURS)
FROM    TBHOURS
WHERE WORKDATE = :HV-WORKDATE
GROUP BY EMPNO, PROJCODE

ברשת מצאנו גישה ששמה "Crosstab Select" – ואותה יישמנו. לאחר הוספת ה-CASE בנינו את השליפה הבאה:

SELECT EMPNO,
       PROJCODE,
       SUM(CASE WHEN WORKDATE = CURRENT DATE THEN HOURS
                ELSE 0
       END) AS TODAY_HOURS ,
       SUM(CASE WHEN WORKDATE = CURRENT DATE – 1 DAY THEN HOURS
                ELSE 0
       END) AS YESTERDAY_HOURS ,
       SUM(CASE WHEN WORKDATE = CURRENT DATE – 2 DAYS THEN HOURS
                ELSE 0
       END) AS DAY_B4_HOURS ,
FROM TBHOURS
AND WORKDATE > CURRENT DATE - 3 DAYS
GROUP BY EMPNO, PROJCODE

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

CASE STUDY מספר 2

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

אנחנו רוצים לשלוף את הסטודנט והציון שלו, אבל עבור הקורס החיצוני – אנחנו רוצים את המידע העדכני.
ואנחנו מעוניינים רק בשנה"ל הנוכחית.

העמודות בטבלת הסטודנטים:

STUDENTNAME
STUDENTID
COURSEID
MARK
ACCYEAR
SEMESTER

העמודות (הרלוונטיות לנו) בטבלא החיצונית:

STUDENTID
MARK

השליפה המקורית נראתה כך:

SELECT STUDENTNAME, STUDENTID, COURSEID, MARK
FROM TBSTUDMARK
WHERE COURSEID <> ‘EXT10’
AND       ACCYEAR = YEAR(CURRENT DATE)
UNION ALL
SELECT S.STUDENTNAME, S.STUDENTID, S.COURSEID, E.MARK
FROM TBSTUDMARK S 
LEFT OUTER JOIN EXTCOURSE E
    ON S.STUDENTID = E.STUDENTID
WHERE S.COURSEID = ‘EXT10’
AND      ACCYEAR = YEAR(CURRENT DATE)

לאחר הוספת הCASE, חסכנו את הגישה הנוספת לטבלא:

SELECT S.STUDENTNAME, S.STUDENTID, S.COURSEID,
CASE WHEN S.COURSEID = ‘EXT10’ THEN E.MARK
     ELSE   S.MARK
END  AS MARK
FROM TBSTUDMARK S
LEFT OUTER JOIN EXTCOURSE E
    ON S.STUDENTID = E.STUDENTID
WHERE S.ACCYEAR = YEAR(CURRENT DATE)

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

Case Study מספר 3

נתונה טבלת עובדים בפרוייקטים  – TBPROJ – שכוללת, בין השאר את העמודות:

EMPNO
PROJCODE
SUBPROJ

עלינו לשלוף את כל המידע עבור עובד (שמספרו נמצא ב-Host Variable), בהקשר לפרוייקט A01 או לתת-פרוייקט 100 של פרוייקט B01.

יש אינדקס על שדות

PROJCODE
EMPNO

השליפה המקורית  נראתה כך:

SELECT *
FROM TBPROJ
WHERE
(EMPNO = :HVEMP AND PROJCODE = ‘A01’)
OR
(EMPNO = :HVEMP AND PROJCODE = ‘B01’ AND SUBPROJ = ‘100’)

בגלל ה-OR והסדר ההפוך של האינדקס, השליפה הזו עשתה Tablespace scan. לאחר השינוי זה נראה כך:

SELECT *
FROM TBPROJ
WHERE EMPNO = :HVEMP
AND PROJCODE IN (‘A01’,’B01’)
AND (CASE WHEN PROJCODE = ‘B01’ THEN SUBPROJ
          ELSE ‘100’
     END )   = ‘100’

כעת הגישה היא לפי האינדקס, עם matchcols 2.

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

לסיכום

שימוש באופציית CASE יכול לצמצמם ולייעל שימוש במשפטי SQL כאשר השליפה מסתמכת על ערך מתוך הנתונים.

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

לתאור האופציה בספרות DB2:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/psmcse.htm

נתראה ברשומה הבאה,
יעל אורגד, DBA DB2  בכירה
עם 22 שנות נסיון במערכות Mainframe, בעיקר בתחום הפיננסי.
ניתן לפנות אלי במייל yael.orgad@gmail.com
The following two tabs change content below.

יעל אורגד

Latest posts by יעל אורגד (see all)

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

שחזור טבלה באמצעות RMAN

בפוסט זה אציג פיצ'ר שאורקל הוסיפו בגרסת  12c (12.1.0.1 ליתר דיוק) – שחזור טבלה\טבלאות לנקודת זמן מסוימת באמצעות RMAN. הפיצ'ר יכול להיות שימושי במקרים הבאים: נמחק או השתנה המידע בטבלה ולא ניתן להשתמש [...]
PL/SQL

PL/SQL Injection – The Beginning

עודד רז פותח בסדרת מאמרים על Pl/sql [...]

תיעוד בסיס הנתונים – למה זה טוב?

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

צבעים דינמיים ב-SSRS

כאשר לקוח שלי ולא משנה אם הוא מבוסס אורקל או SQL Server חושב על פתרון Reporting אני ישר מציע לו את ה- Sql Server Reporting Services. שתי סיבות עיקריות להצעה שלי: רישוי ויכולות. איתי בנימין מסביר על צבעים דינמים [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: