Liveware Publishing Support Forum

Sorry, most of the time I am using simple SQL commands.____In RapidSQL, I have a select statement that creates a temporary table. I can then select on that table and join to more tables for the final results.____The problem is that I cannot get the same SQL code to work properly in Arpeggio Report Writer apparently due to my syntax or statement structure.____This represents my SQL statement (severly stipped down):____begin__select distinct fix.iIdPlanGrpPurp as IdFixRatePGP, com.iIdPlanGrpPurp as iIdCompanyPGP, com.cIdPlanGrpPurp as cIdPlanGrp__ into #Fix_to_Company__ from PlanGrpingMaster com,PlanGrpingMaster fix __ where fix.iIdGrpPurp=9____select distinct ci.cSystemCode, fad.cExtract__ from CompanyInfo ci, FixAccountDuration fad, #Fix_to_Company fix__ where fad.iIdFixRatePGP=fix.iIdFixRatePGP__ and ci.iIdCompanyPGP = fad.iIdPlanGrpPurp__end____I tried putting the above into the SQL statement. I received the error "Table contains no supported fields", then Arpregio Report Writer exits. I then reopened the report and I typed in just the second select statement, and the report works using the temp table created the first time. Somehow the database thinks I am still running the same session so the temp table still exists.____I imagine the "Table contains no supported fields" error is due to the first select statement not returning anything. ____The question is how do I properly structure the SQL code in the SQL Select Statement window, so I get the temp table created and only the results from the second select statement are returned to the report writer.____Do I need to define a ReportView? Use some other syntax?____Any help would be greatly appreciated.____By the way, does anyone know any good links talking about complex SQL statements that work within the constraints of ODBC.____Thanks,____John

What you would need to do is to turn your SQL statements into a stored procedure on the server. You would then do a User SQL in Arpeggio to EXEC that stored procedure.____Kathleen__R&R Support