Creating SQL Expression with alias

[pl1101]

How do you create a sql Expression using an alias. When I try the creating from a duplicate, but changed the table to the alias, I get an error message that the table cannot be found.
Example:
(select sum(req_1.qty) from req_1
where mstr_1.pn=req_1.pn)

 

[Turkbear]

Hi,
The alias is only 'known' to Crystal - the database does not have a table by that name and since Sql Expressions are handled by the database directly, an alias cannot be used in them.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[GJParker]

Your SQL is invalid as you haven't defined the table mstr_1

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[FireGeek21]

GJParker is right. Your SQL is invalid:

(select sum(req_1.qty)
from req_1
where mstr_1.pn=req_1.pn)

If req_1 is your alias, you need to identify your table name. If your true table name is REQUISITION, note the correction below. "from REQUISITION req_1" is how to assign an alias in a SQL statement.

(select sum(req_1.qty)
from REQUISITION req_1
where req_1.pn = mstr_1.pn)

Regarding mstr_1 link, this will work but only if mstr_1 is a true table name already pulled into your report.

Hope this helps.

RSGeek



RSGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[lbass]

In certain versions of Crystal (why you should always identify your version), you cannot reference the table within the summary:

(
select sum(`qty`)
from table A
where A.`groupfield` = table.`groupfield`
)

Why do you feel you need an alias in the first place?

-LB

 

[pl1101]

I am using version XI R2... I have a report that needs to pull in the parent of a table and then reference the child in another table. The quantity is link to the parent and the same table(alias) has to be linked to the child to get the quantity.