I am new to FOCUS. I am using SQLPassThru. I want to dump the results into my Excel 2003 (insert attachment to e-mail). I can get the information into Excel but six columns become one column (display is six columns) when I dump the information into Excel and the information is staggered. My command to put information in Excel is TABLE FILE SQLOUT, PRINT *, ON TABLE PCHOLD FORMAT EXL2K, END. When I am prompted to save after running my report I only have the option of saving as Document or All Files. How do I get the information to display in separate Excel columns?
May 03, 2007, 05:02 PM
Prarie
Try this
TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS NEWONE FORMAT FOCUS END TABLE FILE NEWONE PRINT * ON TABLE PCHOLD FORMAT EXL2K END
0 NUMBER OF RECORDS IN TABLE= 307 LINES= 307 0 HOLDING FOCUS FILE...
May 03, 2007, 05:44 PM
Claykh4
I also just kept the old EXL2K code and added the new code in addition. I got results but in the same format
May 03, 2007, 07:04 PM
FortuneCookie
If you run your code as
SQL PASSTHRU
TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS NEWFILE END TABLE FILE NEWFILE PRINT * ON TABLE PCHOLD FORMAT EXL2K END
You should get the report to show up correctly
Prod: WebFOCUS 7.1.6, Windows 2003
Dev: WebFOCUS 7.6.2, Windows 2003
May 04, 2007, 09:19 AM
KevinG
Clay,
I assume your SQL passthru section has these elements;
ENGINE <rdbms type> SET DEFAULT_CONNECTION <Your connection name here>
SQL <rdbms type> PREPARE SQLOUT FOR
SELECT ......
FROM .....
WHERE ...... ;
END
TABLE FILE SQLOUT
PRINT *
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
The PREPARE statement is critical here. Also, I have seen issue with a PRINT * directly against the SQLOUT, you may try listing your fields explicity in your PRINT section rather than using *.
Hope this helps,
Kevin
WF 7.6.10 / WIN-AIX
May 04, 2007, 12:23 PM
Claykh4
I tried FortuneCookie's suggestion and didn't get that to work correctly either. I do not have a Prepare statement. I will try that. Also have someone local looking at my actual code. She is used to FOCUS but not specifically SQL PassThru.
May 04, 2007, 01:53 PM
FortuneCookie
Not sure what your code looks like or what the errors you're getting that's making it fail.
Here's simple piece of code against the Northwind Database in SQL Server 2000 (I did not specify DEFAULT connection as I only have 1 SQL Server defined to my server).
ENGINE SQLMSS SELECT CATEGORYID FROM Northwind.dbo.Categories; TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS TEST END TABLE FILE TEST PRINT * ON TABLE PCHOLD FORMAT EXL2K END
If you do not generate the interim HOLD file before doing the output to EXL2K, you'll end up with a EXL2K file that contains the table structure.
Prod: WebFOCUS 7.1.6, Windows 2003
Dev: WebFOCUS 7.6.2, Windows 2003
May 04, 2007, 01:55 PM
KevinG
quote:
ENGINE SET DEFAULT_CONNECTION SQL PREPARE SQLOUT FOR SELECT ...... FROM ..... WHERE ...... ; END TABLE FILE SQLOUT PRINT * ON TABLE PCHOLD FORMAT EXL2K END -RUN
Clay,
Either the format I gave above, or this should work...;
ENGINE <rdbms type> SET DEFAULT_CONNECTION <Your connection name here>
ENGINE <rdbms type>
SELECT ......
FROM .....
WHERE ...... ;
TABLE ON TABLE HOLD AS <filename>
END
-RUN
TABLE FILE <filename>
PRINT *
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
Let me know...
Thanks,
Kevin
WF 7.6.10 / WIN-AIX
May 04, 2007, 03:35 PM
Claykh4
Following is my entire code. It runs but when it goes to Excel there is only one column and the information in the one column is staggered. I expected to be able to save the data as .xls but I only have the option of Documents or All Files. How do I get this into 6 columns on Excel? If someone would be so kind as to insert code where you think it should go I would be grateful. I have only tried the first two solutions on this forum that didn't seem to work for fear that I may put something in the wrong place or not quite no what to insert (rdbms type, connection name).
SET SQLENGINE = DB2 SQL DB2 SELECT DISTINCT T284.NA_LAST "NCP LAST" ,T284.NA_FIRST "NCP FIRST" ,T030.ID_CASE_FK "CASE" ,T028.DA_INTL_ACTN_DUE "CK$$W DUE DATE" ,T028.DA_RESOLVED "CK$$W DATE RESOLVED" ,MAX(T132.DA_LEVEL1_PROCESS) "LAST PAYMENT" FROM SMACD00S.MACTB028_CASEDIARY T028 ,SMACD00S.MACTB027_CASE T027 ,SMACD00S.MACTB030_CASEFUNC T030 ,SMACD00S.MACTB284_DCNDTLS T284 ,SMACD00S.MACTB034_CSMEMBER T034 ,SMACD00S.MACTB105_MEMBER T105 ,SMACD00S.MACTB132_OBLALLOC T132 WHERE T030.ID_CASE_FK = T034.ID_CASE_FK AND T034.ID_MEMBER_FK = T105.ID_PK AND T105.NO_DCN = T284.NO_DCN_PK AND T028.ID_CASE_FK = T027.ID_PK AND T027.ID_PK = T030.ID_CASE_FK AND T132.ID_CASE = T030.ID_CASE_FK AND T028.CO_FUNCTIONTYPE_FK = 'CASE' AND T028.CO_ACTIVITYTYPE_FK = 'CK$$W' AND T028.CO_COMPLETION_TYPE <> ' ' AND T028.CO_REASON <> ' ' AND T028.DA_RESOLVED > '2006-07-31' AND T030.CO_MO_CO_FIPS_FK = '007' AND T030.CO_FIPS_OFFICE_FK = '06' AND T034.CO_CASE_ROLE_TYPE IN ('NCPT','ALGF') AND T034.CO_STATUS <> 'INAC' AND T105.NO_DCN > ' ' GROUP BY T284.NA_LAST ,T284.NA_FIRST ,T030.ID_CASE_FK ,T028.DA_INTL_ACTN_DUE ,T028.DA_RESOLVED ; TABLE FILE SQLOUT PRINT * ON TABLE PCHOLD FORMAT EXL2K END
First off, when doing a SELECT with ASNAMES that have more than one word is incorrect. Secondly, it is well documented to do nothing with SQLOUT except HOLD it:
TABLE FILE SQLOUT PRINT * ON TABLE HOLD (FORMAT ALPHA) optional END -RUN ?FF HOLD
This will give you the actual FIELDNAMES, then, you can TABLE them out without the shortcuts into an EXL2K/EXCEL file...This message has been edited. Last edited by: Tom Flynn,
My local FOCUS administrator got the code to work for her with suggestions found on this site. I will get with her to fine tune mine. Thanks everybody.