I have created a SQL query to search for field name of CONVERSION_ADVICE_NO from 2 tables , RM_GRD_CONV and RM_GRD_CONV3.Both of these tables contain almost same field length. The key that I used to search for are serial numbers which is stored in multi field of the table.

what is peculiar is that there are a range of serial numbers which are stored in 2 tables: RM_GRD_CONV, RM_GRD_CONV3. when I run a query , some of them will produce results, some of them don't. EVEN THOUGH those serial numbers are stored in those tables. (I used LIST to check out these "missing" serial numbers when my SQL query failed.

I'll post the SQL query in the next message.

CMD =SQL CREATE VIEW BM_BAY2 (CURRENT_ADVICE_NO,HISTORICAL_ADVICE_NO,"

CMD := "TRANSACTION_CODE,SERIAL_NO) "

CMD := " AS SELECT DISTINCT RM_GRD_CONV.CONVERSION_ADVICE_NO,"

CMD := " RM_GRD_CONV3.CONVERSION_ADVICE_NO,RM_GRD_CONV.TRANSACTION_CODE,"

CMD := " RM_GRD_CONV.SERIAL_NO FROM RM_GRD_CONV,RM_GRD_CONV3 WHERE"

CMD := " (RM_GRD_CONV.TRANSACTION_CODE=:QUOTE(TRAN_CODE)

CMD := " AND RM_GRD_CONV.SERIAL_NO BETWEEN ": QUOTE(START_SN)

CMD := " AND ": QUOTE(END_SN) :" ) AND "

CMD := " (RM_GRD_CONV3.TRANSACTION_CODE=:QUOTE(TRAN_CODE)

CMD := " AND RM_GRD_CONV3.SERIAL_NO BETWEEN ": QUOTE(START_SN2)

CMD := " AND ": QUOTE(END_SN2) :" ) "

PERFORM CMD

Note:

Pardon me for the formatting, I can't seem to align the codes in 1 line. This is how I arranged my SQL query to run in my coding.

TRAN_CODE=transaction code.

START_SN =start serial number

END_SN2 =end serial number

START_SN2 same as START_SN

END_SN2 same as END_SN

sample of search key is TRAN_CODE*START_SN , which is like this: AB*20140004

Is there anything wrong with my SQL syntax?

some of them will produce results, some of them don't. EVEN THOUGH those serial numbers are stored in those tables. (I used LIST to check out these "missing" serial numbers when my SQL query failed.

Note: what I mean here is that even though those range of serial numbers are stored in both tables:

RM_GRD_CONV, RM_GRD_CONV3. The query are display View with a no results.

For example, for serial numbers AB*23550070 to AB*23550072 and AB*23550075 to AB*23550076. All these serial numbers are stored in

RM_GRD_CONV and RM_GRD_CONV3 tables and same CONVERSION_ADVICE_NO field.

However, when I used AB*23550070 to AB*23550072*, it'll display the correct CONVERSION_ADVICE_NO fields values.

But, when I used AB*23550075 to AB*23550076, the SQL view display no results.

What could cause this error?

at first blush i didn't see anything wrong with your sql syntax though i have to say that it has been quite a while since i sql'd in Arev.

So you are saying that the same query (well at least the selection) does work correctly using LIST/SELECT?

well , when i run LIST RM_GRD_CONV TRAN_CODE SERIAL_NO CONVERSION_ADVICE NO WITH TRAN_CODE=WR" AND SERIAL_NO BETWEEN "12330001" AND "12330004"

or LIST RM_GRD_CONV3 TRAN_CODE SERIAL_NO CONVERSION_ADVICE NO WITH TRAN_CODE=WR" AND SERIAL_NO BETWEEN "12330001" AND "12330004"

separately in TCL, there will be results. but if SQL the as in the 2nd statement. SQL View shows no results. which is strange.

so.. I assumed the SQL statement might've been wrong somewhere.

Do you need to add a clause to join the two tables?

I think that most people here do not use SQL in Arev. I used it only when using the SQL Server bond.

CMD =SQL CREATE VIEW BM_BAY2 (CURRENT_ADVICE_NO,HISTORICAL_ADVICE_NO,"

CMD := "TRANSACTION_CODE,SERIAL_NO) "

CMD := " AS SELECT DISTINCT RM_GRD_CONV.CONVERSION_ADVICE_NO,"

CMD := " RM_GRD_CONV3.CONVERSION_ADVICE_NO,RM_GRD_CONV.TRANSACTION_CODE,"

CMD := " RM_GRD_CONV.SERIAL_NO FROM RM_GRD_CONV,RM_GRD_CONV3 WHERE"

CMD := " (RM_GRD_CONV.TRANSACTION_CODE=:QUOTE(TRAN_CODE)

CMD := " AND RM_GRD_CONV.SERIAL_NO BETWEEN ": QUOTE(START_SN)

CMD := " AND ": QUOTE(END_SN) : " ) AND "

CMD := " (RM_GRD_CONV3.TRANSACTION_CODE=:QUOTE(TRAN_CODE)

CMD := " AND RM_GRD_CONV3.SERIAL_NO BETWEEN ": QUOTE(START_SN)

CMD := " AND ": QUOTE(END_SN) :" ) "

PERFORM CMD

The above statement was reason I used SELECT , but it has the shortcoming which I mentioned in the previous posts.

Some advice numbers will not appear in the SQL VIEW even though it's supposed to be there.

The purpose of the above was to check which advice number a certain serial number belongs into, these advice numbers can either appear once /twice in the historical advice no. column OR once / twice in the current advice no. column.

Since then, I have use another method to solve this problem.

SQL INSERT INTO, which I will show in the next message.

CMD = SQL INSERT INTO RM_ADV_CHECK(TRAN_CODE,SERIAL_NO,"

CMD := " HISTORICAL_ADVICE_NO,CONVERTED_GRADE,ORIGINAL_GRADE)"

CMD := " SELECT TRANSACTION_CODE,SERIAL_NO,CONVERSION_ADVICE_NO,"

CMD := " CONVERTED_GRADE,QLY_GRADE"

CMD := " FROM RM_GRD_CONV3 WHERE TRANSACTION_CODE=:QUOTE(TRAN_CODE)

CMD := " AND SERIAL_NO BETWEEN ": QUOTE(START_SN)

CMD := " AND ": QUOTE(END_SN) :" "

PERFORM CMD

CMD3 = SQL INSERT INTO RM_ADV_CHECK(TRAN_CODE,SERIAL_NO,"

CMD3 := " CURRENT_ADVICE_NO,CONVERTED_GRADE,ORIGINAL_GRADE)"

CMD3 := " SELECT TRANSACTION_CODE,SERIAL_NO,CONVERSION_ADVICE_NO,"

CMD3 := " CONVERTED_GRADE,QLY_GRADE"

CMD3 := " FROM RM_GRD_CONV WHERE TRANSACTION_CODE=:QUOTE(TRAN_CODE)

CMD3 := " AND SERIAL_NO BETWEEN ": QUOTE(START_SN)

CMD3 := " AND ": QUOTE(END_SN) :" "

PERFORM CMD3

The CMD will run ,followed by CMD3.

I have made HISTORICAL_ADVICE_NO,CURRENT_ADVICE_NO,CONVERTED_GRADE,ORIGINAL_GRADE fields to be multi value.

But, the problem is.The INSERT INTO in CMD will insert the CONVERTED_GRADE,ORIGINAL_GRADE value in FULL.

But the INSERT INTO in CMD3 will CONVERTED a NULL ORIGINAL_GRADE, and 1st 2 characters of CONVERTED_GRADE value.

However, the values of ORIGINAL_GRADE,CONVERTED_GRADE will be in FULL.

When I create 2 extra multi value columns eg. ORIG_GRADE2,CONV_GRADE2 and have CMD3 INSERT INTO into those columns,the problem above doesn't occur in this case.

Please advise. Thank you

Chang

The simplest advice we could give you is "don't use AREV's SQL for complex tasks". As noone in the Rev community uses SQL it is quite possible that you are discovering bugs which will NOT be fixed as AREV is not a supported product. Now you could move your application to AREV32 and then log a support call or you could just rewrite your update using AREV style logic.

We can pretty much guarantee that the AREV program would take less than an hour to write.

The Sprezzatura Group

World leaders in all things RevSoft

Revelation Conference 2007, London - Wednesday 27th June Click here to register for the premier Revelation Software EMEA event of 2007

View this thread on the forum...