Almost there....but

[Schnappa]

Hi All

Been working on this one for a while now on and off. The DTS works ok, but I note that the TranNarration field in the Database is all Nulls - even though all looks well when I use the preview tab.

Any suggestions would be gratefully accepted.

Cheers

GV

CREATE TABLE [AccountTransactions] (
[ID] int NOT NULL IDENTITY,
[TranNumber] decimal (12,0) NOT NULL,
[AccountID] char (12) NOT NULL,
[TranCode] smallint NOT NULL,
[TranAmount] decimal (21,6) NOT NULL,
[TranDate] smalldatetime NOT NULL,
[TranNarration] varchar (80) NULL,
[OperatorName] varchar (40) NULL,
[TranBranch] varchar (15) NULL,
[TranOriginDesc] varchar (40) NULL,
[TranChannelDesc] char (20) NULL
)

SELECT

dp_history.ptid AS TranNumber,
dp_history.acct_no AS AccountID,
dp_history.tran_code AS TranCode,
dp_history.amt AS TranAmount,
dp_history.effective_dt AS TranDate,
ISNULL(dp_history.long_desc,dp_history.description) AS TranNarration,
ad_gb_rsm.name AS Operatorname,
ad_gb_branch.short_name AS TranBranch,
pc_origin.description AS TranOriginDesc,
ad_gb_channel.short_desc AS TranChannelDesc


FROM

(((tstUCU.dbo.dp_history dp_history LEFT OUTER JOIN tstUCU.dbo.ad_gb_rsm ad_gb_rsm ON dp_history.empl_id=ad_gb_rsm.employee_id) LEFT OUTER JOIN tstUCU.dbo.pc_origin pc_origin ON dp_history.origin_id=pc_origin.origin_id) LEFT OUTER JOIN tstUCU.dbo.ad_gb_channel ad_gb_channel ON dp_history.channel_id=ad_gb_channel.channel_id) LEFT OUTER JOIN tstUCU.dbo.ad_gb_branch ad_gb_branch ON ad_gb_rsm.branch_no=ad_gb_branch.branch_no


WHERE

dp_history.effective_dt>={ts '2006-10-01 00:00:00'} AND dp_history.effective_dt<{ts '2006-11-01 00:00:00'}

 

[AlexCuse]

So the TranNarration field in your query is null, or the long_desc and description fields in your database are null?

What are the data types for long_desc and description?

A wise man once said
"The only thing normal about database guys is their tables".

 

[Schnappa]

Both are vachars, although now thinking about it, they vary in length. The LongTranNarration is set to 255 and the TranNarration is set to 80 (as defined in the source DB).

What I am actually trying to achieve is one table called TranNarration which sources from the one of the two tables mentioned above. The LongTranNarration field has priority as the source, but when it is null, I need the TranNarration field. To make it more interesting, certain transactions will cause nulls in both of the narration fields.

Thanks for your help AlexCuse

Gez