4 Replies - 1619 Views - Last Post: 27 March 2009 - 09:30 AM Rate Topic: -----

#1 alaub  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 34
  • Joined: 08-January 08

Create View converting data types

Posted 18 March 2009 - 11:00 PM

I am trying to create a view that can be used to by our mapping program for updating

CREATE VIEW ccc_septic_risk_low AS
SELECT auprlink.pcl_num AS pcl_Num, CAST(aualdocs.ddn_cd2 AS VarChar(255)) AS Risk, aualchkl.dta_val AS Result, CAST (aualdocs.det_dte AS VarChar(255))  AS Date
FROM aualdocs, auprlink, aualchkl
WHERE aualdocs.fmt_acc = auprlink.mdu_fmt AND aualdocs.fmt_acc = aualchkl.fmt_acc AND aualdocs.doc_typ = 15 AND aualdocs.ddn_cd2  = 1 AND aualchkl.chk_typ = 15 AND aualchkl.itm_num = 2 AND det_cde<>"HWC"
OR aualdocs.fmt_acc = auprlink.mdu_fmt AND aualdocs.fmt_acc = aualchkl.fmt_acc AND aualdocs.doc_typ = 15 AND aualdocs.ddn_cd2  = 1 AND aualchkl.chk_typ = 15 AND aualchkl.itm_num = 2 AND det_cde<>"DEC";



with the above code I can get the select statement on it's own to work but I can't get the create view to work
if i remove the CAST on some of the fields it works but then the updating doesn't work...

My main question is, is it possible to create a view where the field is a type conversion from a table?

Is This A Good Question/Topic? 0
  • +

Replies To: Create View converting data types

#2 puing  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 30
  • Joined: 13-March 09

Re: Create View converting data types

Posted 19 March 2009 - 09:35 PM

is there any error report when you run the script?
and what database you use?
Was This Post Helpful? 0
  • +
  • -

#3 kzimmerm  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 70
  • Joined: 08-February 09

Re: Create View converting data types

Posted 20 March 2009 - 08:22 AM

Try this:

CAST (aualdocs.det_dte AS VarChar(255))  AS [Date]



You may be running into an issue with DATE because it is a reserved word.

Kurt



Nope... something else must be wrong with your query. I just tried a sample view and it works fine.

How about posting the error you receive.

Kurt

This post has been edited by kzimmerm: 20 March 2009 - 08:19 AM

Was This Post Helpful? 0
  • +
  • -

#4 alaub  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 34
  • Joined: 08-January 08

Re: Create View converting data types

Posted 23 March 2009 - 05:44 PM

Hi Kurt and Puing

Attached Image
This is the error that I am receiving when I try to run the code as I have it above.

I am using an Informix database.
I know it has to be somehow possible to do this, as the view which I am trying to recreate (with an update) has it all as I want it.

Unfortunately as I haven't had a response to my previous post it seems that if the SHOW VIEW CREATE doesn't work, noone knows what else to try.

Thanks heaps, Anita
Was This Post Helpful? 0
  • +
  • -

#5 kzimmerm  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 70
  • Joined: 08-February 09

Re: Create View converting data types

Posted 27 March 2009 - 09:30 AM

View Postalaub, on 23 Mar, 2009 - 04:44 PM, said:

Hi Kurt and Puing

Attachment attachment
This is the error that I am receiving when I try to run the code as I have it above.

I am using an Informix database.
I know it has to be somehow possible to do this, as the view which I am trying to recreate (with an update) has it all as I want it.

Unfortunately as I haven't had a response to my previous post it seems that if the SHOW VIEW CREATE doesn't work, noone knows what else to try.

Thanks heaps, Anita



I wish I could help you out but I don't know Informix. Your quickest effort will probably come from Google..

Good luck.

Kurt
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1