SQL statements as Command items 1

[thiagarr]

Hi,

I am trying to use SQL statements as Command under Database Expert. I have couple of questions on this and hope somebody would be able to help me there.

1 Once a command item is added, is there anyway we can change the name of the item? For example, I see the name for the first code shown here as count(1). I would like to rename this to something more meaningful.

2 I tried the following and it works fine.

[code}

SELECT COUNT(1) FROM oradba.up_user
WHERE TRUNC(date_created) >= ADD_MONTHS(TRUNC(sysdate), -12)

[/code]

But the following gives an error while adding the command. "Failed to retireve data from the database. ORA-00911: Invalid charactaer"

Both the codes work in SQL developer successfully.

[code}

SELECT COUNT(1) FROM oradba.up_user
WHERE TRUNC(date_created) >= ADD_MONTHS((LAST_DAY(ADD_MONTHS(trunc(sysdate),-1))), -12);

[/code]

I would appreciate any insight / help on this.




Thanks and regards,

Chandra

 

[ns130]

Try

SELECT COUNT(1) As 'NiceFieldName' FROM oradba.up_user
WHERE TRUNC(date_created) >= ADD_MONTHS(TRUNC(sysdate), -12)

To change the name of the field

 

[thiagarr]

Hi,

Thank you, ns130. The suggestion to name it in the SQL statement worked fine.

The issue with the second code was that the semicolon characeter at the end of the SQL statement is not liked by the command option in CR. When I removed it, it worked fine.



I also want to add another related query here? Instead of sysdate in the above example, is it possible to use variable that has already been defined in CR as part of the formula field?? If yes, how to do that??

Thank you again.

Thanks and regards,

Chandra

 

[lbass]

You can't reference a formula in a command, but you could create a parameter within the command and use that instead of sysdate.

-LB

 

[synapsevampire]

Why would you need a formula in Crystal to do what the dtaabase can do?

Rather than demanding that a certain methodology be used, why not state technical information and requirements?

A formula field in crystal seems like an odd way to go.

-k

 

[thiagarr]

LB / -k,

Thank you for the details.

Basically, I wanted to use the formula as I had number of instances where I use the calculated values of start and end dates in the report. I am trying to use SQL statements from already existing SPs to build a report. Instead of using the date calculations in each of the commands, I thought if I can use a formula, that can be used instead. Parameter is not an option at this stage as we want to hard code the date range.

I have already built the report from scratch in CR after LB gave me some guidnace on how to proceed. But, the users want to preserve the SP from Oracle as much as possible and want the SP itself to be used in CR as an SQL object. I am starting to look at this option now and see how much I can import the SPs into CR without modifications.

I appreciate your help / suggestions on this. The forum has been wonderful to me and I am sure to many others like me.

Thanks and regards,

Chandra

 

[thiagarr]

LB / -k,

Thank you for the details.

Basically, I wanted to use the formula as I had number of instances where I use the calculated values of start and end dates in the report. I am trying to use SQL statements from already existing SPs to build a report. Instead of using the date calculations in each of the commands, I thought if I can use a formula, that can be used instead. Parameter is not an option at this stage as we want to hard code the date range.

I have already built the report from scratch in CR after LB gave me some guidnace on how to proceed. But, the users want to preserve the SP from Oracle as much as possible and want the SP itself to be used in CR as an SQL object. I am starting to look at this option now and see how much I can import the SPs into CR without modifications.

I appreciate your help / suggestions on this. The forum has been wonderful to me and I am sure to many others like me.

Thanks and regards,

Chandra