SQL Statement not working - IBM AS400

This is a discussion on SQL Statement not working - IBM AS400 ; Can you help with an SQL statement which is not working? A friend is trying to copy the data in one field and put it in two other fields and she gets this error: Column qualifier or table B undefined. ...

+ Reply to Thread
Results 1 to 2 of 2

Thread: SQL Statement not working

  1. SQL Statement not working

    Can you help with an SQL statement which is not working?

    A friend is trying to copy the data in one field and put it in two other
    fields and she gets this error: Column qualifier or table B undefined.

    Statement:

    UPDATE AMFLIBK/ITMPRCL0 A SET A.IPDSC1 = B.ITDSAD
    FROM AMFLIBK/ITMRVAL0 B, AMFLIBK/ITMPRCL0 A

    Source Lib: AMFLIBK
    Source file: ITMRVAL0
    Source Field : ITDSAD

    Target File(s) : ITMPRCL0 , PSTDHRL0
    Target Field: IPDSC1 in Itmprcl0
    PSHDSC in Pstdhrl0

    Thanks!




  2. Re: SQL Statement not working

    Ummm... I don't see any join fields. And I'm not sure you can update two
    files at the one time.

    Doing just one file you want something like this::

    UPDATE AMFLIBK/ITMPRCL0 A
    SET A.IPDSC1 =
    (select B.ITDSAD FROM AMFLIBK/ITMRVAL0 B
    where a.somefield = b.somefield and a.field2=b.field2)
    where exists (select 1 from
    FROM AMFLIBK/ITMRVAL0 B
    where a.somefield = b.somefield and
    a.field2=b.field2)
    There final where clause is necessary if not every record in file A has a
    match in file B-- because the first where clause will return a null value in
    such sitautions-- and A.IPDSC1 is not null capable.

    This also presumes that b.somefield and b.somefield2 return a single value,
    not multiple values.

    Sam


    "just bob" wrote in message
    news:VaadnVJeaZRYZjDVnZ2dnUVZ_t3inZ2d@supernews.co m...
    > Can you help with an SQL statement which is not working?
    >
    > A friend is trying to copy the data in one field and put it in two other
    > fields and she gets this error: Column qualifier or table B undefined.
    >
    > Statement:
    >
    > UPDATE AMFLIBK/ITMPRCL0 A SET A.IPDSC1 = B.ITDSAD
    > FROM AMFLIBK/ITMRVAL0 B, AMFLIBK/ITMPRCL0 A
    >
    > Source Lib: AMFLIBK
    > Source file: ITMRVAL0
    > Source Field : ITDSAD
    >
    > Target File(s) : ITMPRCL0 , PSTDHRL0
    > Target Field: IPDSC1 in Itmprcl0
    > PSHDSC in Pstdhrl0
    >
    > Thanks!
    >
    >
    >




+ Reply to Thread