Convert Float to Integer in SQL query

Now I answered myself the question about number format, just modify format property of Text Object.

How about question 2?

What kind of database are you using?

In SQL server you can convert to integer with

convert(int, PLANS.PLAN_NO)

But in other databases it might be somewhat different.

Thank auke_t
My DB is Oracle, so the function convert takes no effect, I also try convert(decimal(5,0),PLANS.PLAN_NO)

Do you know what similars to function convert in Oracle?

But I've already solved the question about number format, so I don't need the function similar to convert() in Oracle. My current problem is:

2. In my CR there is an ordinal number field, how to implement this (in SQL or in CR?) ?

Thanks
2thungrac

But I've already solved the question about number format, so I don't need the function similar to convert() in Oracle. My current problem is:

2. In my CR there is an ordinal number field, how to implement this (in SQL or in CR?) ?

Thanks
2thungrac

In SQL-server this can be done as described below. (Maybe it can be done easier in oracle, but this should work as well)

SELECT
*,
(SELECT COUNT(*) FROM table t2 WHERE t2.id <= t1.id) AS rownumber
FROM
table t1

Your solution will be wrong if ID field not continuous.

Thanks.
2thungrac

ID must be unique, but doesn't need to be continuous.

I must select from 6 tables, and find difficult to implement your idea. Have you ever implemented your idea for more than 2 tables?

My original SQL query :
SELECT  PROJECTS.PROJ_ID, VENDORS.VNDR_NO,
FROM    PROJECTS INNER JOIN (BID_LETTING INNER JOIN (VENDORS INNER JOIN ((BIDS INNER JOIN ADDENDUMS ON BIDS.BID_ID = ADDENDUMS.ADDN_BID_ID) INNER JOIN PLANS ON BIDS.BID_ID = PLANS.PLAN_BID_ID) ON VENDORS.VNDR_NO = BIDS.BID_VNDR_NO) ON BID_LETTING.BIDL_BID_NUMBER = BIDS.BID_BIDL_BID_NUMBER) ON PROJECTS.PROJ_ID = BID_LETTING.BIDL_PROJ_ID
WHERE   PROJECTS.PROJ_ID = 1068

I change it to:
SELECT  PROJECTS.PROJ_ID, VENDORS.VNDR_NO,
            (select count(*) from vendors t1 where t1.VENDOR_NO = t2.VENDOR_NO) as rowcount
FROM    PROJECTS INNER JOIN (BID_LETTING INNER JOIN (VENDORS t2 INNER JOIN ((BIDS INNER JOIN ADDENDUMS ON BIDS.BID_ID = ADDENDUMS.ADDN_BID_ID) INNER JOIN PLANS ON BIDS.BID_ID = PLANS.PLAN_BID_ID) ON VENDORS.VNDR_NO = BIDS.BID_VNDR_NO) ON BID_LETTING.BIDL_BID_NUMBER = BIDS.BID_BIDL_BID_NUMBER) ON PROJECTS.PROJ_ID = BID_LETTING.BIDL_PROJ_ID
WHERE   PROJECTS.PROJ_ID = 1068

but the rowcount field is unchange, it show the quantity of VENDORS table