RE: [sqr-users] Virtual Table

["Everett Carr" <ecarr@holycross.edu>]

SQR / SQL may be having difficulty parsing the imbedded sqr variable
because the syntax "expects" to find single quotes.

    AND N_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,$AsOfDate,121), 1, 10)


So instead perhaps
let $quote_ch = chr(39)  ! Single Quote
let $sql_clause = 'substring(convert(Char~' || $AsOfDate 
|| '~,121),1, 10)'
let $sql_clause = translate($sql_clause,'~', $quote_ch)

    AND N_ED.EFFDT <= [Sql_clause]

will get the needed sql syntax passed 



Instead you must setup a dynaminc clause 
>>> bstone@fastenal.com 05/10/05 10:54 AM >>>
It's called an inline view.  Making a 'fake table' or 'virtual table' in
the
from clause of the select is an inline view.

I'm not sure why SQR is having this problem, but I know I've used
variables
in an inline view in SQR and it's worked.  I'm wondering if maybe the
functions plus the variable (and being within the inline view) are
what's
causing the problem.   (just an idea...i dont know for sure).

We use oracle, but I think a similar principal will work in MS SQL
Server.
The default date type conversion of a string will happen in Oracle when
the
string variable is in the form 'DD-MON-YYYY' or 'DD-MON-YY', so if you
did a
little SQR variable conversion (or SQL edit mask if you're grabbing that
date from a table) to set up $AsOfDate like 'DD-MON-YYYY' (or whatever
the
default string auto-date conversion type for SQL Server is) and then
just
said " AND N_ED.EFFDT <= $AsOfDate " it might go through.  

Otherwise, if that doesn't work, you might have to figure out some other
way
to write the SQL itself...maybe loop through one table on the name, set
a
variable, and within that SQL block call a procedure to loop through the
other tables, using that name variable instead of a table join.  Might
be
slightly less efficient, but at least it would work.

Bob


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Wyatt, Will
Sent: Tuesday, May 10, 2005 9:38 AM
To: 'sqr-users@sqrug.org'
Subject: [sqr-users] Virtual Table


Hi all. I'm not sure what to call the problem I'm having writing this
query
in SQR. I'm using MS SQL Server w/ PeopleSoft. My query is actually more
complex, but this demonstrates the problem. The query works fine in
Query
Analyzer.

SELECT A.FIRST_NAME, A.LAST_NAME
FROM (
  SELECT NAMES.FIRST_NAME, NAMES.LAST_NAME
  FROM PS_NAMES NAMES
  WHERE NAMES.EFFDT = 
  (
    SELECT MAX(N_ED.EFFDT) FROM PS_NAMES N_ED
    WHERE NAMES.EMPLID = N_ED.EMPLID
    AND N_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,'2005-05-31',121), 1, 10)
  )
) AS A

I try to move this query to SQR and produced

BEGIN-SELECT
A.FIRST_NAME
A.LAST_NAME
FROM (
  SELECT NAMES.FIRST_NAME, NAMES.LAST_NAME
  FROM PS_NAMES NAMES
  WHERE NAMES.EFFDT = 
  (
    SELECT MAX(N_ED.EFFDT) FROM PS_NAMES N_ED
    WHERE NAMES.EMPLID = N_ED.EMPLID
    AND N_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,'2005-05-31',121), 1, 10)
  )
) AS A
END-SELECT

My problem is when I try to replace the '2005-05-31' in the effective
dated
logic with a variable, something like

    AND N_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,$AsOfDate,121), 1, 10)

I get an SQR 1303 error 'Error in SQL (perhaps missing &name after
expression).

I suspect that I can't do this because the virtual table A isn't really
a
table as far as SQR is concerned. Is there someway I can do this.

PS. Is there a technical name for what I'm trying to do with the Select
statement that is creating table A in the main query?

TIA.
--------------------------------------
Will Wyatt
PeopleSoft Analyst
Abilene Independent School District


_______________________________________________
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