[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

RE: [sqr-users] SQL Null date

i understand.  you're trying to create a dynamic where clause that could either 
have targetstartdate or reportdate.  The problem I'm saying is that when you 
create a dynamic clause as you did, you're not using the SQR variable.  Instead 
you're using the character string that is the variable name.

 let $where = '$month'  
is very different than 
 let $where = $month

The 2nd one will use $month as an assigned varaible (something like '09' for 
example), and the 1st one will be the dollar sign and the letters m-o-n-t-h 
after it in a character string.

if you go to a screen where you can run regular old SQL (not SQR, just sql) and 
type in

SELECT * 
FROM MYTABLE
WHERE datepart(MM,mydate) = '09'

and 

SELECT *
FROM MYTABLE
WHERE datepart(MM,mydate) = $month

I'd bet some good money that the 2nd one would fail. The database will look at 
you funny and say "what in the heck is a $ supposed to mean?  I'm expecting a 
string like 01 or 12 and you give me a $?" 

That's what you're doing when you set your variable like this : 

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

the way I wrote it with the ||s will subsititue that '09', so that the SQR will 
ask the database "and datepart(MM, mydate) = '09'" and actually run the SQL.



-----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: Thursday, September 15, 2005 2:33 PM
To: This list is for discussion about the SQR database reportinglanguage
from Hyperion Solutions.
Subject: RE: [sqr-users] SQL Null date


I have other reports that are using the datepart within the where
statement ie.   
    and datepart(mm,actfinish) = $month  
    and datepart(YYYY,actfinish) = $year  

And they work just fine.

The difference here is that I need to check multiple dates. if there is
a targstartdate I want to use it else I need the reportdate.
Thanks for the input I am trying them as they come in, but haven't got
it yet.
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
Thompson, Betty P
Sent: Thursday, September 15, 2005 12:17 PM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: RE: [sqr-users] SQL Null date


'Good catch!  I think you're absolutely right:  $month and $year would
never resolve as variables the way I'd mentioned them. 

 

-----Original Message-----
From: sqr-users-bounces+thompsob=uww.edu@sqrug.org
[mailto:sqr-users-bounces+thompsob=uww.edu@sqrug.org] On Behalf Of Bob
Stone
Sent: Thursday, September 15, 2005 2:08 PM
To: This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.
Subject: RE: [sqr-users] SQL Null date

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

_______________________________________________
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