Solved

SQL Command in Crystal Reports

Posted on 2009-07-02
6
525 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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/…
Welcome to my series of short tips on migrations. Whilst based on Microsoft migrations the same principles can be applied to any type of migration. My first tip Migration Tip #1 – Source Server Health can be found here: http://www.experts-exchang…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now