Load *; on top of SQL SELECT * not working

[reanfadyl]

Hi Experts,

Can someone please answer why the third example of load scripting below doesn't work?

The first two work fine, but the third one creates a bunch of $Orphan tables, as opposed to one PATIENTS_V3 table.

/ ----------------------------------------

// Load the PATIENTS data

// ----------------------------------------

//Works version 1

PATIENTS_V1:

LOAD *;

SQL SELECT *

FROM PIMS.PATIENTS

Where

MODIF_DTTM > to_date('$(vModifyDateTime)','DD/MM/YYYY');

//Works version 2

PATIENTS_V2:

LOAD

"PATNT_REFNO",

    "SEXXX_REFNO",

    FORENAME;

SQL SELECT *

from

    PIMS.PATIENTS patnt,

        PIMS.PROVIDER_SPELLS prvsp

where PATNT.PATNT_REFNO=PRVSP.PATNT_REFNO

       and  nvl(PRVSP.ARCHV_FLAG,'N')='N'

       and PRVSP.MODIF_DTTM > sysdate-101;

//Doesn't Work???

PATIENTS_V3:

LOAD *;

SQL SELECT *

from

    PIMS.PATIENTS patnt,

        PIMS.PROVIDER_SPELLS prvsp

where PATNT.PATNT_REFNO=PRVSP.PATNT_REFNO

       and  nvl(PRVSP.ARCHV_FLAG,'N')='N'

       and PRVSP.MODIF_DTTM > sysdate-101;

Cheers

Rean

[Chanty4u]

wat error ur getting?

[kkkumar82]

What is the error your are getting?? can you share the image

[sunny_talwar]

Are you loading all the three tables in one script or are you just loading one of the three tables? Can you share a screenshot of the tables that get created?

[Peter_Cammaert]

Check if you're using a CROSSTABLE prefix somewhere...

[awhitfield]

Have you tried the 3rd query in SQL?

Andy

[Clever_Anjos · Accepted solution]

I´m guessing that one of your SELECT * retrieve more than one column with same name.

QlikView does not allow a LOAD with two (or more) columns with duplicated names

[awhitfield]

You'll get ambiguous results!

[reanfadyl]

Thanks, this makes sense, I will test and post up if it fixes the issue.

[reanfadyl]

Hi All,

The below worked, as suggested by Anjos I think my issue was being caused by duplicate columns returned by the SELECT * acting across multiple tables with common field names.

The below worked:

Patients:

Load *;

select

patnt.*

,prvsp.prvsp_refno

,refrl.refrl_refno

from PIMS.patients patnt,

              PIMS.provider_spells prvsp,

              PIMS.referrals refrl

where prvsp.patnt_refno=patnt.patnt_refno

and   prvsp.refrl_refno=refrl.refrl_refno

and   nvl(prvsp.archv_flag,'N') = 'N'

and   (prvsp.modif_dttm > sysdate -101 or refrl.modif_dttm > sysdate-101)

union

select

patnt.*

, null

, refrl.refrl_refno

from PIMS.patients patnt,

     PIMS.referrals refrl

where refrl.patnt_refno=patnt.patnt_refno

and   nvl(refrl.archv_flag,'N') = 'N'

and   refrl.modif_dttm > sysdate -101

and   not exists (

select null from

PIMS.provider_spells prvsp2

where prvsp2.refrl_refno=refrl.refrl_refno

and   nvl(prvsp2.archv_flag,'N') = 'N'

and   (prvsp2.modif_dttm > sysdate -101 or refrl.modif_dttm > sysdate -101));