RE: [sqr-users] SQL Null date

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

I think Betty is right that it might be the & variable, if it's the isnull() 
that's causing you problems.

But you are probably also going to have a problem with what you're setting the 
where clause to...when you put all of that into a text variable the $month is 
no longer going to be read as an SQR variable, but rather a SQL something (and 
SQL will get very confused by the $).

let $where5 = 'and datepart(MM,reportdate) = ''' 
              || $month 
              || ''' and datepart(YYYY,reportdate) = ''' 
              || $year || ''''


are you saying 'datepart()' is an SQR procedure?  because then you dont want it 
in the middle of the string either...but i'm not typing that out.


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Thompson, Betty P
Sent: Thursday, September 15, 2005 2:00 PM
To: This list is for discussion about the SQR database reportinglanguage
from Hyperion Solutions.
Subject: RE: [sqr-users] SQL Null date



Perhaps I'm not grasping exactly what problem you're having...

In the code you showed:

  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

You aren't saying the 'datepart' function is failing (it's simply text
being included in an assignment to variable $where5, right?); you're
saying the "isnull(&targstartdate3)" is yielding invalid results --
correct? 

If this is the case, I think the issue might be related to using a host
variable instead of an SQR variable.  Why don't you try doing an
assignment first (such as "Let $targstartdate3 = &targstartdate3"), and
then use the 'isnull' function on the SQR variable? 

 

 

-----Original Message-----
From: sqr-users-bounces+thompsob=uww.edu@sqrug.org
[mailto:sqr-users-bounces+thompsob=uww.edu@sqrug.org] On Behalf Of
McCown, Larry
Sent: Thursday, September 15, 2005 1:48 PM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: RE: [sqr-users] SQL Null date

I've been using it for a couple of years.  It usually works just fine.
I think Wes was the one who told me about it. 

-----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
Knapp, Richard
Sent: Thursday, September 15, 2005 11:12 AM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: RE: [sqr-users] SQL Null date


Is datepart() a new SQR function?

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu

-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
McCown, Larry
Sent: Thursday, September 15, 2005 10:46 AM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: RE: [sqr-users] SQL Null date

Hello it's me again.
Looks like the if isnull(&targstartdate3) worked in one are a but it
doesn't appear to be working on the below.  I am attempting to count the
number of requests within the month and year.
Any suggestions.

       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

Thanks in advance
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
McCown, Larry
Sent: Wednesday, September 14, 2005 9:34 AM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: RE: [sqr-users] SQL Null date

Thanks Clark, Roger and Wes for the quick response. 

-----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
Wes Williams
Sent: Wednesday, September 14, 2005 9:19 AM
To: This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.
Subject: RE: [sqr-users] SQL Null date

How about
if isnull(&targstartdate3) = 1

returns 1 if it is null 0 if it isn't

Wes

Wes Williams
Manager Facilities Mgt. Systems
Princeton University
609-258-7027
 

-----Original Message-----
From: sqr-users-bounces+wes=princeton.edu@sqrug.org
[mailto:sqr-users-bounces+wes=princeton.edu@sqrug.org] On Behalf Of
McCown, Larry
Sent: Wednesday, September 14, 2005 12:11 PM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: [sqr-users] SQL Null date

 
Greetings All,
I have a date field that I need to do a if then statement with based on
if there is any data in this field.
Tried:
        if &targstartdate3 is null
        if &targstartdate3 null 
        if &targstartdate3 = ' '
        if &targstartdate3 = ''  This one appears to work but is it the
proper way to look for a blank date field?
Any Suggestions?
Thanks
Larry

_______________________________________________
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

_______________________________________________
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

_______________________________________________
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