Hi Kay,
Have you dumped the query? I've found a few cases where a duplicate column
name, even though it is a join node, is returned in duplicate:
SELECT h.*, r.*
FROM horse h, rider r
WHERE h.smell = r.smell;
Might return a columnlist like
('horse_name','h.smell','r.smell','rider_name')
OK, dubious example, but you get the picture. No smell column.
If you're even unsure about the column names that may be present, alias
those that you need to refer to - it's also good form to avoid a SELECT *
when feasible, both for performance and explicit declaration of variables.
SELECT h.*, h.smell AS odorous_severity, r.*
FROM horse h, rider r
WHERE h.smell = r.smell;
Also, if the sysadmin really has put your user in the db_owner role, you
should be able to change ownership of objects in the database. In EM,
right-click -> Design View on a table. Then in Properties, you can select a
new owner (either an actual user in the database, a security group, or the
built-in literal dbo). It can be slow, but saves time waiting for someone
else (or searching for the right syntax to do it in QA). ;)
HTH,
Chris
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On
Behalf Of Kay Smoljak
Sent: Monday, 20 February 2006 1:55 PM
To: [email protected]
Subject: [farcry-dev] custom rule join query not returning all columns
I have the following query in a custom rule which returns records as
expected in Query Analyzer:
SELECT csg_farcry.pwPosition.*, csg_farcry.pwEmployer.Company
FROM csg_farcry.pwPosition INNER JOIN
csg_farcry.pwEmployer ON
csg_farcry.pwPosition.Employer = csg_farcry.pwEmployer.ObjectID
WHERE (csg_farcry.pwPosition.status IN ('approved'))
ORDER BY csg_farcry.pwPosition.label
However, when the rule runs in code, the "company" column is not
passed (everything out of the pwPosition table is there). I copied the
news rule originally and just replaced the object names as necessary.
Is there any FarCry reason why the join wouldn't work? It's odd
because outside of FarCry, the joined field is returned. But the same
SQL code copied and pasted into my rule doesn't work. I've commented
out all other code in the execute method so I'm positive that this is
where the problem is.
I'm just not having a good day with databases! The client's sysadmin
has yet to get back to me with the results of the re-owning exericise,
for any CFTalkers or CFAussie peeps who are following the daily soap
opera that is my development life :D
--
Kay Smoljak
http://kay.zombiecoder.com/
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"farcry-dev" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/farcry-dev
-~----------~----~----~----~------~----~------~--~---
