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?

Try this

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS NEWONE FORMAT FOCUS
END
TABLE FILE NEWONE
PRINT *
ON TABLE PCHOLD FORMAT EXL2K
END

I add the code and ran and got this:

No HTML Output!

--------------------------------------------------------------------------------


0 NUMBER OF RECORDS IN TABLE= 307 LINES= 307
0 HOLDING FOCUS FILE...

I also just kept the old EXL2K code and added the new code in addition. I got results but in the same format

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

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

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.

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.

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

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

quote:

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"

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.