Archives of the TeradataForum

Message Posted: Wed, 01 Aug 2007 @ 18:11:49 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi All,

I have this interesting results to be generated in one of my module

I have two Tables with Structure as follows.

     FIELD_ERR_DATA
     Column Name     Err_TYPE        NO_ERR_RECORDS
     BUSINESS        E01                    100
     PRODUCT         E02                      5
     COLLATERAL      B01                      7
     PRODUCT         E01                      1
     ;               ;                        ;


     DETAIL_TABLE Sample Data        DETAIL_TABLE

     RECORD_NUMBER   BUSINESS        PRODUCT COLLATERAL      NAME    ;
           1         E01-XEROX       COPIER  REAL ESTATE     ANKIT   ;
           2         E01-HP          LAPTOP  FINANCIAL GURANTEE      RAHUL   ;
           3         DELL            MONITOR LEASE   ABHI    ;
           4         DASANI E02-WATER       BOTTLES KETAKI  ;
           5         STAPLES E01-NOTBOOK     PENS    SACHI   ;
           6         BESTBUY COMPUTERS       B01-PRINMTER    SUSHIL  ;

I Want to be able to run and generate following SQL dynamically.

     SELECT * FROM DETAIL_TABLE WHERE
     BUSINESS LIKE 'E01%' SAMPLE (200 or 10% of No of error records which
     ever is smaller and should give atleast 1 record)
     UNION SELECT * FROM DETAIL_TABLE WHERE
     PRODUCT LIKE 'E02%' SAMPLE SAMPLE (200 or 10% of No of error records
     which ever is smaller and should give at least 1 record)
     UNION SELECT * FROM DETAIL_TABLE WHERE
     COLLATERAL LIKE 'B01%' SAMPLE SAMPLE (200 or 10% of No of error records
     which ever is smaller and should give atleast 1 record)
     UNION SELECT * FROM DETAIL_TABLE WHERE
     PRODUCT LIKE 'E01%' SAMPLE SAMPLE (200 or 10% of No of error records
     which ever is smaller and should give atleast 1 record)

As you can can see the WHERE condition as well as the number of records to be shown in sample is dependent and driven from the FIELD_ERR_DATA table.

Any help on getting to achieve this will be appreciated.

Regards,

Ankit

	<Prev	Next>		<<First	<Prev	Next>	Last>>