All Forums Database
TDW 36 posts Joined 11/12
15 Mar 2013
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).
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
  (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
  (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,


Thanks and God Bless,

KS42982 137 posts Joined 12/12
15 Mar 2013

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 ?

Adeel Chaudhry 773 posts Joined 04/08
18 Mar 2013

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.
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.

-- If you are stuck at something .... consider it an opportunity to think anew.

TDW 36 posts Joined 11/12
28 May 2013

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 <> ' '
SELECT HeaderField, Field2N, Field2V
from tCustProd where Field2V <> ' '

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,

Thanks and God Bless,

You must sign in to leave a comment.