Dots (.) in SOQL Load.. SOQL doesn't allow aliasing, the dilemma!

[#1]

So below is a simplified version of my query..

Opportunity:

LOAD     Id,

Name,

OwnerId,

Account.OwnerId,

Broker_Account__r.OwnerId;

SQL SELECT Id,

Name,

OwnerId,

Account.OwnerId,

Broker_Account__r.OwnerId

FROM Opportunity

The problem is:

• QlikView doesn't load "Account.OwnerID" and "Broker_Account__r.OwnerId" because it can't find the field (Script Error: Field not found - <Account.OwnerId>)
• SOQL doesn't allow for field Aliasing.

So, QV wants me to alias the field because it doesn't like dots, and SOQL won't let me alias and will include the dot!

Any idea on how I get around this?

[anbu1984]

Load all fields and drop unnecessary fields as below

Opportunity:

LOAD     *;

SQL SELECT *

FROM Opportunity;

Drop fields field1,field2;

[flipside]

Interesting query language SOQL!!

I've never used it myself, but a quick read seems to suggest the dot notation in fields is referring to a parent object (is that right?). If that is the case, are you able to return from each relevant table, with a linking key field between the tables and rebuild them in Qlikview.

I'm thinking in this case it would be three separate queries (in this case basing the link on id, but it could be something else) ...

SQL SELECT Id,

Name,

OwnerId

FROM Opportunity

SQL SELECT Id,

OwnerId

FROM Account

SQL SELECT Id,

OwnerId

FROM Broker_Account__r

Or does it not work that way?

flipside

[#4]

That is EXACTLY what it's doing "." denotes a parent relationship and  "__r" denotes a custom relationship (this is similar to a join in normal SQL).

What I could do is create two mapping tables of the fields giving me issues, but I am trying my BEST not to do that and to stick to one table load since the load script itself is getting pretty big and I didn't want to do three DB calls when I could do one (the script in total does about 20 DB calls and a LOT of logic, so trying to keep is as simple as possible)

[#5]

The fields I'm pulling will not show up if I do select * since those are relationship fields, this is similar to a join in SQL.

[flipside]

Okay. If Qlikview doesn't like the dot notation, and I would try using as many variations of brackets and quotes as I could to see if it works, is it possible to build the select statement top down as this uses sub-queries and avoids dot notated fields.

www. sfdc99.com/2013/06/24/example-how-to-write-a-cross-object-soql-query-part-2/

flipside

[#7]

Thanks, I'll experiment some more before I give up on this approach and split the query into simplified ones.

[#8 · Accepted solution]

Wanted to update the thread in case someone was looking for an answer.

I created two Mapping tables with the joined fields and applied the map within the LOAD statement of the Opportunity table