RE: [sqr-users] RE: SQL Null date (McCown, Larry)

["Bob Stone" <bstone@fastenal.com>]

Larry,

you can't set a varaible you want to use in a SQL statement WITHIN THE SQL 
STATEMENT ITSELF.  

the SQL is returning rows, and you're trying to change the statement that was 
already run (AFTER its run) for each new row it returns (even though they were 
already returned by the SQL)...that doesn't make any sense whatsoever.  I can't 
even explain how it doesn't make sense.

I don't know what you're trying to do there...but your logic shouldn't be like 
that.

Talk it out to yourself, and you'll probably see what sort of nesting/checking 
you need to be doing.  "first I need to grab a date to find which one to use.  
Then for valid dates I need to grab soemthing from the workorder table."  ...in 
which case you'd find the dates (maybe in it's own SQL) and then for valid 
dates (some if statements) do some procedure with the select from workorder 
(and the where clause can be set with the dates returned in the first part).  

Or maybe the logic can be built into the SQL itself? 
"where ( ( datepart(MM,targstartdate) = $month 
           AND datepart(YYYY,targstartdate) = $year )
       OR
         ( targstartdate is null 
           AND ( datepart(MM,reportdate) = $month 
                 AND datepart(YYYY,reportdate) = $year )  
        )

Look at it another way.  The dyanmic where clause only works when the variable 
is set BEFORE the SQL you try to use it in.

bob





-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
McCown, Larry
Sent: Friday, September 16, 2005 3:29 PM
To: This list is for discussion about the SQR database reportinglanguage
from Hyperion Solutions.
Subject: RE: [sqr-users] RE: SQL Null date (McCown, Larry)


In dealing with this all week I finally smartened up and decided to add
a print statement.  The results show that the $where5 is correct but the
output is still showing workorder outside the date range.
Wonum           reportdate              targstartdate           location
$where5
321626 Jun 12 2003 12:21PM                                      BETHPAGE
and datepart(MM,reportdate) = '08' and datepart(YYYY,reportdate) =
'2005'
329877 Aug 01 2003 05:48AM      Aug 04 2003 08:56AM     BETHPAGE
and datepart(MM,targstartdate) = '08' and datepart(YYYY,targstartdate) =
'2005'

CODE:
  
begin-select
wonum           &wonum5             (+1,1)
reportdate      &reportdate        (,+1)    
targstartdate   &targstartdate     (,+1)
location        &location3          (,+1)
   if isnull(&targstartdate)
            let $where5 = 'and datepart(MM,reportdate) = ''' || $month
||
                     ''' and datepart(YYYY,reportdate) = ''' || $year ||
''''
        else
            let $where5 = 'and datepart(MM,targstartdate) = ''' ||
$month ||
                 ''' and datepart(YYYY,targstartdate) = ''' || $year ||
''''
        end-if
        print $where5           (,+1)
        add 1 to #opencount
    add 1 to #opensubcount
        from workorder
        where [$where]
          [$where2]
         [$where5]
     and workorder.status != 'CAN'
    and worktype not in ('PM','TR','AFP')
end-select
end-procedure


Any suggestions?
Thank you to all.
Larry
 

-----Original Message-----
From: sqr-users-bounces+larry.mccown=ngc.com@sqrug.org
[mailto:sqr-users-bounces+larry.mccown=ngc.com@sqrug.org] On Behalf Of
Ed Kelly
Sent: Friday, September 16, 2005 9:19 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] RE: SQL Null date (McCown, Larry)

The problem is, as mentioned earlier, that the strings $month and $year,
are being passed to the RDBMS, not their values.  The correct syntax for
your IF statement would be as follows:

if isnull(&targstartdate3)
   let $where5 = 'and datepart(MM,reportdate) = ''' || $month ||
                 ''' and datepart(YYYY,reportdate) = ''' || $year ||
''''
else
   let $where5 = 'and datepart(MM,targstartdate) = ''' || $month ||
                 ''' and datepart(YYYY,targstartdate) = ''' || $year ||
''''
end-if

...........

And by the way folks, datepart() is a database function, NOT and SQR
function.



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users