问Oracle SQL语句、嵌套语句和运算符(万一)

EN

/*TRANSPORT UNIT*/
define tu_len = 564;
define tu_wid = 366;
define tu_hgt = 302;
define tu_wgt = 16;
define tu_min = 3;

/*CONVEYABLE MAX*/
define cv_lenmn = 620;
define cv_widmn = 545;
define cv_hgtmn = 500;
define cv_wgtmn = 0;

/*CONVEYABLE MIN*/
define cv_lenmx = 240;
define cv_widmx = 180;
define cv_hgtmx = 100;
define cv_wgtmx = 16;

/*TEST CUBI*/
define len = 564;
define wid = 366;
define hgt = 302;
define wgt = 16;

define len2 = 620;
define wid2 = 545;
define hgt2 = 500;
define wgt2 = 16;


SELECT
case 
    when len1 <= &tu_len  and wid1 <= &tu_len   and hgt1 <= &tu_len then 'LWH' 
    when len1 <= &tu_len  and wid1 <= &tu_len   and hgt1 > &tu_len then 'LW' 
    when len1 <= &tu_len  and wid1 > &tu_len    and hgt1 <= &tu_len then 'LH' 
    when len1 > &tu_len   and wid1 <= &tu_len   and hgt1 <= &tu_len then 'WH' 
    when len1 <= &tu_len  and wid1 > &tu_len    and hgt1 > &tu_len then 'L'
    when len1 > &tu_len   and wid1 <= &tu_len   and hgt1 > &tu_len then 'W' 
    when len1 > &tu_len   and wid1 > &tu_len    and hgt1 <= &tu_len then 'H' 
    else 'X' end as LENC1,
case 
    when len1 <= &tu_wid  and wid1 <= &tu_wid   and hgt1 <= &tu_wid then 'LWH' 
    when len1 <= &tu_wid  and wid1 <= &tu_wid   and hgt1 > &tu_wid then 'LW' 
    when len1 <= &tu_wid  and wid1 > &tu_wid    and hgt1 <= &tu_wid then 'LH' 
    when len1 > &tu_wid   and wid1 <= &tu_wid   and hgt1 <= &tu_wid then 'WH' 
    when len1 <= &tu_wid  and wid1 > &tu_wid    and hgt1 > &tu_wid then 'L'
    when len1 > &tu_wid   and wid1 <= &tu_wid   and hgt1 > &tu_wid then 'W' 
    when len1 > &tu_wid   and wid1 > &tu_wid    and hgt1 <= &tu_wid then 'H' 
    else 'X' end  as WIDC1,
case 
    when len1 <= &tu_hgt  and wid1 <= &tu_hgt   and hgt1 <= &tu_hgt then 'LWH' 
    when len1 <= &tu_hgt  and wid1 <= &tu_hgt   and hgt1 > &tu_hgt then 'LW' 
    when len1 <= &tu_hgt  and wid1 > &tu_hgt    and hgt1 <= &tu_hgt then 'LH' 
    when len1 > &tu_hgt   and wid1 <= &tu_hgt   and hgt1 <= &tu_hgt then 'WH' 
    when len1 <= &tu_hgt  and wid1 > &tu_hgt    and hgt1 > &tu_hgt then 'L'
    when len1 > &tu_hgt   and wid1 <= &tu_hgt   and hgt1 > &tu_hgt then 'W' 
    when len1 > &tu_hgt   and wid1 > &tu_hgt    and hgt1 <= &tu_hgt then 'H' 
    else 'X' end  as HGTC1,
case
    when wgt1 <= &tu_wgt then 'P'
    else 'F' end as WGTC1,
case  /*DOES IT FIT WITHIN 240 AND 620*/
    when (len2 >= &cv_lenmn and len2 <= &cv_lenmx)      and (wid2 >= &cv_lenmn and wid2 <= &cv_lenmx)       and (hgt2 >= &cv_lenmn and hgt2 <= &cv_lenmx)   then 'LWH' 
    when (len2 >= &cv_lenmn and len2 <= &cv_lenmx)      and (wid2 >= &cv_lenmn and wid2 <= &cv_lenmx)       and (hgt2 < &cv_lenmn or hgt2 > &cv_lenmx)      then 'LW' 
    when (len2 >= &cv_lenmn and len2 <= &cv_lenmx)      and (wid2 < &cv_lenmn or wid2 > &cv_lenmx)          and (hgt2 >= &cv_lenmn and hgt2 <= &cv_lenmx)   then 'LH' 
    when (len2 < &cv_lenmn or len2 > &cv_lenmx)         and (wid2 >= &cv_lenmn and wid2 <= &cv_lenmx)       and (hgt2 >= &cv_lenmn and hgt2 <= &cv_lenmx)   then 'WH' 
    when (len2 >= &cv_lenmn and len2 <= &cv_lenmx)      and (wid2 < &cv_lenmn or wid2 > &cv_lenmx)          and (hgt2 < &cv_lenmn or hgt2 > &cv_lenmx)      then 'L'
    when (len2 < &cv_lenmn or len2 > &cv_lenmx)         and (wid2 >= &cv_lenmn and wid2 <= &cv_lenmx)       and (hgt2 < &cv_lenmn or hgt2 > &cv_lenmx)      then 'W' 
    when (len2 < &cv_lenmn or len2 > &cv_lenmx)         and (wid2 < &cv_lenmn or wid2 > &cv_lenmx)          and (hgt2 >= &cv_lenmn and hgt2 <= &cv_lenmx)   then 'H' 
    else 'X' end as LENC2,                                      
case /*DOES IT FIT WITHIN 180 AND 545*/                                         
    when (len2 >= &cv_widmn and len2 <= &cv_widmx)      and (wid2 >= &cv_widmn and wid2 <= &cv_widmx)       and (hgt2 >= &cv_widmn and hgt2 <= &cv_widmx)   then 'LWH' 
    when (len2 >= &cv_widmn and len2 <= &cv_widmx)      and (wid2 >= &cv_widmn and wid2 <= &cv_widmx)       and (hgt2 < &cv_widmn or hgt2 > &cv_widmx)      then 'LW' 
    when (len2 >= &cv_widmn and len2 <= &cv_widmx)      and (wid2 < &cv_widmn or wid2 > &cv_widmx)          and (hgt2 >= &cv_widmn and hgt2 <= &cv_widmx)   then 'LH' 
    when (len2 < &cv_widmn or len2 > &cv_widmx)         and (wid2 >= &cv_widmn and wid2 <= &cv_widmx)       and (hgt2 >= &cv_widmn and hgt2 <= &cv_widmx)   then 'WH' 
    when (len2 >= &cv_widmn and len2 <= &cv_widmx)      and (wid2 < &cv_widmn or wid2 > &cv_widmx)          and (hgt2 < &cv_widmn or hgt2 > &cv_widmx)      then 'L'
    when (len2 < &cv_widmn or len2 > &cv_widmx)         and (wid2 >= &cv_widmn and wid2 <= &cv_widmx)       and (hgt2 < &cv_widmn or hgt2 > &cv_widmx)      then 'W' 
    when (len2 < &cv_widmn or len2 > &cv_widmx)         and (wid2 < &cv_widmn or wid2 > &cv_widmx)          and (hgt2 >= &cv_widmn and hgt2 <= &cv_widmx)   then 'H' 
    else 'X' end  as WIDC2,                             
case /*DOES IT FIT WITHIN 100 AND 500*/                                         
    when (len2 >= &cv_hgtmn and len2 <= &cv_hgtmx)      and (wid2 >= &cv_hgtmn and wid2 <= &cv_hgtmx)       and (hgt2 >= &cv_hgtmn and hgt2 <= &cv_hgtmx)   then 'LWH' 
    when (len2 >= &cv_hgtmn and len2 <= &cv_hgtmx)      and (wid2 >= &cv_hgtmn and wid2 <= &cv_hgtmx)       and (hgt2 < &cv_hgtmn or hgt2 > &cv_hgtmx)      then 'LW' 
    when (len2 >= &cv_hgtmn and len2 <= &cv_hgtmx)      and (wid2 < &cv_hgtmn or wid2 > &cv_hgtmx)          and (hgt2 >= &cv_hgtmn and hgt2 <= &cv_hgtmx)   then 'LH' 
    when (len2 < &cv_hgtmn or len2 > &cv_hgtmx)         and (wid2 >= &cv_hgtmn and wid2 <= &cv_hgtmx)       and (hgt2 >= &cv_hgtmn and hgt2 <= &cv_hgtmx)   then 'WH' 
    when (len2 >= &cv_hgtmn and len2 <= &cv_hgtmx)      and (wid2 < &cv_hgtmn or wid2 > &cv_hgtmx)          and (hgt2 < &cv_hgtmn or hgt2 > &cv_hgtmx)      then 'L'
    when (len2 < &cv_hgtmn or len2 > &cv_hgtmx)         and (wid2 >= &cv_hgtmn and wid2 <= &cv_hgtmx)       and (hgt2 < &cv_hgtmn or hgt2 > &cv_hgtmx)      then 'W' 
    when (len2 < &cv_hgtmn or len2 > &cv_hgtmx)         and (wid2 < &cv_hgtmn or wid2 > &cv_hgtmx)          and (hgt2 >= &cv_hgtmn and hgt2 <= &cv_hgtmx)   then 'H'  
    else 'X' end  as HGTC2,
case
    when wgt2 <= &cv_wgtmx then 'P'
    else 'F' end as WGTC2
FROM (select &len as len1,&wid as wid1,&hgt as hgt1,&wgt as wgt1,&len2 as len2,&wid2 as wid2,&hgt2 as hgt2,&wgt2 as wgt2 from DUAL );