How to Express SQL "IN" statment in Progress

[mmarshall]

I'm working on building a new configurator in ERP software Epicor that uses Progress while my old configurator used SQL. Trying to adapt some of these expressions to Progress is proving to be challenging since I don't know the language well yet. I'm particularly interested in the use of the "IN" statement and capturing all of my possibilites in parentheses instead of redundantly writing {Finish} = '74M' OR {Finish} = '75D' OR {Finish} = '75L'.....etc.

Below is an SQL expression that I'd like to rewrite in Progress. [ItemNumber] = 'AWS1296B' AND {Finish} IN ('74M','75D','75L','76G','76W','77D','78') Lastly, if anyone knows of any references that compare and contrasts the various statements between the two languages, it would be helpfull to me. Thanks in advance, Michael

 

[Cringer]

I'd be very wary of trying to wholesale rewrite SQL to Progress as SQL is record set oriented whilst Progress is Record oriented meaning that the logic isn't going to be the same.

The statement I think you're after is LOOKUP().

 

[mmarshall]

What I decided to do is use the Index Function to represent an "in" from SQL.


INDEX("abc","zenbdh, wadsn, assgn", 1)

If the INDEX is > 0 then exists else it doesnt exist.

Thanks!

 

[SergioC]

Hi, this example demonstrates the use of LOOKUP and INDEX.

[FONT=courier new]DEFINE VARIABLE finish AS CHAR INITIAL '69G' NO-UNDO.[/FONT]
[FONT=courier new]DISPLAY LOOKUP(finish,'75D,69G,78') LABEL 'LOOKUP'/* ENTRY # 2 */[/FONT]
[FONT=courier new]        INDEX('75D,69G,78', finish) LABEL 'INDEX' /* POSITION # 5 */.[/FONT]

Regards.

 

[LarryD]

What I decided to do is use the Index Function to represent an "in" from SQL.


INDEX("abc","zenbdh, wadsn, assgn", 1)

If the INDEX is > 0 then exists else it doesnt exist.

Thanks!

Be aware that INDEX looks for the characters you have in a string as opposed to LOOKUP which searches for the value in a delimited list.

e.g. INDEX("123,34,5678","12") will find the value of 12 (would return a 1), whereas LOOKUP("12","123,34,5678") will NOT find it.

 

[mmarshall]

Hi, this example demonstrates the use of LOOKUP and INDEX.

[FONT=courier new]DEFINE VARIABLE finish AS CHAR INITIAL '69G' NO-UNDO.[/FONT]
[FONT=courier new]DISPLAY LOOKUP(finish,'75D,69G,78') LABEL 'LOOKUP'/* ENTRY # 2 */[/FONT]
[FONT=courier new]       INDEX('75D,69G,78', finish) LABEL 'INDEX' /* POSITION # 5 */.[/FONT]

Regards.

Exactly!!

I've been trouble shooting the INDEX for an hour now becuase my arguments were in the wrong place. We'll end up using the LOOKUP statment...seems a little cleaner.

Thanks to everyone for chiming in!

Marshall