Solved

SQL Command in Crystal Reports

Posted on 2009-07-02
6
532 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Every system administrator encounters once in while in a problem where the solution seems to be a needle in haystack.  My needle was an anti-virus version causing problems with my Exchange server. I have an HP DL350 with Windows Server 2008 Stand…
Problem Description: Actually I found the below issue with some customers after migration from SMS 2003 to SCCM 2007 and epically if they change site code, some clients may appear in the console with old site code, plus old sites still appearing …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

626 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