Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Command in Crystal Reports

Posted on 2009-07-02
6
Medium Priority
?
535 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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 2000 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

After having deployed hundreds of thousands of Terminal Services seats worldwide, I still see all the time people asking me that same old question: "If TS/RDS is that reliable why are you telling me I should reboot it that often? My DC/SQL/Exchange/…
Have you considered what group policies are backwards and forwards compatible? Windows Active Directory servers and clients use group policy templates to deploy sets of policies within your domain. But, there is a catch to deploying policies. The…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

715 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