Data From Vendor Changes

Database
TDW
Enthusiast

Data From Vendor Changes

I have adopted previously written BTQ's from a 3rd party.

We receive a flat file (I believe) from one of our clients each month. The record size varies.

For example. Say there are a total of 11 colums in the flat file as follows.

  • CustID
  • Prod1
  • Value1
  • Prod2
  • Value2
  •     :
  • Prod5
  • Value5

Our client transmits the following (logical view).

CustID   Prod1   Value1   Prod2   Value2   Prod3   Value3   Prod4  Value4   Prod5  Value5

C123         0010     10         0035      5           0085      99        0245     3           0900    14

C456                                   0035     75           0085     42                                  0900     6

This is what we receive (physical view).

C123,0010,10,0035,5,0085,99,0245,3,0900,14

C456,0035,75,0085,42,0900,6

As you can see, the record size varies because the client does not (will not) use NULL values in their records. *Don't know the reason, but I suspect that because we are dealing hundreds of thousands of records (I only listed 2 customers with 5 products) this reduces their transmit time and costs.

The 3rd party who developed the BTQ uses the following code to populate the table.

Insert Into      tCustProd

CustomerID,

  (case        when Prod1N = 0020 then Value1V

      else ' '  end )     as DressShoes,

  (case        when Prod1N = 0030 then Value1V 

               when Prod2N = 0030 then Value2V

      else ' '  end )     as BollaHats ,

  (case       when Prod1N = 0040 then Value1V

                  when Prod2N = 0040 then Value2V

                  when Prod3N = 0040 then Value3V 

        Else ' '  end  )        as SportsSocks ,

Not only are there hundreds of thousands of records, but there thousands of products. When the client adds a new product they do not use sequential Prod Numbers. For example, if they were to add were to 0010 0035, the above code would be changed to this (Changes are bolded).

Insert Into      tCustProd

CustomerID,

  (case        when Prod1N = 0010 then Value1V

      else ' '  end )     as TankTops,

  (case        when Prod1N = 0020 then Value1V 

               when Prod2N = 0020 then Value2V

      else ' '  end )     as DressShoes,

  (case       when Prod1N = 0030 then Value1V

                  when Prod2N = 0030 then Value2V

                  when Prod3N = 0030 then Value3V 

        Else ' '  end  )        as BollaHats,

  (case       when Prod1N = 0035 then Value1V

                  when Prod2N = 0035 then Value2V

                  when Prod3N = 0035 then Value3V 

                  when Prod4N = 0035 then Value4V 

        Else ' '  end  )        as CommandoBoots ,

  (case       when Prod1N = 0040 then Value1V

                  when Prod2N = 0040 then Value2V

                  when Prod3N = 0040 then Value3V 

                  when Prod4N = 0040 then Value4V

                  when Prod5N = 0040 then Value5V

        Else ' '  end  )        as SportsSocks ,

Let me know if your require a deeper explanation. 

Thank and God Bless,

Genesius

3 REPLIES
Enthusiast

Re: Data From Vendor Changes

I tried hard to find out what is your question or issue here, but not able to, can you please put what's your challenge ?

Teradata Employee

Re: Data From Vendor Changes

The scenario requires a complete documentation on the format of the file and possible cases to cater for. What is seems is the example of complex structured file containing interrelated data in seperate rows.

OR

Actually usable file format, if the column doesnt have a value, yet it needs to be specified in the file you get .... hence the number of commas in the file for every row should be same.

TDW
Enthusiast

Re: Data From Vendor Changes

My apologies for not responding sooner. I have not receiving emails when a member comments on my posts. Also, I apologize for the cryptic nature of my question and sample data. I work in a highly confidential area and I was trying to sanitize my code and still make it understandable.

Since posting the above, we discovered one of our former programmers had found a way around this issue. However, before I display the change, additional explanation, though still somewhat cryptic, is in order.

The process.

We receive a flat file containing hundreds of columns for a single customer record. The columns are paired off with a code number and value. Prod1N and Value1V....Prod100N and Value100V. I will use Prod1N & Value1V through Prod5N & Value5V for this example.

A customer might have only ordered tank tops (1N & 1V) and sportsocks (5N & 5V); however, the 2N...4V cols would still be poplulated with NULL's. In order to process the customer each col pair would be read in using the convulted coding in the original post. However, this former programmer wrote another BTQ to be run prior which reduced the decreased the number of cols but increased the number of records, making the coding more efficient.

This code...

insert into tCustProd

SELECT  HeaderField, Field1N, Field1V

from tCustProd where Field1V <> ' '

UNION

SELECT HeaderField, Field2N, Field2V

from tCustProd where Field2V <> ' '

UNION


etc....

produced this table...

CustID   Prod1   Value1  

C123     0010     10

C123     0035      5

C123     0085     99

C123     0245      3

C123     0900     14

C456     0035     75

C456     0085     42

C456     0900      6

Now in the second (original BTQ) I run this coding instead

max(case when Prod1N = 0010 then Value1V end)

 as TankTops,

max(case when Prod1N = 0020 then Value1V end)

 as DressShoes,

max(case when Prod1N = 0030 then Value1V end)

 as BollaHats,

max(case when Prod1N = 0035 then Value1V end)

 as CommandoBoots,

max(case when Prod1N = 0040 then Value1V end)

 as SportsSocks,

because there are only 3 cols (CustID, Prod1, Value1) in the new table.

Again, I apologize for being so cryptic. But I wanted to respond and not appear ungrateful for the help you were providing. I am certain I will need more assistance in the future.

Thanks and God Bless,

Genesius