Toggle Submenu
  1. Forums
  2. Recent
  3. Categories
  4. Tags
  5. Users
Add a reply View Replies (4)

Resolved PostgreSQL error in tyring to print report (from Valentina Studio Pro)

  1. Stefania Georgiadi
    Stefania Georgiadi
  2. Report Editor
  3. Tuesday, July 12 2016, 07:50 PM
  4.  Subscribe via email
Hello,

I have a problem with trying to print a report (from Valentina Studio) on Postgres.
The report prints Ok on SQLServer. The query is simple:

SELECT wteSurname, wteName, wteClockCard,wteDepartment, wteCode from wtEmployee


I have 2 sort fields on the report: wteSurname, wteName.

When I am trying to print the report it shows the following:

SELECT * FROM ( SELECT wteSurname, wteName, wteClockCard,wteDepartment, wteCode from wtEmployee) as foo ORDER BY "wteSurname","wteName"

and

ERROR: column "wteSurname" does not exist

Where does "foo" come from? why is the query warped in this way? Should I do something differently?
Comment
There are no comments made yet.
Responses (4)
Stefania Georgiadi
Stefania Georgiadi Accepted Answer
Actually it seems that the problem comes from the double quotes in the following part of the query produced by Valentina Studio:

... ORDER BY "wteSurname","wteName"

It seems that wteSurname works, wtesurname works but "wteSurname" (which forces case sensitivity) doesn't work.
Is it possible to set it to not put on the quotes, so that it doesn't force case sensitivity?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 1
Sergey Pashkov
Sergey Pashkov Accepted Answer
1. That's how grouping, sorting, expression calculation works.

Without use of these features - a source query will be executed as is.

2. No, there is no such option, and looks like it is not so easy to understand - should we add quotes or not.

These field names currently in the lower case in the table, so the best way to avoid any future problems is to write them in lower case, too:
SELECT wtesurname, wtename, wteclockcard,wtedepartment, wtecode from wtemployee
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 2
Stefania Georgiadi
Stefania Georgiadi Accepted Answer
Concerning the quotes I understand, but in SQLServer the columns are returned capitalized, so if I am prototyping on MSSQL Server then the columns will not be readable from Postgres.

However my investigation showed that SQLServer is quite tolerant, so the sort columns even if in quotes, even if lowercase do not pose a problem.
So if it is not possible to remove the quotes, then the solution is to prototype using Postgres?! Or is there any other way?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 3
Sergey Pashkov
Sergey Pashkov Accepted Answer
Right, need to use PostgreSQL for prototyping or
- use double quotes always
- write names with upper-case letters in double quotes

Here's what happens:
1. Create a report with grouping by FIELD1 for MS SQL database - "FIELD1" is written to the report
2. Use this report in PostgreSQL database. But the field was created without double quotes, so it is in the lower case and can't be found by grouping feature
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 4
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.