can someone please tell me what this dql code does.

DECODE
(measureid,
'TOTD', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal),
'CDL', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
- (SELECT NVL (SUM (0.5), 0)
FROM emp_log_entries ele
WHERE empl_emp_id = employeeid
AND ele.empl_status = 'A'
AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
AND ele.empl_trt_id IN ('PH', 'WEND')),
'DNR', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
- (SELECT COUNT (empl_id) / 2
FROM emp_log_entries ele
WHERE empl_emp_id = employeeid
AND ele.empl_status = 'A'
AND ele.empl_origin_id = 'T'
AND ele.empl_date BETWEEN :fecinicial AND :fecfinal),
measurevalue
) measurevalue
Oracle's DECODE is similar in functionality to the ANSI-standard CASE
expression used in SQL Server. Different values are returned depending on
if the measureid value is 'TODT', 'CDL' or 'DNR'.
Hope this helps.
Dan Guzman
SQL Server MVP
"dwynenelson880" <u33162@.uwe> wrote in message news:7067dc28f849e@.uwe...
> DECODE
> (measureid,
> 'TOTD', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal),
> 'CDL', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
> - (SELECT NVL (SUM (0.5), 0)
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
> AND ele.empl_trt_id IN ('PH', 'WEND')),
> 'DNR', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
> - (SELECT COUNT (empl_id) / 2
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_origin_id = 'T'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal),
> measurevalue
> ) measurevalue
>
|||dwynenelson880 wrote:

> 'CDL', ...
> - (SELECT NVL (SUM (0.5), 0)
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
> AND ele.empl_trt_id IN ('PH', 'WEND')),
>
my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
correct?
because i think that the result will aways be 0.5
if someone can explain this to me it would be very helpfull.
thank you
|||> my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
> correct?
> because i think that the result will aways be 0.5
If no rows satisfy the WHERE clause, the SUM(0.5) aggregate function will
return NULL. I think the purpose of NVL here is to return 0 instead of
NULL. In SQL Server, you can use the ANSI standard COALESCE function for
this purpose.
For questions are specific to Oracle, you are better off posting to an
Oracle forum. However, we might be able to help you convert Oracle code to
SQL Server here.
Hope this helps.
Dan Guzman
SQL Server MVP
"dwynenelson880" <u33162@.uwe> wrote in message news:70683db28c33e@.uwe...
> dwynenelson880 wrote:
>
> my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
> correct?
> because i think that the result will aways be 0.5
> if someone can explain this to me it would be very helpfull.
> thank you
>
|||>If no rows satisfy the WHERE clause, the SUM(0.5) aggregate function will
>return NULL. I think the purpose of NVL here is to return 0 instead of
>NULL. In SQL Server, you can use the ANSI standard COALESCE function for
>this purpose.
>
thank you very much Dan
i finaly understand it now. men that was really helpfull. surry for posting
in the sql forum. next time i will know better.