subquery in SQL

I am a newbie when it comes to TBS and templating in general, but I'm liking it already: my code has never been this clean with HTML, PHP and CSS separated cleanly! And the TBS-class makes it so easy to connect to dbases, it's amazing.

Now I have a problem that I don't know if it is a TBS-issue that I'm not getting or a plain postgresql-issue. This is the line of code that is bugging me:

[CODE]$TBS->MergeBlock('overzicht_dossiers',$cnx_id,'SELECT * FROM dossiers WHERE (SELECT EXTRACT(ISOYEAR FROM DATE \'dossiers_open\'))=2006 ORDER BY dossiers_id DESC');[/CODE]

And this is the error I'm getting:
[CODE]TinyButStrong Error when merging block [overzicht_dossiers] : PostgreSQL error message when opening the query: ERROR: invalid input syntax for type date: "dossiers_open"[/CODE]

I merely want to filter out all the records of that table where the creation date (dossiers_open) is somewhere in 2006. The date format is YYYY-MM-DD, as is recommended by postgresql. When I echo the full table, the dates are printed correctly and pgadmin3 tells me the column is formatted as a date.

The function EXTRACT ISOYEAR FROM DATE will fetch the year out of the date - I also have tried the date_part-function, which produced the same error.

My guess is that PG is using the column title 'dossiers_open' instead of the implied value of that column/record. Am I not getting something about TBS and subqueries (maybe adding quotes, although I have virtually tried any combination of quotes/no quotes)?

THX!
Zl.

Hi,

The error message implies (to me) that you're getting a PG error - nothing to do with TBS.

Try isolating the query into a separate PHP script to see what you get for results/errors.

My prediction ;) is that the portion ...=2006 ... is a potential source for error. Many db's handle dates as char type data so it might be expecting ...='2006'... -or- ...="2006"...

Keep us posted,

THX for your reply. You were correct: I was comparing dates with strings and solved it like this:

My attempts to solve this were making me stray further and further from the solution: in the last attempt before I started over completely, I was converting '2006' into a date value... *sigh* learning several things at the same time (SQL and TBS in my case) can be timeconsuming :)

Anyway: looks great and I feel I haven't scratched the surface of possibilities of TBS!