Hi, I am trying to get the following command to work. It is designed to create a table of dates (blank), and also grab data from a rainfall table (weather station). I need to create a rainfall chart with months across the top of the page (jan to dec) and days for rows (1st to 31st). What is happening is when I try to use a parameter to filter down to only one weather station, only the days with a rainfall are left. I need to have all the days showing in the table with just the rainfall records showing a value. Here is a better description from a previous thread.
That's a result of the linking process. The dates from the date table are tied to the dates on the other table. The linking is happening before the selection. When you exclude the records with the selection criteria you are excluding the dates as well.
There are two possible solutions.
1) Pass the parameters to the 1st table before it's linked to the dates table.
2) Use formatting to "hide" the unwanted records.
The 1st option (my favorite) is to get rid of the existing table and replace it with a new command. You can pass you parameters at the command level which pushes the selection back to the server. This in turn brings back the reduces record set before the linking occurs.
SLQ Commands will typically replace tables completely. You will typically try to avoid mixing tables and commands. the reason is that CR looses it's ability to push sorting and grouping back to the server when a command is introduced.
This is typically not a big deal though, because, you can use the Sql Command do your sorting and grouping on the server yourself.
a typical SQL Command may look something like this:
SELECT
a.Field1,
a.Field2,
a.Field3
FROM TableName AS a
WHERE a.Field1 = {?Parameter1}
So here's what I have so far....
CREATE TABLE #DateList
(
Dates DateTime NOT NULL PRIMARY KEY
);
DECLARE locDate DateTime;
DECLARE BEG DateTime;
DECLARE FIN DateTime;
DECLARE N DOUBLE PRECISION;
//-- Sets the 1st date in the table.
SET BEG=TIMESTAMP '2000-01-01 00:00:00';
//-- Sets the Last date in the Table.
SET FIN=TIMESTAMP '2008-12-31 00:00:00';
//-- Fills all the dates in between...
SET locDate = BEG;
WHILE locDate <= FIN DO
INSERT INTO #DateList
(Dates)
SELECT locDate AS Dates FROM #dummy;
/* This should work, but it looks like Nexus haven't written the code yet to
actually add dates, the slack dogs.
SET locDate=locDate + INTERVAL '1' DAY;
*/
SET N = CAST(locDate AS DOUBLE PRECISION) + 1;
SET locDate = CAST(N AS TIMESTAMP);
END WHILE;
SELECT *
FROM #DateList;
DROP TABLE #DateList;
SELECT "WeatherDetail"."RainFall", "WeatherStation"."Name", "WeatherDetail"."Date"
FROM "WeatherDetail" "WeatherDetail" INNER JOIN "WeatherStation" "WeatherStation" ON "WeatherDetail"."WeatherStationID"="WeatherStation"."WeatherStationID"
WHERE "WeatherStation"."Name" = ''
Any ideas what may be going on here??