Archives of the TeradataForum

Message Posted: Mon, 06 Jan 2003 @ 17:28:40 GMT

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

HI ALL,

Hope you had good holidays. I want to know if following queryman query going to use cross join.

        select
        CASE WHEN CustNo is not null
        THEN b.custno
        ELSE BTN
        END as ACCT_NUMBER
        ,a.eightnum as LOS
        ,'800' as LOS_TYPE
        , CASE
        WHEN (trim(BillFName) || ' ' || trim(BillMI) || ' ' || trim(BillLName)) is not null
        THEN trim(BillFName) || ' ' || trim(BillMI) || ' ' || trim(BillLName)
        WHEN (trim(BillFName) || ' ' || trim(BillMI) || ' ' || trim(BillLName)) is null
        THEN ''
        END as ACCT_NAME
        ,CASE WHEN BillAddr1 is not null
        THEN BillAddr1
        ELSE
        ''
        END as ACCT_ADDRESS1
        ,CASE WHEN BillAddr2 is not null
        THEN BillAddr2
        ELSE
        ''
        END as ACCT_ADDRESS2
        ,CASE WHEN BillCity is not null
        THEN BillCity
        ELSE
        ''
        END as ACCT_CITY
        ,CASE WHEN BillSTate is not null
        THEN BillState
        ELSE
        ''
        END as ACCT_STATE
        ,CASE WHEN BillZip is not null
        THEN BillZip
        ELSE
        ''
        END as ACCT_ZIP
        ,b.btn as ACCT_PHONE
        ,substr(a.install,1,4) || substr(a.install,6,2) || substr(a.install,9,2) as START_DATE
        ,b.btn as BILL_TO_NUMBER
        ,a.termani as TRANSLATED_NUMBER
        from dev.serv800 a,dev.customer_cops b
        where a.install is not null and a.inactivate is null
        and b.recid = a.fk_Customer
        and siteno not in ('9998','9999')
        and custno is not null
        and trim(custno)<>''

        UNION

        select CASE WHEN CustNo is not null
        THEN b.custno
        ELSE
        BTN
        END as ACCT_NUMBER
        ,a.authcode as LOS
        ,'TCC' as LOS_TYPE
        , CASE
        WHEN (trim(BillFName) || ' ' || trim(BillMI) || ' ' || trim(BillLName)) is not null
        THEN trim(BillFName) || ' ' || trim(BillMI) || ' ' || trim(BillLName)
        WHEN (trim(BillFName) || ' ' || trim(BillMI) || ' ' || trim(BillLName)) is null
        THEN ''
        END as ACCT_NAME
        ,CASE WHEN BillAddr1 is not null
        THEN BillAddr1
        ELSE
        ''
        END as ACCT_ADDRESS1
        ,CASE WHEN BillAddr2 is not null
        THEN BillAddr2
        ELSE
        ''
        END as ACCT_ADDRESS2
        ,CASE WHEN BillCity is not null
        THEN BillCity
        ELSE
        ''
        END as ACCT_CITY
        ,CASE WHEN BillSTate is not null
        THEN BillState
        ELSE
        ''
        END as ACCT_STATE
        ,CASE WHEN BillZip is not null
        THEN BillZip
        ELSE
        ''
        END as ACCT_ZIP
        ,b.btn as ACCT_PHONE
        ,substr(a.install,1,4) || substr(a.install,6,2) || substr(a.install,9,2) as START_DATE
        ,b.btn as BILL_TO_NUMBER
        ,'' as TRANSLATED_NUMBER
        from dev.travcard a,dev.customer_cops b
        where a.install is not null and a.inactivate is null
        and b.recid = a.fk_Customer
        and siteno not in ('9998','9999')
        and custno is not null
        and trim(custno)<>''

THANX

Mayur

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