فیلد محاسباتی در SQL اکسس؛ آموزش IIf و عبارت‌های محاسباتی

خروجی مفید یک کوئری همیشه فقط ستون‌های ذخیره‌شده جدول نیست. مبلغ ردیف سفارش از ضرب تعداد در قیمت به دست می‌آید، برچسب وضعیت می‌تواند دیرکرد را توضیح دهد و نام نمایشی ممکن است از ترکیب چند ستون ساخته شود. فیلد محاسباتی در SQL اکسس اجازه می‌دهد این خروجی‌ها را بدون افزودن ستون فیزیکی جدید در SELECT ایجاد کنیم.

این آموزش در ادامه SELECT و توابع تجمیعی به عبارت‌های محاسباتی، نام مستعار ستون، IIf و محدودیت‌های شرطی‌سازی می‌پردازد. نکته ظریف آن است که عبارتی با ظاهر محافظت‌شده ممکن است هنگام اجرا خطا دهد. با مثال‌های کنترل‌شده رفتار Null و تقسیم بر صفر را می‌بینیم و نتیجه‌ها را از نظر منطقی بررسی می‌کنیم.

مفهوم و محدوده کاربرد

فیلد محاسباتی مقداری است که کوئری به‌عنوان خروجی می‌سازد و لزوماً در جدول ذخیره نمی‌شود. برای آن نامی مانند LineTotal انتخاب کنید تا فرم و گزارش بتوانند یک ستون پایدار را بخوانند. در طراحی درست، داده پایه در یک محل معتبر ذخیره و محاسبات قابل اشتقاق در کوئری یا لایه مناسب برنامه انجام می‌شوند.

تابع IIf(condition, truepart, falsepart) یکی از دو مقدار را برمی‌گرداند، اما موتور عبارت‌های VBA می‌تواند هر دو عبارت نتیجه را ارزیابی کند؛ حتی اگر یکی انتخاب نشود. بنابراین IIf که در شاخه ظاهراً اجرا‌نشونده تقسیم بر صفر دارد، لزوماً ایمن نیست. این تفاوت با بسیاری از زبان‌های برنامه‌نویسی اهمیت عملی زیادی دارد.

تابع Switch چند جفت شرط و نتیجه را می‌پذیرد و مقدار مربوط به نخستین شرط درست را برمی‌گرداند. برای دسته‌بندی ساده مناسب است، ولی عبارت‌های آن باید قابل ارزیابی و ایمن باشند. اگر قاعده چندین آستانه و استثنا دارد، معمولاً جدول راهنما یا تابع VBA روشن، از یک SELECT طولانی و مبهم قابل نگهداری‌تر است.

داده‌های نمونه و فرض‌ها

جدول OrderDetails با OrderDetailID و OrderID و Quantity و UnitPrice و DiscountRate و جدول Orders با OrderID و DueDate و Status و TotalAmount فرض می‌شود. ردیف‌هایی با تعداد ۲، قیمت ۱۲۵، تعداد صفر، تخفیف Null و در نسخه آزمایشی قیمت منفی بسازید. این نمونه‌ها کمک می‌کنند رفتار عادی و مرزی را بدون آسیب‌زدن به اطلاعات واقعی بررسی کنیم.

ساختار دستور و تفسیر آن

در Access SQL می‌توان عبارت را به صورت expression AS Alias در SELECT نوشت. برای نمونه Quantity * UnitPrice AS GrossAmount یک مبلغ مشتق‌شده می‌سازد. اگر تخفیف اختیاری واقعاً در حالت ثبت‌نشدن صفر محسوب می‌شود، Nz(DiscountRate,0) یک جایگزین صریح فراهم می‌کند. نوع جایگزین و وجود Null در داده اصلی را از یاد نبرید.

برای نام فیلدهای دارای فاصله یا واژه رزروشده از کروشه و برای خروجی از alias معنادار استفاده کنید. اگر در IIf یک شاخه عدد و شاخه دیگر متن می‌دهد، نوع نهایی خروجی را در گزارش و فایل خروجی بررسی کنید. معمولاً بهتر است مبلغ عددی و برچسب وضعیت متنی دو ستون مستقل باشند.

SELECT OrderDetailID, Quantity, UnitPrice,
       Quantity * UnitPrice AS GrossAmount
FROM OrderDetails;

مثال‌های گام‌به‌گام

مثال‌ها از ضرب ساده شروع می‌شوند، سپس برچسب شرطی و در نهایت تخفیف اختیاری را نشان می‌دهند. همه مثال‌ها از نوع SELECT هستند؛ پس مقادیر اصلی جدول را تغییر نمی‌دهند.

محاسبه مبلغ ردیف

در این عبارت تعداد ذخیره‌شده در قیمت ذخیره‌شده ضرب می‌شود. اگر Quantity برابر ۲ و UnitPrice برابر ۱۲۵ باشد، GrossAmount عدد ۲۵۰ است. واحد قیمت و وضعیت مالیات را بررسی کنید؛ ضرب درست با تعریف نادرست داده همچنان مبلغ کسب‌وکار اشتباه می‌سازد.

SELECT OrderDetailID, Quantity * UnitPrice AS GrossAmount FROM OrderDetails;

برچسب‌گذاری با IIf

تابع IIf بر اساس حد آستانه یکی از دو برچسب را برمی‌گرداند. مبلغ دقیقاً هزار در گروه Large قرار می‌گیرد. هر دو شاخه این مثال فقط متن ثابت هستند و خطر عملیاتی ندارند. اگر TotalAmount امکان Null دارد، پیش از ساخت گزارش درباره وضعیت مبلغ نامعلوم تصمیم‌گیری کنید.

SELECT OrderID, TotalAmount, IIf(TotalAmount >= 1000, "Large", "Standard") AS SizeLabel FROM Orders;

اعمال تخفیف اختیاری

در این فرمول نرخ تخفیف بین صفر و یک فرض می‌شود و نرخ Null معادل صفر در نظر گرفته شده است. قبل از استفاده، این سیاست و محدوده معتبر نرخ را کنترل کنید. ذخیره نرخ ۱۵ در کنار نرخ 0.15 باعث می‌شود یک فرمول واحد اعداد کاملاً متفاوت تولید کند.

SELECT OrderDetailID, Quantity * UnitPrice AS GrossAmount,
       Quantity * UnitPrice * (1 - Nz(DiscountRate, 0)) AS NetAmount
FROM OrderDetails;

اجرای کوئری با VBA در اکسس

کد DAO یک snapshot خواندنی از ردیف‌های سفارش باز می‌کند و مبلغ GrossAmount محاسبه‌شده را نمایش می‌دهد. این روال Quantity یا UnitPrice را ویرایش نمی‌کند. برای مبالغ مالی در کاربرد مناسب، نوع Currency نسبت به اعداد اعشاری شناور دقت ثابت و قابل توضیح‌تری دارد.

محاسبه را مستقل از ظاهر گزارش آزمایش کنید. خروجی را با چند ردیف دستی مقایسه و حالت Quantity صفر را بررسی کنید. حتی اگر SQL از نظر Syntax درست باشد، معلوم نیست فرمول با تعریف تخفیف، مالیات یا گردکردن در سامانه شما یکسان باشد. تعریف کسب‌وکار باید کنار کد ثبت شود.

Option Explicit
Public Sub ShowOrderLineAmounts()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    On Error GoTo Fail
    Set db = CurrentDb
    Set rs = db.OpenRecordset( _
        "SELECT OrderDetailID, Quantity, UnitPrice, " & _
        "Quantity * UnitPrice AS GrossAmount " & _
        "FROM OrderDetails ORDER BY OrderDetailID;", dbOpenSnapshot)
    Do Until rs.EOF
        Debug.Print rs!OrderDetailID, rs!GrossAmount
        rs.MoveNext
    Loop
CleanUp:
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
Fail:
    Debug.Print Err.Number, Err.Description
    Resume CleanUp
End Sub

کنترل نتیجه و شرایط مرزی

برای دست‌کم سه ردیف مبلغ را دستی محاسبه و با کوئری مقایسه کنید. یک DiscountRate از نوع Null اضافه و تعیین کنید معنای آن تخفیف صفر است یا تخفیف نامعلوم. پیش از اعمال تخفیف، GrossAmount را جدا کنترل کنید. در برچسب‌گذاری نیز مقادیر پایین‌تر، بالاتر و دقیقاً مساوی آستانه را آزمایش کنید.

خطاهای رایج و راه‌حل

اشتباهات عبارت‌های محاسباتی ممکن است به نوع داده یا ترتیب ارزیابی مربوط باشند. بسیاری از آن‌ها خطای Syntax نمی‌دهند و تنها خروجی نادرست تولید می‌کنند:

بهترین روش‌ها برای کوئری قابل اعتماد

نام‌های GrossAmount و DiscountAmount و NetAmount را مستقل و شفاف انتخاب کنید. وقتی مقادیر میانی دیده شوند، بررسی محاسبه آسان‌تر است. از فرمول تو در تویی که تعداد، قیمت، نرخ، گردکردن و دسته‌بندی را هم‌زمان انجام می‌دهد، تا جای ممکن دوری کنید.

قبل از قرار دادن عملیات خطرناک داخل IIf رفتار ارزیابی را بشناسید. عبارت IIf(Quantity=0,0,Amount/Quantity) محافظ مطمئنی در برابر تقسیم بر صفر نیست، چون هر دو شاخه ممکن است ارزیابی شوند. برای محاسبه ایمن، ردیف نامعتبر را جدا کنید یا در VBA از If واقعی و شاخه‌بندی کنترل‌شده استفاده کنید.

واحد هر ستون را مستند کنید. تخفیف ذخیره‌شده به صورت 0.15 با تخفیف 15 تفاوت اساسی دارد. Currency و درصد و تعداد باید پیش از ضرب معنای مشخصی داشته باشند. اگر فرمول در چند گزارش تکرار می‌شود، کوئری ذخیره‌شده مشترک بسازید و داده آزمون مرزی را نیز نگه دارید.

توابع قالب‌بندی ظاهر عدد را تغییر می‌دهند و نباید جای نوع عددی واقعی را بگیرند. اگر مبلغ را داخل SQL به رشته نمایشی تبدیل کنید، مرتب‌سازی یا جمع‌کردن آن ممکن است نتیجه‌ای متفاوت بدهد. بهتر است مبلغ عددی از SQL خارج و قالب نمایش در فرم یا گزارش اعمال شود.

اگر کوئری چند جدول را JOIN می‌کند، قبل از محاسبه جمع کل، مطمئن شوید هر ردیف سفارش دقیقاً یک‌بار ظاهر می‌شود. رابطه یک‌به‌چند نادرست می‌تواند ردیف‌ها را تکثیر و جمع را چندبرابر کند، در حالی که فرمول ضرب کاملاً صحیح است. تعداد سطرهای قبل و بعد از JOIN را مقایسه کنید.

آزمایشگاه تمرین

کوئری qryOrderLineAmounts بسازید و OrderDetailID و Quantity و UnitPrice و GrossAmount و DiscountAmount و NetAmount را برگردانید. برای سه ردیف، نتیجه مورد انتظار را با دقت متناسب به‌صورت دستی محاسبه کنید. مراقب باشید DiscountRate یک‌جا درصد نسبی و جای دیگر مبلغ ثابت تفسیر نشود.

برای جدول Orders برچسبی بسازید که تأخیر را بر اساس DueDate نشان دهد؛ اما از ابتدا تصمیم بگیرید وقتی تاریخ Null است یا سفارش قبلاً Completed شده چه باید کرد. این حالت‌ها را مستقل آزمایش کنید. اگر تعداد شرایط زیاد شد، قاعده را در تابع روشن یا جدول مستقل نگهداری کنید.

درس‌های مرتبط

درس SELECT خروجی ستون‌ها را توضیح می‌دهد، کوئری ذخیره‌شده امکان استفاده مجدد می‌دهد و درس NULL اثر داده ناموجود بر محاسبه را روشن می‌کند. فیلد محاسباتی باید با این مبانی هماهنگ باشد.

• کوئری پارامتری با QueryDef: کوئری پارامتری در SQL اکسس با QueryDef و VBA
• مرتب‌سازی ORDER BY در اکسس: آموزش مرتب‌سازی نتایج کوئری با ORDER BY در SQL اکسس
• کاربرد WHERE در SQL اکسس: آموزش SQL در Microsoft Access: کاربرد جزء WHERE در SQL

جمع‌بندی

فیلدهای محاسباتی در SQL اکسس گزارش را خواناتر و منطق تکراری را کمتر می‌کنند؛ به شرط آنکه نام مستعار، نوع داده و معنی هر عدد مشخص باشد.

در مورد IIf هیچ‌گاه فرض نکنید شاخه انتخاب‌نشده اجرا نمی‌شود. ابتدا محاسبه ایمن طراحی و سپس خروجی را با مثال دستی، مقدار Null و شرایط مرزی مقایسه کنید.