SQLLDR sequence that may repeat

[Welshbird]

I'm trying to load a legacy-system flatfile into Oracle.

The flatfile I have has an initial field that tells me which record type this is, and I have (finally) managed to get this to load into the correct table using a 'when' command. So far so good...

but, I have just found that the way of 'rolling up' this data is currently based on where it appears in the file rather than a code inherent within the file. I suspect this may be something I can deal with on loading rather than later, but at the moment I'm at a loss with ideas.

Example of the file:

HM;00000000000001;ANTI-ACNE;;;;A;
HM;00000000000101;ALL OTHER ANTI ACNE SUBTOTAL;;;;A;
HM;00000000010101;ANTI ACNE OTHER;;;;N;
HM;10000000934794;ACNISAL 2% ACNE MEDIC CLNSER 1;4155;ALLIANCE PHARMS;9999-12-31;N;
HM;10000000002600;ACTINAC PWD/SOLVENT 25MLX2;0059;PECKFORTON PHAR;9999-12-31;N;
HM;10000002729416;AKNEMYCIN PLUS SOL 25ML;8106;RECKITT-BENCKIS;9999-12-31;N;
HM;10000002495877;ERYACNE 4 4% GEL 30G;5250;GALDERMA;9999-12-31;N;
HM;10000002495869;ERYACNE 2 2% GEL 30G (DISC);5250;GALDERMA;2003-06-01;N;
HM;10000000090480;ESKAMEL CREAM 25G;2115;GOLDSHIELD HEAL;9999-12-31;N;
HM;10000000065979;METROGEL 0.75% WATER BASED GEL;5250;GALDERMA;9999-12-31;N;
HM;10008880002838;METRONIDAZOLE 0.75% GEL 30G (U;0002;UNBRANDED;9999-12-31;N;
HM;10000001115765;METRONIDAZOLE 0.75% GEL 40G (H;4242;HILLCROSS PHARM;9999-12-31;N;
HM;10008880005027;METRONIDAZOLE 0.75% GEL 40G (U;0002;UNBRANDED;9999-12-31;N;
HM;10000003190535;METROSA 0.75% GEL 30G;0597;LINDERMA LTD;9999-12-31;N;
HM;10000002932861;METROSA 0.75% GEL 40G;0597;LINDERMA LTD;9999-12-31;N;
HM;10000000473058;RETIN-A 0.01% GEL 60G;7246;JANSSEN-CILAG;9999-12-31;N;
HM;10000000057117;RETIN-A 0.025% CREAM 60G;7246;JANSSEN-CILAG;9999-12-31;N;
HM;10000000247650;RETIN-A 0.025% GEL 60G;7246;JANSSEN-CILAG;9999-12-31;N;
HM;10000000266882;RETIN-A 0.025% LOTION 100ML (D;7246;JANSSEN-CILAG;2006-08-08;N;
HM;10000002500684;ROZEX 0.75% CREAM 30G;5250;GALDERMA;9999-12-31;N;
HM;10000002849867;ROZEX 0.75% CREAM 40G;5250;GALDERMA;9999-12-31;N;
HM;10000002136380;ROZEX 0.75% GEL 30G;5250;GALDERMA;9999-12-31;N;
HM;10000002849859;ROZEX 0.75% GEL 40G;5250;GALDERMA;9999-12-31;N;
HM;10000000739474;SKINOREN 20% CREAM 30G;1829;SCHERING HEALTH;9999-12-31;N;
HM;10000000164855;TOPICYCLINE 0.22% ACNE TRT SOL;1140;SHIRE PHARMS;9999-12-31;N;
HM;10000000479840;VALDERMA CREAM 30G;0604;RANSOM CONS H/C;9999-12-31;N;
HM;10000000422576;VALDERMA SOAP 100G;0604;RANSOM CONS H/C;9999-12-31;N;

File layout is described as follows:

type char(2), product_code number, label, client_code number, manufacturer char(15), date char(10), detail char(1), master_code number

The date field I am going to ignore, as it isn't relevant, and I really don't want to be adding things with a date of 31st Dec 9999 at the moment, but hey. I'll just deal with what needs to happen for now!

Now, it seems that the each product whose code begins with a '1' is the detail, and these all roll-up within the preceeding product whose code begins with a '0'.
so, to simplify, I could have the following:
0000005 Veg
1000045 Beans
1000056 Peas
0000006 Meat
1000444 Lamb
1004567 Beef
1000765 Venison
My ideal would be to add a column on load so that the data above looks like the following:

0000005 Veg
1000045 Beans 0000005
1000056 Peas 0000005
0000006 Meat
1000444 Lamb 0000006
1004567 Beef 0000006
1000765 Venison 0000006

I tried using a sequence on load, thinking that this must help me with at least knowing where the items are within the file, but I confess I am now all out of ideas, so if anyone could offer me some assistance I'd be very grateful.

Thanks in advance chaps and chapesses!


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

I've solved part of this (I think!), so have re-posted with maybe a better explanation at:

thread1177-1313333

Thanks

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]