הביטוי לעמודה משקל (שהיא גם עמודה מחושבת 45):
PARTPARAM.WEIGHT * ORDERITEMS.TQUANT / 100.0
הביטוי לעמודה משטחים:
ORDERITEMS.TQUANT / 100 * (#70 <= 0 ? 0 : ( 1.0 / #70))
כאשר העמודה המחושבת 70 היא:
(FAMILY.FAMILYNAME LIKE '2__' ?
(FAMILY.FAMILYNAME LIKE '210' ? 456 :
(FAMILY.FAMILYNAME LIKE '220' ?
(PART.PARTNAME LIKE '22_______16____' ? 736 :
(PART.PARTNAME LIKE '22_______08____' ? 736 :
(PART.PARTNAME LIKE '22_______20____' ? 800 :
(PART.PARTNAME LIKE '22_______196___' ? 784 : -1)))) :
(FAMILY.FAMILYNAME LIKE '230' ? 2484 :
(FAMILY.FAMILYNAME LIKE '240' ?
(PART.PARTNAME LIKE '24_______50____' ? 600 :
(PART.PARTNAME LIKE '24_______60____' ? 600 : -1)) : -1)))) :
(FAMILY.FAMILYNAME LIKE '4__' ?
(FAMILY.FAMILYNAME LIKE '41_' ?
(FAMILY.FAMILYNAME LIKE '410' ? 14400 :
(FAMILY.FAMILYNAME LIKE '411' ? 12000 :
(FAMILY.FAMILYNAME LIKE '412' ? 17280 :
(FAMILY.FAMILYNAME LIKE '413' ?
(PART.PARTNAME LIKE '413____1125%' ? 18000 :
(PART.PARTNAME LIKE '413____1200%' ? 19200 :
(PART.PARTNAME LIKE '413____1320%' ? 21120 : -1))) : -1)))) :
(ORDERITEMS.TQUANT * 5 / #45)) :
(FAMILY.FAMILYNAME LIKE '6__' ? (ORDERITEMS.TQUANT * 5 / #45) :
(FAMILY.FAMILYNAME LIKE '530' ? 23000 :
(FAMILY.FAMILYNAME LIKE '520' ? 12000 :
-1)))))
העמודה הנוספת שאני מנסה להוסיף:
(FAMILY.FAMILYNAME LIKE '2__' ?
(FAMILY.FAMILYNAME NOT LIKE '250' ? '`
סטרץ
' :
'לוחות'
) : (FAMILY.FAMILYNAME LIKE '4__' ?
(FAMILY.FAMILYNAME LIKE '41_' ?
'סלסלות'
: (FAMILY.FAMILYNAME LIKE '42_' ?
'מצעים'
:
'יריעות'
)) : (FAMILY.FAMILYNAME LIKE '6__' ?
'לוחות'
: (FAMILY.FAMILYNAME LIKE '5__' ?
(FAMILY.FAMILYNAME NOT LIKE '510' ?
'מגשים'
:
'גלילים'
) : (FAMILY.FAMILYNAME LIKE '3__' ?
(FAMILY.FAMILYNAME NOT LIKE '330' ?
'מגשים'
:
'סרטים'
) :
(FAMILY.FAMILYNAME LIKE '710' ?
'לוחות'
: ''))))))
הדו"ח בלי התוספת עמודה:
/*
*
* Report SPIR_SALESAGNTCSTFML : ומשפחה לקוח ,סוכן לפי הזמנות
*
*/
/* הזמנות לפי סוכן, לקוח ומשפחה */
SELECT ORDERS.CURDATE AS 'תאריך',
AGENTS.AGENTNAME AS 'סוכן שם',
CUSTOMERS.CUSTDES AS 'לקוח שם',
ORDERS.ORDNAME AS 'הזמנה',
FAMILY.FAMILYDES AS 'משפחה תאור',
SUM((0.0 + (PARTPARAM.WEIGHT * ORDERITEMS.TQUANT / 100.0))) AS 'משקל',
SUM((0.0 + (ORDERITEMS.TQUANT / 100 * (((FAMILY.FAMILYNAME LIKE '2__'
? (FAMILY.FAMILYNAME LIKE '210' ? 456 : (FAMILY.FAMILYNAME LIKE '220' ?
(PART.PARTNAME LIKE '22_______16____' ? 736 : (PART.PARTNAME LIKE '22_______08____'
? 736 : (PART.PARTNAME LIKE '22_______20____' ? 800 : (PART.PARTNAME LIKE
'22_______196___' ? 784 : -1)))) : (FAMILY.FAMILYNAME LIKE '230' ? 2484
: (FAMILY.FAMILYNAME LIKE '240' ? (PART.PARTNAME LIKE '24_______50____'
? 600 : (PART.PARTNAME LIKE '24_______60____' ? 600 : -1)) : -1)))) : (FAMILY.FAMILYNAME
LIKE '4__' ? (FAMILY.FAMILYNAME LIKE '41_' ? (FAMILY.FAMILYNAME LIKE '410'
? 14400 : (FAMILY.FAMILYNAME LIKE '411' ? 12000 : (FAMILY.FAMILYNAME LIKE
'412' ? 17280 : (FAMILY.FAMILYNAME LIKE '413' ? (PART.PARTNAME LIKE '413____1125%'
? 18000 : (PART.PARTNAME LIKE '413____1200%' ? 19200 : (PART.PARTNAME LIKE
'413____1320%' ? 21120 : -1))) : -1)))) : (ORDERITEMS.TQUANT * 5 / (PARTPARAM.WEIGHT
* ORDERITEMS.TQUANT / 100.0))) : (FAMILY.FAMILYNAME LIKE '6__' ? (ORDERITEMS.TQUANT
* 5 / (PARTPARAM.WEIGHT * ORDERITEMS.TQUANT / 100.0)) : (FAMILY.FAMILYNAME
LIKE '530' ? 23000 : (FAMILY.FAMILYNAME LIKE '520' ? 12000 : -1)))))) = 210 )
AND ( ATOI( FAMILY.FAMILYNAME ) < 810 ))
AND (DUMMY.DUMMY =1
AND ORDSTATUS.ORDSTATUSDES NOT LIKE 'טיוטא'
AND ORDSTATUS.ORDSTATUSDES NOT LIKE '%מבוטל')
AND (PART.FAMILY = FAMILY.FAMILY)
AND (ORDERITEMS.PART = PART.PART)
AND (ORDERS.ORDSTATUS = ORDSTATUS.ORDSTATUS)
AND (ORDERS.AGENT = AGENTS.AGENT)
AND (ORDERS.CUST = CUSTOMERS.CUST)
AND (ORDERS.ORD = ORDERITEMS.ORD)
AND (ORDERS.CURDATE BETWEEN :FDT
AND :TDT)
AND ( 1=1 )
GROUP BY 1, 2, 3, 4, 5
ORDER BY 1 ASC, 2 ASC, 3 ASC, 5 ASC ;
