Need Query in SQL for this progress Code

[Raj2010]

Hi All,

I am using Open Edge 10.2B, Microsoft Windows XP Version 2002 SP3,
I need SQL Query for the below Piece of Progress Code if any Expert in SQL as Well as Progress Developer Can help me?

FOR EACH arm.loadheader NO-LOCK:
/* below customer can contain may customer for the single load-id so i need to get first record from the below arm.Cust TABLE */
FIND FIRST arm.cust
WHERE arm.cust.customer_nbr = arm.loadheader.cust_nbr
NO-LOCK NO-ERROR.
/* As loadheader contains freight code as well as Carrier code i am assigning below with 2 variables(pv_freight_code_desc, pv_code_desc) and finally displaying it in single shot */
FIND FIRST arm.usertable
WHERE arm.usertable.flag_active
AND arm.usertable.Codetype = "FREIGHT"
AND arm.usertable.codevalue = arm.loadheader.freightcode
NO-LOCK NO-ERROR.
pv_freight_code_desc = arm.usertable.codedesc.
FIND FIRST arm.usertable
WHERE arm.usertable.flag_active
AND arm.usertable.Codetype = "CARRIER"
AND arm.usertable.codevalue = arm.loadheader.carriercode
NO-LOCK NO-ERROR.
ASSIGN pv_code_desc = arm.usertable.codedesc.
DISP
arm.loadheader.load_id
arm.loadheader.cust_nbr
arm.cust.full_name
arm.loadheader.carriercode
pv_code_desc
arm.loadheader.freightcode
pv_freight_code_desc
WITH 2 COL.
END.

I need Equivalent SQL statement for the above Progress Query.
Please help me. Thanks in Advance

Regards,
Raj
(Jr.Developer)

 

[Cringer]

Trying to translate ABL to SQL will land you in all sorts of bother.

 

[TomBascom]

Trying to think SQL while coding in the 4gl is most certainly the road to hell.

Translating 4gl to SQL might not be quite so bad so long as the intent is to use the query in some external tool that is, itself, a SQL tool.

Lots of people come to the 4gl from a SQL background so it might also be helpful for them to take a 4gl query and at least think about how they would do that with SQL (and in many cases realize how much easier 4gl is...) If you understand SQL the 4gl statements ought to be fairly easy to translate. Hint: the WHERE clause is usually more or less the same.

If you understand neither 4gl, nor SQL then you either need some training or you are in the wrong line of business.

Raj2010 -- what have you tried and what problems have you encountered?

 

[Raj2010]

Hi Tom,

My Problem is when i write query in SQL its creating problem with Below Code
FIND FIRST arm.cust
WHERE arm.cust.customer_nbr = arm.loadheader.cust_nbr
NO-LOCK NO-ERROR.

As progress selects first record from the above statement but in SQL it will not.

Secondly with below code

FIND FIRST arm.usertable
WHERE arm.usertable.flag_active
AND arm.usertable.Codetype = "FREIGHT"
AND arm.usertable.codevalue = arm.loadheader.freightcode
NO-LOCK NO-ERROR.
pv_freight_code_desc = arm.usertable.codedesc.
FIND FIRST arm.usertable
WHERE arm.usertable.flag_active
AND arm.usertable.Codetype = "CARRIER"
AND arm.usertable.codevalue = arm.loadheader.carriercode
NO-LOCK NO-ERROR.
ASSIGN pv_code_desc = arm.usertable.codedesc.

As arm.usertable.codevalue is compared twice above with different code values from arm.loadheader.freightcode and arm.loadheader.carriercode i don't no how to handle this in SQL as i need to create a simple report out of it.

Hope above explanation will help me to get some my required Query?

Regards,
Raj.

 

[TomBascom]

What SQL tool are you using to create this query?

 

[Raj2010]

WinSQL Lite

 

[TomBascom]

How would you normally go about selecting one specific row in a SQL query?

 

[Raj2010]

i am not aware much with SQL coding.

Select Top 1 * from table_name.

 

[TomBascom]

Also -- I asked "what have you tried?". I was expecting to see some code so that I could better understand the context of the problems that you are reporting.

 

[medu]

can we ask why do you need to access the application data through SQL? is it reporting, application integration, data integration, ODBC/JDBC??? there might be better approaches than just 'translating' SQL