Solved

SQL Command in Crystal Reports

Posted on 2009-07-02
6
529 Views
Last Modified: 2013-12-05
I need to write an sql command to use in a crystal report. In that command one of the fields I need is soc# formated as character with the separating hyphens. The incoming field is a 9 position numeric. I need to do this so I can link to other tables that have the soc# formated as character with the separators. I am having a problem finding what I need to change it to character, so I can substring and concatenate to do the reformat.
0
Comment
Question by:qbjgqbjg
  • 4
  • 2
6 Comments
 
LVL 5

Expert Comment

by:jgbreeden
ID: 24768812
would that be CONVERT(CHAR(9), fieldname)
0
 

Author Comment

by:qbjgqbjg
ID: 24785047
This is the code I was trying, that it did not like:
 substring("pr500ap"."prssno",1,3) + '-' +
          substring("pr500ap"."prssno",4,2) + '-' +
          substring("pr500ap"."prssno",6,4)
So how can I use the CONVERT(CHAR(9) in this statement?
0
 
LVL 5

Expert Comment

by:jgbreeden
ID: 24786034
if that is a numeric field, you convert it before you apply any string functions:
substring(CONVERT(CHAR(9),"pr500ap"."prssno"),1,3)
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 5

Expert Comment

by:jgbreeden
ID: 24786088
another thought, if that is a numeric field, it will strip off any leading zeros, you will need to check the length and pad it with zeros if it is less than 9 digits.
0
 

Author Comment

by:qbjgqbjg
ID: 24794213
How do I check the number of digits?
0
 
LVL 5

Accepted Solution

by:
jgbreeden earned 500 total points
ID: 24795785
You could just pad some leading zeros then take the RIGHT 9 chars:
RIGHT("000"+CONVERT(CHAR(9),"pr500ap"."prssno"), 9)
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

On a regular basis I get questions about slow RDP performance, RDP connection problems, strange errors and even BSOD, remote computers freezing or restarting after initiation of a remote session. In a lot of this cases the quick solutions made b…
Some time ago I faced the need to use a uniform folder structure that spanned across numerous sites of an enterprise to be used as a common repository for the Software packages of the Configuration Manager 2007 infrastructure. Because the procedu…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question