Using subselect in BMCLOAD

aravindunlimited · New User Joined: 08 May 2006 Posts: 16

I have to build a load card that tries to load the data from FILE1 to TABLE1, but it has to lookup another TABLE2 as a subselect. I found that a WHEN clause can be used to selectively load data into the TABLE1

LOAD DATA INDDN SYSREC RESUME YES LOG NO COPY YES
EBCDIC CCSID(285,65534,65534)
IFDISCARDS 2
FORMAT CSV TERMINATEDBY '|'
INTO TABLE XYZ.TABLE1
WHEN (1:1) NOT = ‘A’
(
COL_NO1 POSITION(*)
,COL_NO2 POSITION(*)
)

Is there a way to plug a subselect into this WHEN clause?

like say WHEN EXISTS (SELECT 1 FROM XYZ.TABLE2 WHERE COL_NO3 = COL_2).... (I tried it, but it failed)

Just wanted to know whether there is any option to do that in BMCLOAD...

thanks v much

expat · Posted: Mon Nov 22, 2010 3:49 pm

And exactly what has this topic have to do with DFSORT / ICETOOL.
Hardly showing any initiative is it when you can't even post in the correct forum.

sushanth bobby · Posted: Tue Nov 23, 2010 10:02 am

aravindunlimited,

I think you misunderstood the 'WHEN' clause used in the LOAD statement. Its basically used for filtering the input dataset when loading. And i am not sure for how it works in BMC.

Can i know why are you checking in TABLE2, does TABLE1 and TABLE2 are referentially related. If so, just load the TABLE1 and it will go into check-pending and move all the orphan rows to the exception tables using CHECK DATA utiltiy.

To get really good reply's, you got to tell the why's.

Sushanth

aravindunlimited · New User Joined: 08 May 2006 Posts: 16

Sorry for posting this in the wrong forum, I guess once posted I cannot change the forum. Will make sure this is taken care of.

My requirement was to load a set of data from a file into TABLE1 where that record should be present in another table TABLE2 as well (But there is no referential integrity), this process has to be automated.

I was little over ambitious, and wanted to tryout such a thing directly in BMCLOAD had there been a facility for this. As of now I have simplified the design and am doing the 'election' process ahead of load.

Thanks for all the replies