1 Topic by Alan Hale

  • Alan Hale
  • Veteran
  • Offline
  • From: Aberystwyth, Wales
  • Registered: 11-05-2006
  • Posts: 278

Topic: SQL Error with pre-built form record_details_2

Sorry - here's another problem:

I was thinking about building my own record details form when I stumbled across two prebuilt forms under "Utilities". The first - record_details -  is pretty basic. record_details_2 looks more promising, but generates an SQL error with me:

ERROR: function right(character varying, integer) does not exist at character 730 HINT: No function matches the given name and argument types. You might need to add explicit type casts

Cheers
Alan

Alan Hale
Aberystwyth

Alan Hale's Website

2 Reply by Jim Bacon

  • Jim Bacon
  • Veteran
  • Offline
  • From: BRC
  • Registered: 03-11-2008
  • Posts: 980

Re: SQL Error with pre-built form record_details_2

Hi Alan

An easy one.
PostgreSQL added the right() function in version 9.1. We should not be using it.

The offending report can be found in reports/ reports_for_prebuilt_forms/ record_details_2/ record_data_attributes_with_hiddens.xml.

Would you like to try working out how to do it with version 8.4 string functions?

Jim Bacon.

3 Reply by Alan Hale

  • Alan Hale
  • Veteran
  • Offline
  • From: Aberystwyth, Wales
  • Registered: 11-05-2006
  • Posts: 278

Re: SQL Error with pre-built form record_details_2

Hi Jim - glad it was an easy one!

Yes, I'll certainly have a go at modifying the form using the 8.4 functions.

(Still trying to figure out how to upgrade to Postgresql 9.2 - there are some complications on my web server. Not a high priority for me at the moment though).

Cheers

Alan

Alan Hale
Aberystwyth

Alan Hale's Website

4 Reply by Alan Hale (edited by Alan Hale 23-03-2013 14:53:00)

  • Alan Hale
  • Veteran
  • Offline
  • From: Aberystwyth, Wales
  • Registered: 11-05-2006
  • Posts: 278

Re: SQL Error with pre-built form record_details_2

OK, this works:

I've altered

CASE WHEN right(a.caption, 4) = 'link' AND left(av.text_value, 4) = 'http'

to

CASE WHEN substring(a.caption from (char_length(a.caption)-5) for 4) = 'link' AND substring(av.text_value from 0 for 4) = 'http'

Alan

Alan Hale
Aberystwyth

Alan Hale's Website

5 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: SQL Error with pre-built form record_details_2

Thanks Alan. I've committed that change to SVN.

John van Breda
Biodiverse IT

johnvanbreda's Website