Help creating a script in SQL or Calculated field in Crystal

How do I take this script and manipulate it to give me one long string with 
static text as well as SQL data in specific positions within the string.

Example result:

Positions / Data:
1-3 / 173 (Static text)
4-6 / spaces
7-10 / "X_UPR30300"."YEAR1",  (has to show up as 2007. Showing up as 2,007)
11 / 4 (Static text)
12-22 / "UPR00100"."SOCSCNUM"
23-57 / "UPR00100"."LASTNAME"
58-92 / "UPR00100"."FRSTNAME"
93 / "UPR00100"."MIDLNAME"
94-120 / "UPR00102"."ADDRESS1"
121-148 / "UPR00102"."ADDRESS2"
149-183 / "UPR00102"."CITY"
184-185 / "UPR00102"."STATE"
186-195 / "UPR00102"."ZIPCODE"
196-105 / "X_UPR30300"."UPRTRXAM"
106-116 / "X_UPR30301"."QTR4WAGE"
117-123 / "UPR00102"."ZIPCODE"
124 / R (Static text)

SQL Statement from Crystal Reports right now:

SELECT "UPR00100"."EMPLOYID", "UPR00100"."SOCSCNUM", "UPR00100"."FRSTNAME", 
"UPR00100"."MIDLNAME", "X_UPR30300"."UPRTRXAM", "UPR00102"."ADDRESS1", 
"UPR00102"."ADDRESS2", "UPR00102"."CITY", "UPR00102"."STATE", 
"UPR00102"."ZIPCODE", "X_UPR30300"."YEAR1", "UPR00100"."LASTNAME", 
"X_UPR30301"."PAYROLCD", "UPR41400"."DSCRIPTN", "X_UPR30300"."CHEKDATE", 
"UPR00100"."INACTIVE", "X_UPR30301"."QTR4WAGE"
 FROM   ((("BRKL1"."dbo"."X_UPR30300" "X_UPR30300" INNER JOIN 
"BRKL1"."dbo"."UPR00100" "UPR00100" ON 
"X_UPR30300"."EMPLOYID"="UPR00100"."EMPLOYID") INNER JOIN 
"BRKL1"."dbo"."X_UPR30301" "X_UPR30301" ON 
(("X_UPR30300"."EMPLOYID"="X_UPR30301"."EMPLOYID") AND 
("X_UPR30300"."YEAR1"="X_UPR30301"."YEAR1")) AND 
("X_UPR30300"."PAYROLCD"="X_UPR30301"."PAYROLCD")) INNER JOIN 
"BRKL1"."dbo"."UPR41400" "UPR41400" ON 
"X_UPR30301"."PAYROLCD"="UPR41400"."LOCALTAX") INNER JOIN 
"BRKL1"."dbo"."UPR00102" "UPR00102" ON 
"UPR00100"."EMPLOYID"="UPR00102"."EMPLOYID"
 WHERE  "X_UPR30301"."PAYROLCD"<>'' AND ("X_UPR30300"."CHEKDATE">='10-01-07' 
AND "X_UPR30300"."CHEKDATE"<='12-31-07') AND "X_UPR30300"."YEAR1"=2007 AND 
"UPR00100"."INACTIVE"=1
 ORDER BY "UPR00100"."EMPLOYID"
0
Mike1154 (1216)
1/30/2008 2:04:02 PM
greatplains 29623 articles. 6 followers. Follow

3 Replies
381 Views

Similar Articles

[PageSpeed] 3

Mike,

To set fields lengthes, use the CONVERT function for dates and CAST function 
for everything else. Use SQL help as it gives some good examples.

Hope this helps!

Lori
-- 
Lori A. Baker
HRM Consultant
JAT Computer Consulting
lbaker@jatnet.com


"Mike" wrote:

> How do I take this script and manipulate it to give me one long string with 
> static text as well as SQL data in specific positions within the string.
> 
> Example result:
> 
> Positions / Data:
> 1-3 / 173 (Static text)
> 4-6 / spaces
> 7-10 / "X_UPR30300"."YEAR1",  (has to show up as 2007. Showing up as 2,007)
> 11 / 4 (Static text)
> 12-22 / "UPR00100"."SOCSCNUM"
> 23-57 / "UPR00100"."LASTNAME"
> 58-92 / "UPR00100"."FRSTNAME"
> 93 / "UPR00100"."MIDLNAME"
> 94-120 / "UPR00102"."ADDRESS1"
> 121-148 / "UPR00102"."ADDRESS2"
> 149-183 / "UPR00102"."CITY"
> 184-185 / "UPR00102"."STATE"
> 186-195 / "UPR00102"."ZIPCODE"
> 196-105 / "X_UPR30300"."UPRTRXAM"
> 106-116 / "X_UPR30301"."QTR4WAGE"
> 117-123 / "UPR00102"."ZIPCODE"
> 124 / R (Static text)
> 
> SQL Statement from Crystal Reports right now:
> 
> SELECT "UPR00100"."EMPLOYID", "UPR00100"."SOCSCNUM", "UPR00100"."FRSTNAME", 
> "UPR00100"."MIDLNAME", "X_UPR30300"."UPRTRXAM", "UPR00102"."ADDRESS1", 
> "UPR00102"."ADDRESS2", "UPR00102"."CITY", "UPR00102"."STATE", 
> "UPR00102"."ZIPCODE", "X_UPR30300"."YEAR1", "UPR00100"."LASTNAME", 
> "X_UPR30301"."PAYROLCD", "UPR41400"."DSCRIPTN", "X_UPR30300"."CHEKDATE", 
> "UPR00100"."INACTIVE", "X_UPR30301"."QTR4WAGE"
>  FROM   ((("BRKL1"."dbo"."X_UPR30300" "X_UPR30300" INNER JOIN 
> "BRKL1"."dbo"."UPR00100" "UPR00100" ON 
> "X_UPR30300"."EMPLOYID"="UPR00100"."EMPLOYID") INNER JOIN 
> "BRKL1"."dbo"."X_UPR30301" "X_UPR30301" ON 
> (("X_UPR30300"."EMPLOYID"="X_UPR30301"."EMPLOYID") AND 
> ("X_UPR30300"."YEAR1"="X_UPR30301"."YEAR1")) AND 
> ("X_UPR30300"."PAYROLCD"="X_UPR30301"."PAYROLCD")) INNER JOIN 
> "BRKL1"."dbo"."UPR41400" "UPR41400" ON 
> "X_UPR30301"."PAYROLCD"="UPR41400"."LOCALTAX") INNER JOIN 
> "BRKL1"."dbo"."UPR00102" "UPR00102" ON 
> "UPR00100"."EMPLOYID"="UPR00102"."EMPLOYID"
>  WHERE  "X_UPR30301"."PAYROLCD"<>'' AND ("X_UPR30300"."CHEKDATE">='10-01-07' 
> AND "X_UPR30300"."CHEKDATE"<='12-31-07') AND "X_UPR30300"."YEAR1"=2007 AND 
> "UPR00100"."INACTIVE"=1
>  ORDER BY "UPR00100"."EMPLOYID"
0
lori1 (126)
1/30/2008 3:23:00 PM
Lori's response is a good one with CAST or CONVERT options within SQL.  
Within Crystal what I've done in the past is create several smaller formulas 
for parts of the string and one formula to concatenate them together.  (using 
several smaller ones aids in readability).

In crystal make them all string fields and use Help to assist you with 
converting to proper formats.  E.G. your 2007 showing as 2,007 there is a 
CSTR function and it can be simply STR(date) but there are optional 
formatting where the formula might look like STR(date,"####") to force the 
number to no decimals and no thousands separator.  (I can't recall exact 
syntax whether the formatting portion needs to be in quotes or not).

Jen

"Mike" wrote:

> How do I take this script and manipulate it to give me one long string with 
> static text as well as SQL data in specific positions within the string.
> 
> Example result:
> 
> Positions / Data:
> 1-3 / 173 (Static text)
> 4-6 / spaces
> 7-10 / "X_UPR30300"."YEAR1",  (has to show up as 2007. Showing up as 2,007)
> 11 / 4 (Static text)
> 12-22 / "UPR00100"."SOCSCNUM"
> 23-57 / "UPR00100"."LASTNAME"
> 58-92 / "UPR00100"."FRSTNAME"
> 93 / "UPR00100"."MIDLNAME"
> 94-120 / "UPR00102"."ADDRESS1"
> 121-148 / "UPR00102"."ADDRESS2"
> 149-183 / "UPR00102"."CITY"
> 184-185 / "UPR00102"."STATE"
> 186-195 / "UPR00102"."ZIPCODE"
> 196-105 / "X_UPR30300"."UPRTRXAM"
> 106-116 / "X_UPR30301"."QTR4WAGE"
> 117-123 / "UPR00102"."ZIPCODE"
> 124 / R (Static text)
> 
> SQL Statement from Crystal Reports right now:
> 
> SELECT "UPR00100"."EMPLOYID", "UPR00100"."SOCSCNUM", "UPR00100"."FRSTNAME", 
> "UPR00100"."MIDLNAME", "X_UPR30300"."UPRTRXAM", "UPR00102"."ADDRESS1", 
> "UPR00102"."ADDRESS2", "UPR00102"."CITY", "UPR00102"."STATE", 
> "UPR00102"."ZIPCODE", "X_UPR30300"."YEAR1", "UPR00100"."LASTNAME", 
> "X_UPR30301"."PAYROLCD", "UPR41400"."DSCRIPTN", "X_UPR30300"."CHEKDATE", 
> "UPR00100"."INACTIVE", "X_UPR30301"."QTR4WAGE"
>  FROM   ((("BRKL1"."dbo"."X_UPR30300" "X_UPR30300" INNER JOIN 
> "BRKL1"."dbo"."UPR00100" "UPR00100" ON 
> "X_UPR30300"."EMPLOYID"="UPR00100"."EMPLOYID") INNER JOIN 
> "BRKL1"."dbo"."X_UPR30301" "X_UPR30301" ON 
> (("X_UPR30300"."EMPLOYID"="X_UPR30301"."EMPLOYID") AND 
> ("X_UPR30300"."YEAR1"="X_UPR30301"."YEAR1")) AND 
> ("X_UPR30300"."PAYROLCD"="X_UPR30301"."PAYROLCD")) INNER JOIN 
> "BRKL1"."dbo"."UPR41400" "UPR41400" ON 
> "X_UPR30301"."PAYROLCD"="UPR41400"."LOCALTAX") INNER JOIN 
> "BRKL1"."dbo"."UPR00102" "UPR00102" ON 
> "UPR00100"."EMPLOYID"="UPR00102"."EMPLOYID"
>  WHERE  "X_UPR30301"."PAYROLCD"<>'' AND ("X_UPR30300"."CHEKDATE">='10-01-07' 
> AND "X_UPR30300"."CHEKDATE"<='12-31-07') AND "X_UPR30300"."YEAR1"=2007 AND 
> "UPR00100"."INACTIVE"=1
>  ORDER BY "UPR00100"."EMPLOYID"
0
JenKuntz (14)
1/31/2008 6:52:03 PM
the 2,007 issue is simply a number formatting issue - set the number format 
to not use a thousands separtor.
What you are looking for is a fixed-width file.  That can be achieved using 
an export format in Crystal but fixed width exports have specific formatting 
requirements.
It's been years since I did a fixed-width export and can't remember 
everything that was needed.
I do know that report headers and footers should be avoided.
You can use groups but that had a special requirement as well
Just play around with the exports and you should be able to figure it out.

Regards
Habib


"Jen Kuntz" <JenKuntz@discussions.microsoft.com> wrote in message 
news:DB62D083-4829-4C05-8832-DB834C182313@microsoft.com...
> Lori's response is a good one with CAST or CONVERT options within SQL.
> Within Crystal what I've done in the past is create several smaller 
> formulas
> for parts of the string and one formula to concatenate them together. 
> (using
> several smaller ones aids in readability).
>
> In crystal make them all string fields and use Help to assist you with
> converting to proper formats.  E.G. your 2007 showing as 2,007 there is a
> CSTR function and it can be simply STR(date) but there are optional
> formatting where the formula might look like STR(date,"####") to force the
> number to no decimals and no thousands separator.  (I can't recall exact
> syntax whether the formatting portion needs to be in quotes or not).
>
> Jen
>
> "Mike" wrote:
>
>> How do I take this script and manipulate it to give me one long string 
>> with
>> static text as well as SQL data in specific positions within the string.
>>
>> Example result:
>>
>> Positions / Data:
>> 1-3 / 173 (Static text)
>> 4-6 / spaces
>> 7-10 / "X_UPR30300"."YEAR1",  (has to show up as 2007. Showing up as 
>> 2,007)
>> 11 / 4 (Static text)
>> 12-22 / "UPR00100"."SOCSCNUM"
>> 23-57 / "UPR00100"."LASTNAME"
>> 58-92 / "UPR00100"."FRSTNAME"
>> 93 / "UPR00100"."MIDLNAME"
>> 94-120 / "UPR00102"."ADDRESS1"
>> 121-148 / "UPR00102"."ADDRESS2"
>> 149-183 / "UPR00102"."CITY"
>> 184-185 / "UPR00102"."STATE"
>> 186-195 / "UPR00102"."ZIPCODE"
>> 196-105 / "X_UPR30300"."UPRTRXAM"
>> 106-116 / "X_UPR30301"."QTR4WAGE"
>> 117-123 / "UPR00102"."ZIPCODE"
>> 124 / R (Static text)
>>
>> SQL Statement from Crystal Reports right now:
>>
>> SELECT "UPR00100"."EMPLOYID", "UPR00100"."SOCSCNUM", 
>> "UPR00100"."FRSTNAME",
>> "UPR00100"."MIDLNAME", "X_UPR30300"."UPRTRXAM", "UPR00102"."ADDRESS1",
>> "UPR00102"."ADDRESS2", "UPR00102"."CITY", "UPR00102"."STATE",
>> "UPR00102"."ZIPCODE", "X_UPR30300"."YEAR1", "UPR00100"."LASTNAME",
>> "X_UPR30301"."PAYROLCD", "UPR41400"."DSCRIPTN", "X_UPR30300"."CHEKDATE",
>> "UPR00100"."INACTIVE", "X_UPR30301"."QTR4WAGE"
>>  FROM   ((("BRKL1"."dbo"."X_UPR30300" "X_UPR30300" INNER JOIN
>> "BRKL1"."dbo"."UPR00100" "UPR00100" ON
>> "X_UPR30300"."EMPLOYID"="UPR00100"."EMPLOYID") INNER JOIN
>> "BRKL1"."dbo"."X_UPR30301" "X_UPR30301" ON
>> (("X_UPR30300"."EMPLOYID"="X_UPR30301"."EMPLOYID") AND
>> ("X_UPR30300"."YEAR1"="X_UPR30301"."YEAR1")) AND
>> ("X_UPR30300"."PAYROLCD"="X_UPR30301"."PAYROLCD")) INNER JOIN
>> "BRKL1"."dbo"."UPR41400" "UPR41400" ON
>> "X_UPR30301"."PAYROLCD"="UPR41400"."LOCALTAX") INNER JOIN
>> "BRKL1"."dbo"."UPR00102" "UPR00102" ON
>> "UPR00100"."EMPLOYID"="UPR00102"."EMPLOYID"
>>  WHERE  "X_UPR30301"."PAYROLCD"<>'' AND 
>> ("X_UPR30300"."CHEKDATE">='10-01-07'
>> AND "X_UPR30300"."CHEKDATE"<='12-31-07') AND "X_UPR30300"."YEAR1"=2007 
>> AND
>> "UPR00100"."INACTIVE"=1
>>  ORDER BY "UPR00100"."EMPLOYID" 


0
HSalim (1270)
1/31/2008 9:29:47 PM
Reply:

Similar Artilces: