subselect based on another subselect

Applications Report PDF
#1

I’m trying to create a report. It has multiple subselects in it based on one another. I get an error with the where clause in the query for the second sub select.
Here is how i’m selecting the field:
AccountNo = {group.AccountNo}

with group being the first subselect

It says Unknown column ‘Array’ in ‘where clause’ on runtime.

Any help would be much appreciated. Thanks in advance.

#2

[QUOTE=gamer99;19279]I’m trying to create a report. It has multiple subselects in it based on one another. I get an error with the where clause in the query for the second sub select.
Here is how i’m selecting the field:
AccountNo = {group.AccountNo}

with group being the first subselect

It says Unknown column ‘Array’ in ‘where clause’ on runtime.

Any help would be much appreciated. Thanks in advance.[/QUOTE]

I’m interested in the {group.AccountNo} part. As fields between {} are treated as formfield variables, I think that {group.AccountNo} is not a valid fieldname for a formfield. If SC is allowing a period in a fieldname then I advise to get rid of that since it will generate all kind of problems. I.e. by calling the field groupAccountNo.

#3

Thanks for replying.

Yeah, its not really a form field. It was created by the first subselect.

Basically my problem is I dont know how to call/compare the first subselect field with the second subselect field.

Some more details:

Here is the query for my first subselect:
SELECT
GAccountNo,
GAccountDescription,
Debit,
Credit
FROM
coagroup
WHERE
MAccountNo = {MAccountNo}

Here is the query for the second subselect:
SELECT
CAccountNo,
CAccountDescription,
Debit,
Credit
FROM
coacontrol
WHERE
GAccountNo = ???

The where clause is the problem. I dont know how to call the GAccountNo from the first subselect. The field list is showing it as: group.GAccountNo

#4

It’s not clear for me what you exactly want to achieve. But you are trying to create some kind of join between two tables?

#5

Lets see. I’ve got 4 tables: Master, Group, Control and Sub. I’ve created the PDF report with the Master table and have joined the Group table to it via a subselect. Now I need to create a second subselect to join the data from the Control table to the Group table.

In the field list the the fields from the group table have been added like: group.GAccountNo, group.AccountDescription and so on. So I dont know how to link group.GAccountNo to GAccountNo in the WHERE clause of the Control subselect query (the Control table has a field called GAccountNo).

Hope this clarifies my situation.

Thanks for replying.

#6

[QUOTE=gamer99;19302]Lets see. I’ve got 4 tables: Master, Group, Control and Sub. I’ve created the PDF report with the Master table and have joined the Group table to it via a subselect. Now I need to create a second subselect to join the data from the Control table to the Group table.

In the field list the the fields from the group table have been added like: group.GAccountNo, group.AccountDescription and so on. So I dont know how to link group.GAccountNo to GAccountNo in the WHERE clause of the Control subselect query (the Control table has a field called GAccountNo).

Hope this clarifies my situation.

Thanks for replying.[/QUOTE]

Ah yes, I understand. I have to admit that I don’t have that much experience with the pdf stuff to answer this question in a relyable way. Will try to find some time to look in to that, but I hope someone else can answer this question meanwhile.

#7

No worries. Thank you for your time and effort.

#8

Hola seria algo asi, al menos en Sql :

Here is the query for the second subselect:
SELECT
CAccountNo,
CAccountDescription,
Debit,
Credit
FROM
coacontrol
WHERE
GAccountNo in (
SELECT
GAccountNo
WHERE
MAccountNo = {MAccountNo}
)

#9

Thanks for the reply. I’ll try it out.