Help with Mid Function?

[smedvid]

Seems simple enough, but I am spinning my wheels....

I need to construct an SQL statement that will be used for a report. I have a field (Payment_Detail) that is CHAR(1200). I need to break the field down in 254 chuncks via Mid() so the data can be exported. Appearently, 255 characters is max for export to Excel from the Web Component. So... what is the syntax... I keep getting invalid Column Name.


SELECT
CRTRANDETAIL."USERGROUP",
CRTRANDETAIL."MESSAGE_ID",
CRTRANDETAIL."PAYMENT_DETAIL",
...
MID(xxxxx,1,254) AS PD_1,
MID(xxxxx,255,254) AS PD_2,
...
CRTRANDETAIL."POST_TIME",
CRTRANDETAIL."MESSAGE_CLASS",


Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[Naith]

If you're using SQL, and want to split the field up before the report gets to process the data, you'd use the substring function, which - depending on your database - would be SUBSTR(FIELD,1,5).

MID is a Crystal function, which is used within the report developer. You have the correct syntax for the function, but you're using it in the wrong context. Use SUBSTR for the query. Save MID for the report.

All the best,

Naith

 

[smedvid]

Thanks... that was the trick! Steve Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...