August 3, 2006 at 12:12 pm
Hello all...
I need to separate some rows returned from a table based on a value that's in the table. See, I've got 3 different categories, S, B, and BA. This field is in a column called actor_role.
Here's my current code:
***************************************
INSERT INTO #PT61_PREFIX_ACTOR
SELECT
[FILING_ID] = SUBSTRING(PT61_RECORD,2,20),
[BUYER_BUSI_FLG] = SUBSTRING(PT61_RECORD,24,1) ,
[BUYER_LAST_NAME] = SUBSTRING(PT61_RECORD, 25,100) ,
[BUYER_FIRST_NAME] = SUBSTRING(PT61_RECORD, 125, 70),
[BUYER_MIDDLE] = SUBSTRING(PT61_RECORD, 195, 70),
[BUYER_ADDRESS_1] = SUBSTRING(PT61_RECORD, 265, 200),
[BUYER_ADDRESS_2] = SUBSTRING(PT61_RECORD, 465, 200),
[BUYER_CITY] = SUBSTRING(PT61_RECORD, 665, 70),
[BUYER_STATE] = SUBSTRING(PT61_RECORD, 735, 100),
[BUYER_ZIP] = SUBSTRING(PT61_RECORD, 835, 50),
[BUYER_CNTRY_ABV] = SUBSTRING(PT61_RECORD, 885, 3),
[BUYER_COUNTRY] = SUBSTRING(PT61_RECORD, 888, 50),
[BUYER_ADDR_TYPE] =SUBSTRING(PT61_RECORD, 938, 1),
[SELLER_LAST_NAME] = SUBSTRING(PT61_RECORD, 25,100) ,
[SELLER_FIRST_NAME] = SUBSTRING(PT61_RECORD, 125, 70),
[SELLER_MIDDLE] = SUBSTRING(PT61_RECORD, 195, 70),
[ADDITIONAL_BUYER_LAST_NAME] = SUBSTRING(PT61_RECORD, 25,100) ,
[ADDITIONAL_BUYER_FIRST_NAME] = SUBSTRING(PT61_RECORD, 125, 70),
[ADDITIONAL_BUYER_MIDDLE] = SUBSTRING(PT61_RECORD, 195, 70)
from pt61_table_load
***************************************************
You can see that there is a prefix before each column name: buyer, seller, and additional buyer represented by B, S, and BA respectively. I need the rows that have a actor_role column value of 'B' loaded only into the 'buyer%' columns only, the rows that have an actor_role value of 'S' loaded into the 'seller' columns, and so on...
I'm pretty sure this isn't too hard, but as a SQL newbie, I'm just a little confused on how to get this done.
Thanks for you help...
August 3, 2006 at 1:48 pm
do you have some sample records?
August 3, 2006 at 2:57 pm
INSERT INTO #PT61_PREFIX_ACTOR
SELECT
...
[BUYER_LAST_NAME] = case actor_role when 'B' then SUBSTRING(PT61_RECORD, 25,100) end ,
...
[SELLER_LAST_NAME] = case actor_role when 'S' then SUBSTRING(PT61_RECORD, 25,100) end,
...
[ADDITIONAL_BUYER_LAST_NAME] = case actor_role when 'BA' then SUBSTRING(PT61_RECORD, 25,100) end,
...
from pt61_table_load
August 7, 2006 at 11:18 am
I'm sorry, I worded my initial question a little wrong.
The actor_role column is actually in another table, a temp table, that gets created just before this one. When I try to use this code:
[BUYER_LAST_NAME] = case actor_role when 'B' then SUBSTRING(PT61_RECORD, 25,100) end ,
I get an error saying invalid column name for actor_role. The previous temp table still exists, I just don't understand why the current block of code can't see it.
Thanks for your help...
August 7, 2006 at 2:38 pm
Are you joining the other #temp table to your pt61_table_load table?
I wasn't born stupid - I had to study.
August 8, 2006 at 8:39 am
That was it. Thanks again, you guys are great!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply