Thanks Adam,

I can see where I was going wrong. I'm just a hack really..

the select * was just an example I do have real code for the entire statement.

yes the code is being used inside a procedure.

problem has occured in isql & the program.

I think once I have updated all of the code to represent the new statement everything should work just fine.

We are really impressed with the speed of fb1.5 and can't wait to get it fully tested and up and running.

Helen - My boss is going to be at your conference in Melbourne... I'm sure he'll be keen to chat with you ;-)

Best Regards Julie

----- Original Message -----
From: Adam
To: firebird-support@yahoogroups.com
Sent: Thursday, April 06, 2006 3:17 PM
Subject: [firebird-support] Re: USER key word not working in fb 1.5


> Hi Adam,
>
> Thanks for your detailed response.
>
> You're sp_test did work on fb1.5
>
> Here is an example of my code;
>
> select *
> from users a, USERS_PROFILES b
> left join loc_office d on a.office = d.office
> where a.STOCK_PROFILE = b.PROFILE_NAME
> and a.username=user

That is a strange mix of SQL 89 and SQL 92. I will convert it all to
SQL92 to make it more readable.

select *
from users a
join USERS_PROFILES b on (a.STOCK_PROFILE = b.PROFILE_NAME)
left join loc_office d on (a.office = d.office)
where a.username=user

Does this work? If not, can you substitute user for a constant string
and see if that works. eg.

select *
from users a
join USERS_PROFILES b on (a.STOCK_PROFILE = b.PROFILE_NAME)
left join loc_office d on (a.office = d.office)
where a.username='Adam'

I see that all the joins are carefully aliased which is good. Select
* is generally a bad idea, because your program will often fall to
pieces if someone adds a new field to users etc.

Where is this code being used? If it is inside a stored procedure it
will need an into statement. If you don't specifically name the
fields, then the position may be out causing a problem. Is it
possible to have multiple loc_office records with the same 'office'
value. If so, you will get multiple rows from the select, which may
be an issue if you are using the select inside an update statement or
something along those lines.

Does the problem occur in iSQL or just in your program?







++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




SPONSORED LINKS Technical support Computer technical support Compaq computer technical support
Compaq technical support Hewlett packard technical support Microsoft technical support


------------------------------------------------------------------------------
YAHOO! GROUPS LINKS

a.. Visit your group "firebird-support" on the web.

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


------------------------------------------------------------------------------



[Non-text portions of this message have been removed]