Insert report field into my SQL query?

[magicrjm]

Using Crystal Reports 11.

I am attempting to add a report field into SQL Expression. I have already added the SQL expression field to my report to grab the data from the database which is as follows:

Field name = lastbatch

(select top 1
cus_name
from dbo.ppordfil_sql
where ppordfil_sql.item_no = 'TI032489IM'
order by entered_dt desc)

What I am trying to do is replace the item_no 'TI032489IM' with a report field such as "Field 7" from my report. I am unclear if I am even going about this the right way. Can someone point me in the right direction?

 

[briangriffin]

Nope, SQL Expressions have to be evaluated solely on the database side. You may be able to accomplish this with a combination of a command object and a parameter. Describe what you're trying to do in more detail and there's probably a way to do it.

 

[magicrjm]

The crystal report is a production work order from our ERP system Macola ES. The user enters the work order in Macola then prints it. The user will input a work order number and the report will populate multiple fields from that work order. To be specific I am trying to add a field on the report that will display the most recently entered batch number for the item number that is in the work order.

item_no = Field 7 in the report

The t-sql below above achieves what I would like to display on the report when searching the database. The text after /* and before */ are my comments.

/* Search for the most recent order entered in the ppordfil_sql database
where the item number equals Field7 */

select top 1
cus_name /* Batch number field */
from dbo.ppordfil_sql
where ppordfil_sql.item_no = "Crystal Report Field 7"
order by entered_dt desc

 

[Turkbear]

Hi,
Even in TSql I find it hard to believe that :

where ppordfil_sql.item_no = "Crystal Report Field 7"

would retreive any records, since Crystal Report Field 7 is not in the database.

Have you tried:

select top 1 cus_name 
from dbo.ppordfil_sql
where ppordfil_sql.item_no =  lastbatch
order by entered_dt desc

Can you post the description of the ppordfil_sql database table?





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

 

[magicrjm]

No No. "Crystal Report Field 7" is what I am trying to accomplish. Field 7 in the Crystal Report is the item_no in my report that I want to have the report query off. See my first post to what I would actually type in query analyzer.

The ppordfil_sql table is the production order table. The table contains information for each production order in POP. The PPORDFIL information includes order type, inventory location, quantity, cost, order source (the package from which the order orginiated - OE, MRP, or POP) and dependent order information.

 

[magicrjm]

I figured it out. All I had to do was create a new formula field with the syntax of Maximum ({PPORDFIL_SQL.cus_name}).