I have lots of these "join" sytax statements in my metadata code.

Is there anyway I can conver the lot? Or will I need to manual update the code?

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]