Results 1 to 1 of 1
  1. #1
    Join Date
    Apr 2003
    Posts
    1

    Question Unanswered: Datastage Select

    Hello,
    the following Select returns the correct values in WINSQL or any other query tool. But when I execute this SQL within a Datastage job, it returns NULL values for all selected fields. This is strange as I use COALESCE, therefore it should at least return a value.

    I think that the problem has to do with the max function, because when I remove the max() from the Select, it does return a value, of course the wrong value but it does return a value. The same select statement with this max() does not return any value.

    What is also strange is that I get a truncated message. Maybe this is the reason why datastage is not returning any value.

    Here is the SQL:

    Select
    char(coalesce(max(DLR_CD),'------'),6),
    char(coalesce(max(LCL_DIST_TP),'-'),1),
    char(coalesce(max(LCL_FLET_CNTLNG_DLR_FL),'-'),1),
    char(coalesce(max(LCL_ORDER_GENRTN_FL),'-'),1)
    from
    (
    Select ASG.DLR_CD,
    case
    when DEF.DLR_SUBGRPING_DESC = 'SPANISH_ISLANDS' then
    ( case when ASG.DLR_CD is not NULL
    then 'I'
    when ASG.DLR_CD is NULL
    then 'M'
    end )
    end
    as LCL_DIST_TP
    ,
    case
    when DEF.DLR_SUBGRPING_DESC = 'ORDER_GENERATION' then
    ( case when ASG.DLR_CD is not NULL
    then 'Y'
    when ASG.DLR_CD is NULL
    then 'N'
    end )
    end
    as LCL_FLET_CNTLNG_DLR_FL
    ,
    case
    when DEF.DLR_SUBGRPING_DESC = 'FLEET_DEALER' then
    ( case when ASG.DLR_CD is not NULL
    then '1'
    when ASG.DLR_CD is NULL
    then '2'
    end )
    end
    as LCL_ORDER_GENRTN_FL
    from
    (Select * from ADS.V_DLR_GRPNG_DEFNTN) As DEF
    left outer join
    (Select DLR_GRPNG_ID, DLR_CD from ADS.V_DLR_GRPNG_ASGNMN
    where DLR_CD = ?) As ASG
    on
    DEF.DLR_GRPNG_ID = ASG.DLR_GRPNG_ID
    ) as TBL
    Last edited by guido.thelen; 04-29-03 at 10:27.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •