Hello,
I have a requirement to generate a report using SQL Query Data.

We have two tables i.e. tccom100 and tctax400

1. i need to get all record from tccom100 + related record from tctax400 where tccom100.bpid = tctax400.bpid (in short, i need to put left join here)

2. in the same query it also demanded to get one extra field tctax400.fovn from table tctax400

i did every thing what i can do but i couldn't find a way to get all records from main tables, related record from subtable and an extra field from sub table.
*All i have to do with standard sql.


is there any body who can help me with this.

Thanks in advance.
/Aamir

Hi Aamir,

I think REFERS TO clause will help you to achieve your requirement.

Try "tccom100.bpid REFERS TO tctax400.bpid".

Hope it help you.

Regards,
--Rahul

I think what you are trying to do is get all records from tccom100, even if they do not have records in tctax400 - correct? Are you using a session or easy sql? I always keep it simple in a session - outer main query would get tccom100 and then I include an inner query to look in tctax400. There are other constructs, but not sure they work in easy sql.

Thanks for your reply guys.

Rahul, in my case REFERS TO clause is not giving expected result. It only gives matching result but in addition i need all records from first table as well.

mark_h, yes you are right that it is easy in session script. But my problem is that i have to do it easy sql where i have to use standard SQL.

my problem is still there :mad: ... is there any one who can help me with it.... :confused:

... you can make what is possible in sqlquerry .. and then put the rest of the logic in the report script of the related report which you can always edit ... as long you not overwrite it from easy sql, i.e

SQL Query:

1. i need to get all record from tccom100 + related record from tctax400 where tccom100.bpid = tctax400.bpid (in short, i need to put left join here)

Report script:
detail.1: (or after.recieve.data)
before.layout:
2. in the same query it also demanded to get one extra field tctax400.fovn from table tctax400

Regards,

Kozure

unref clear (http://www.baanboard.com/baanboard/showthread.php?t=54136&highlight=unref) - you can try it. Something like:

tccom100.bpid REFERS TO tctax400.bpid unref clear


Now I have never used this in easy sql and maybe only 1 time in some code. The only solution I have done is select the main table in the script. Then in the report select data from another table(multiple records) if they exist. Then use the layout.again() structure.