Crystal XI - Can I create and drop temp tables using SQL Command?

[thementornetwork]

I am currently working with a "closed" DB and we cannot add any custom functions nor stored procs to.
SQL Command is the closest thing to a stored proc. I know I can write a select statement in SQL Command but can I go to the extend of create multiple temp tables and refer to each other? Thanks.

 

[kskid]

Using the Command in Crystal is similar to creating an Oracle view if that is what you are wanting.

 

[Madawc]

You can create and add data using something like

Create   Table  #FEE_F (CustNo int, RollNo int, ArrearVal Decimal(9,2), Asmonth char(02), 
     RetCount int , DDCt int, ChqCt int, Custmonth char(15))
Insert  into  #FEE_F (CustNo, RollNo, ArrearVal, Asmonth, RetCount, DDCt, ChqCt, Custmonth)

For dropping, you can say something simple at the end, like Drop Table #FEE_FS. This will cause trouble where the run in interrupted and it fails to drop the tables.

The best method is to conditionally drop the tables at the start of the run. There is more than one way to do it, but the recommended method is

If Object_ID('tempdb.dbo.#FEE_GS') Is Not Null
 Drop Table #FEE_GS

Temporary tables can indeed refer to each other. You can also group and delete conditionally, useful options.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10