Conditions while loading via Edit Script

[easternfish]

Hi All,

Is this possible?....

LOAD

FILED1,

FIELD2,

FIELD3,

FIELD4,

IF(FIELD2 = 'X' And FIELD4 ='Y',0,1) AS FIELD42;

SQL SELECT *

FROM ....................WHERE FIELD42 > 0;

Thank you in advance!

[swuehl]

No, FIELD42 is not known at the time the WHERE clause is executed. Either use the full condition in your SQL SELECT WHERE clause, or a preceding load with your new field:

LOAD * WHERE FIELD42 > 0;

LOAD

FILED1,

FIELD2,

FIELD3,

FIELD4,

IF(FIELD2 = 'X' And FIELD4 ='Y',0,1) AS FIELD42;

SQL SELECT *

FROM ....................;

But this will query the complete record set from your DBMS, then only limit the records on your QV client side.

[easternfish]

Hi,

Can you please provide a sample on how to do it.

Thank you

[swuehl]

Sorry, I don't quite understand. What about the script snippet in my previous post?

[easternfish]

Oh sorry I didn’t notice.

LOAD * WHERE FIELD42 > 0;

Where would I input this information on a new TAB or same TAB above the SQL SELECT *.

Because I tried using it this way and it wouldn’t run.

LOAD * WHERE FIELD42 > 0;

LOAD

FILED1,

FIELD2,

FIELD3,

FIELD4,

IF(FIELD2 = 'X' And FIELD4 ='Y',0,1) AS FIELD42;

SQL SELECT *

FROM ....................;

[swuehl]

You are getting an error when executing?

I think it should run, maybe I am missing something.

This should run as an example:

LOAD * WHERE Val >1;

LOAD Recno() as RecID, RAND()*3 as Val AutoGenerate 100;

But as said, it might be better to add the WHERE to the SQL, so you avoid to query too many records first hand:

LOAD

FILED1,

FIELD2,

FIELD3,

FIELD4,

IF(FIELD2 = 'X' And FIELD4 ='Y',0,1) AS FIELD42;

SQL SELECT *

FROM ....................WHERE FIELD2 = 'X' AND FIELD4 ='Y';

This should limit your records when querying the DB and you should only get a value of 1 in field FIELD42.

[easternfish]

Ok Let me try it again. quick question I have this report running perfect in MS Access and I have multiple IIF and Criteria in the design followed by a multitude of Left Joins (for 10 different tables). What do you recommend would be the proper way to transition and move this report without having any mayor difficulties?

Thank you for all your help so far!!!!