SQL Expression error - ????

[EBee]

The Below code is error out for some reason. I tried to do this in query analyzer and it worked just fine. Now, the way I understand SQL Expression in CR9, you can use standard sql epxression to get the result you want.
this errors out on the first line. .

ERROR: syntax error or violation error :42000

any Ideas. . . .

select {mc_period_dates.calendar_date}
from mc_period_dates
where period_year =
(select {mc_period_dates.period_year}
from mc_period_dates
where {mc_period_dates.calendar_date} = convert(varchar (8), getdate(), 112)) and
period =
(select {mc_period_dates.period}
from mc_period_dates
where {mc_period_dates.calendar_date} = convert(varchar (8), getdate(), 112)) and
day_of_period = 1

 

[synapsevampire]

You could use standard SQL Expressions in previous versions as well.

You forgot to state the intent here.

If you're trying to use SQL to supply a recordset for the report, use the Add Command, not a SQL Expression.

It looks like you're using SQL Server, so connect to the SQL Server data source and the first choice will be Add Command, it will also allow you to create and apply parameters there.

-k

 

[EBee]

I would like to schedule our reports to run weekly without entering 4 date parameters. and based on 5-4-4 accounting period dates.

StartDate . . . first day of each period
EndDate. . . . Current date
LY_StartDate . . first day of last year same period
Ly_EndDate . . . Same date for last year(current date)

I have a period_Dates Table populated and have the following

Calendar_Date = datefield
Period_year = number
Period = number
DayofPeriod = number

My code below seems to have the logic needed to get the dates, however, it does not work. Please help
thank you.

This was the original intent, however -it seems that this could be accomplished using sql expression and provide the 4 dates that I need.

Is there a better way to achieve this?

Thank you

======================================================
//?StartDate
if {mc_period_dates.calendar_date} = today then

local numbervar period1 := {mc_period_dates.period};
local numbervar dayofperiod1 := 1;
local numbervar periodyear1 := {mc_period_dates.period_year};

if {mc_period_dates.period} = Period1 and
{mc_period_dates.day_of_period} = dayofperiod1 and
{mc_period_dates.period_year} = periodyear1 then

{mc_period_dates.calendar_date}

 

[synapsevampire]

I'm afraid that I don't know what the startdate represents for a 5-4-4, if you would take the time to describe them, I'll code it out.

StartDate . . . first day of each period
EndDate. . . . Current date
LY_StartDate . . first day of last year same period
Ly_EndDate . . . Same date for last year(current date)

-k

 

[EBee]

I was able to add "ADD COMMAND" that was very helpfull - Thank you. Is there away to re-name the COMMAND to something else. And, what is the Create parameter about when you create a ADD COMMAND?

I added 3 COMMAND

command
command_1
command_2

this was created by the CR.

 

[vidru]

To rename a Command: Database > Database Expert > highlight the Command to change > hit [F2] > start typing.

-dave