PEEK & SQL

[#1]

Hi,

Can anyone point out my silly mistake in the code bellow it returns a NULL value each time, or is it not possible to use peek on a SQL loaded table, it seems to work fine if I load from a QVD. I assumed that once the record was loaded it would respond in the same way ...

Para1:
LOAD "curr-year" AS [Current Year],
 "last-year" AS [Last Year],
 "next-year" AS [Next Year],
 "period-no" AS [Period],
 "report-pd" AS [Report Period],
 "report-wk" AS [Report Week],
 "week" AS [Week];
SQL SELECT "curr-year", "last-year", "next-year", "period-no", "report-pd",
 "report-wk", "week"
FROM PUB.para1;
LET vCurrentYear = year(peek('[Current Year]',1,'Para1'));

[Anonymous · Accepted solution]

You're using peek outside of SQL, so this part is fine. Table name here is not needed since you're using LET immideately after the Paral table. Try to remove brackets. And, if Current Year field is not in a date format, you don't need year() function:
LET vCurrentYear = peek('Current Year',1);

[Oleg_Troyansky]

In addition to Michael's suggestions:

1. check that you are actually getting data from your SQL statement and what are the field names.

2. Check that you don't have a previous load (from QVD, for example), that's loading the same fields into a different table name. Maybe your two tables are being automatically concatenated and this table name simply doesn't exist).

Oleg

[#4]

Thanks both for your reply, it turns out that the problem was the square brackets round the field name. My mistake, I was under the impression that this was used every time a field name contained a space.

[Anonymous]

Filed name here is a string in single quotes, so space doesn't matter. Brackets were assumed to be a part of the field name, I think.