[sqr-users] Virtual Table

[Matt Rogish <rogishmn@muohio.edu>]

Will:

Did you try the "$space" trick (someone on this list showed me this many 
moons ago)?

let $junkVariable = ' '

begin-select
...

from [$junkVariable]
( SELECT foo ... rest of sql )
end-select

Usually that fixes most SQR-related parsing problems of non-trivial SQL. 
The parser must see parens and freeze up.

--
Matt

At 07:01 AM 5/11/2005, you wrote:
>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