Post reply

How to return null or blank when database column dose not extist in database

  • June 7, 2008 at 5:09 am

    #93318

    Hello all,

    I am retrieving data from my some table. If one of the table dose not contain data then the query returns zero record. If the particuler column dose not contain any record then null or blank should be displayed in that column and the query all the record.

    Following query return records

    select cat.catname,cat.catamount,purchase.purchasedate,purchase.taken,purchase.UniqueId

    from ATA_SurveyPurchase purchase, ada_category cat where purchase.userid=2 and purchase.catid=cat.catid order by purchase.purchasedate desc

    Now i modified above auery by

    select cat.catname,cat.catamount,purchase.purchasedate,purchase.taken,purchase.UniqueId,ATA_ExtraaPayment.amount

    from ATA_SurveyPurchase purchase, ada_category cat,ATA_ExtraaPayment where purchase.userid=2

    and purchase.catid=cat.catid and ATA_ExtraaPayment.uniqueid=purchase.uniqueid

    order by purchase.purchasedate desc

    above query dose not return any record.

    I have tried following query

    select cat.catname,cat.catamount,purchase.purchasedate,purchase.taken,purchase.UniqueId,

    case when ATA_ExtraaPayment.amount is null then 0 else ATA_ExtraaPayment.amount end AmountEx

    from ATA_SurveyPurchase purchase, ada_category cat,ATA_ExtraaPayment where purchase.userid=2

    and purchase.catid=cat.catid and ATA_ExtraaPayment.uniqueid=purchase.uniqueid

    order by purchase.purchasedate desc

    but result is same. the above query dose not return any record.

    Please guide me how can i display null or any message if the table column dosenot contain any record. I need following output.

    Catname Catamount purchasedate uniqueid taken AmountEx

    abc 25 02/02/2008 xyz 0 200

    pqr 22 07/06/2008 xyz03 1 NUll or any message if this column dosenot contain any record.

    Thanks

    Rahul

  • June 7, 2008 at 6:43 am

    #825806

    Use the "LEFT OUTER JOIN" option as described in Books OnLine:

    SELECT ada_category.catname

    ,ada_category.catamount

    ,ATA_SurveyPurchase.purchasedate

    ,ATA_SurveyPurchase.taken

    ,ATA_SurveyPurchase.UniqueId

    FROM ada_categorycat

    LEFT OUTER JOIN

    ATA_SurveyPurchase

    ON ATA_SurveyPurchase.catid = ada_category.catid

    AND ATA_SurveyPurchase.userid = 2

    ORDER BY ATA_SurveyPurchase.purchasedate DESC

    SQL = Scarcely Qualifies as a Language

  • June 7, 2008 at 7:13 am

    #825809

    thanks a lot.

    it solves my problem..

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply