Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
SQL*XL Won't Run This SQL Script Properly (Read 2867 times)
glennes
YaBB Newbies
*


I Love SQL*XL

Posts: 21
SQL*XL Won't Run This SQL Script Properly
04.06.08 at 15:17:15
 
I can execute the following SQL script fine using other tools, such as Golden32, and it will return the proper result set.
When I run it from SQL*XL inside Excel 2003, it executes with no errors but returns an empty result set.
If I remark out the second ACCEPT statement and also remark out the first WHERE condition which refers to that ACCEPT statement,
SQL*XL will run the script fine and deliver the proper result set.
 
Why does the script run fine in Golden32 but not with SQL*XL?
_________________________
 
ACCEPT SAL_PR_PD_END_DATE PROMPT 'Enter SALARIED PR Period End Date as YYYY/MM/DD:';
ACCEPT CO_ID PROMPT 'Enter Company to Process - NORM or NRSH :';
 
SELECT p.emp_id, l.ded_num, d.descr,f.ss_num, f.ou_id,
      SUM(NVL( l.amt, 0 )),
      p.gross_pay,
      e.pct, f.last_name, f.first_name, f.middle_initial
 
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f
 
WHERE (f.alpha_6 = '&CO_ID' OR f.alpha_6 = '&&CO_ID')
 
   AND l.ded_num = '553'
 
   AND l.ded_num = d.ded_num
   AND l.pr_chk_sa_num = p.pr_chk_sa_num
 
AND ((p.pay_group_num IN ('200','250','325','750')
       AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))
       OR
       (p.prd_end_date = '&&sal_pr_pd_end_date'
       and p.pay_group_num IN ('100','300','400','700')))
 
   AND (p.emp_id = e.emp_id AND e.ded_num = '553')
   AND f.emp_id = p.emp_id
 
 
GROUP BY p.emp_id,f.ss_num, f.ou_id, l.ded_num,
        d.descr,
        p.gross_pay,
        e.pct,
        f.last_name, f.first_name, f.middle_initial
 
 
HAVING SUM(NVL( l.amt, 0 )) <> 0
 
ORDER BY p.emp_id
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQL*XL Won't Run This SQL Script Properly
Reply #1 - 07.06.08 at 17:05:36
 
Hi,
 
The parameter usage is correct although I would have used a single & sign in the SQL. I tested it using this similar SQL:
Code:
accept p_name prompt 'Enter name:';
select * from authors where author like '&&p_name%'; 


 
Perhaps the problem is with your to_char and to_dates. The syntax you use is not correct and may or may not lead to the desired result. Let me explain:
 
This is the part of the SQL that has the problem:
p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))  
OR (p.prd_end_date = '&&sal_pr_pd_end_date'

 
Assuming the prd_end_date column is a true date column you attempt to cast the substitution parameter into a date but in the second usage you do not attempt this at all.
 
When you do this p.prd_end_date = '&&sal_pr_pd_end_date' you basically let SQL*XL put the value of the parameter inside the single quotes and the database will then need to do the evaluation of the expression. It will see that you are comparing a date with a string and it will attempt to either convert the string into a date or to convert the date into a string. It depends on your NLS settings which date format is used and whether the comparison evaluates to a true or false.
 
In the first instance where you compare the parameter and the column you do attempt to make conversions:
p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))
 
The reason that this is not correct is that first  you convert the parameter to a date. The -7 substraction is ok. Then you convert it to a string again, but why?. Eventually you give the database a string and a date to compare. Exactly the same difficulty as I discussed above.
 
I think that what you wanted to use is this:
p.prd_end_date = to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7)  
OR (p.prd_end_date = to_date('&&sal_pr_pd_end_date','YYYY/MM/DD')

 
Now, why some tools do this correctly and SQL*XL does not may be due to the choice of drivers and how that is implemented, whether SQL is pre-parsed, etc. You may want to try a different driver if that gets the behavior inline with other tools. You probably connect through OO4O which has a slightly different implementation that ODBC or OLE DB. Usually for the better, sometimes not...
 
In any case I suggest you get familiar with implicit and explicit conversions.
 
See also:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1132566631
Back to top
 
« Last Edit: 07.06.08 at 17:19:52 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1