• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1033
  • Last Modified:

How do I change the format of SQL output - Informix

This is a two-parter:
I have a query and I am using NVL to grab an integer but it is bringing the result back as 1.0000000000 or 2.0000000000 when it is just in the db as a 1 or a 2.

The 2nd part is I am grabbing dates as YYYY-MM-DD, I need to change the output to their preferred format of mm/dd/yyyy

Here is the query:
select i.id, e.grad_date, p.sex, a.common_app_id, i.firstname, i.lastname, i.middlename, NVL(v.score,'') as score
from id_rec i
join adm_rec a on i.id = a.id
join profile_rec p on i.id = p.id
join ed_rec e on e.id = i.id and e.prim_sch = 1
 left outer join intvwrecom_rec v on i.id = v.id and v.intvwrecom = 'INT1'
where a.plan_enr_yr = '2011'

Sample of the output I need changed:
grad_date: 2011-06-01
score: 2.0000000000000000

What I want to see:
grad_date: 06/01/2011
score: 2
0
dcrowley_01
Asked:
dcrowley_01
  • 3
1 Solution
 
Dave FordSoftware Developer / Database AdministratorCommented:

add the INTEGER function to v.score and the CHAR function to grad_date with a parameter of USA.

HTH,
DaveSlash

e.g.

select i.id,
       char(e.grad_date, usa) as grad_date,
       p.sex, 
       a.common_app_id, 
       i.firstname, 
       i.lastname, 
       i.middlename, 
       NVL(integer(v.score),'') as score
  from id_rec i 
  join adm_rec a
    on i.id = a.id 
  join profile_rec p
    on i.id = p.id
  join ed_rec e
    on e.id = i.id
   and e.prim_sch = 1
  left outer join intvwrecom_rec v
    on i.id = v.id
   and v.intvwrecom = 'INT1'
 where a.plan_enr_yr = '2011'

Open in new window

0
 
dcrowley_01Author Commented:
There is no USA function in my informix, but I was able to get the date part to work like this:
 TO_CHAR(e.grad_date, "%m/%d/%iY") as grad_date

But the integer part isn't working either and I can't figure that out. It says:
Error: Routine(integer) can not be resolved
0
 
dcrowley_01Author Commented:
Answer:
select i.id, TO_CHAR(e.grad_date, "%m/%d/%iY") as grad_date, p.sex, a.common_app_id, i.firstname, i.lastname, i.middlename, TO_CHAR(NVL(v.score,''),"*") as score
from id_rec i
join adm_rec a on i.id = a.id
join profile_rec p on i.id = p.id
join ed_rec e on e.id = i.id and e.prim_sch = 1
 left outer join intvwrecom_rec v on i.id = v.id and v.intvwrecom = 'INT1'
where a.plan_enr_yr = '2011'


0
 
dcrowley_01Author Commented:
I had to figure this out myself, but at least this gives some history if others do a search.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now