This discussion is archived
7 Replies Latest reply: Jan 9, 2013 4:17 AM by Saro RSS

Variable not in select list issue

Saro Explorer
Currently Being Moderated
Hi friends,

To the existing OBIA mapping, that is for one of the SDE mapping i dragged a single column from source to source qualifier and also updated that column in the sql query of the source qualifier and finally dragged it to my target staging table.

After that if i tried to run that SDE task in my DAC means, it is failing and returning the below error in log
ora-01007 variable not in select list in informatica
since i have checked and verified the order of the column in the source qualifier port and the query. It is same, but then too returning the above error.

What is the fix for it.

Thanks in advance.

Regards,
Saro
  • 1. Re: Variable not in select list issue
    Srini VEERAVALLI Guru
    Currently Being Moderated
    What workflow is running for the SDE mapping? since its OOB mapping you should have 2 workflows(full and incr) for the same mapping, make sure your changes are affected to workflow ending with _Full.

    Open session properties->source->look for SQL Override.
  • 2. Re: Variable not in select list issue
    Saro Explorer
    Currently Being Moderated
    Hi Svee,

    Thanks for the reply.

    I followed the below link for adding the extra column to the per-built mapping

    http://www.rittmanmead.com/2008/07/amending-existing-category-1-sde-and-sil-mappings-in-oracle-bi-apps-795/+_

    Hence for example, i copied the workflow of SDE_ORA_AbsenceEvent to my new custom folder. In that i just tried only with SDE mapping(from source to staging)
    i had three source tables for it
    
    per_absence_attendances
    per_absence_attendance_types
    per_abs_attendance_reasons
    
    Target(staging table)
    
    W_absence_event_ds
    i tried of adding a date column "last_updated_by" from the table "per_absence_attendances" to the staging table "W_absence_event_ds"

    Before adding the date column, i just tried by copying the SDE_ORA_AbsenceEvent to my custom folder and tried to execute that in DAC as it is loading successfully in my target(staging table) "W_absence_event_ds".

    After that i added the date column to the target(staging table) "W_absence_event_ds" and tried to execute the same in DAC, but @ that time only im receiving the variable error.

    Like you said
    since its OOB mapping you should have 2 workflows(full and incr) for the same mapping, make sure your changes are affected to workflow ending with _Full.
    I dint copied the workflow SDE_ORA_AbsenceEvent_full to my custom folder. Whether is this the problem for this variable error whether can i copy the full(workflow) and can i try.

    And also a good news that OBIA 7.9.6.4 and DAC 11g released.

    http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bi-downloads-1525270.html_+

    Regards,
    Saro
  • 3. Re: Variable not in select list issue
    Srini VEERAVALLI Guru
    Currently Being Moderated
    When ever you are going to customize oob mappings I would suggest you to export workflow object and import into your custom folder. You may use copy and paste using repository Manager.

    I doubt on DAC task is still pointing to OOB folder!! Have you modified the dac task to point your custom folder?
  • 4. Re: Variable not in select list issue
    Saro Explorer
    Currently Being Moderated
    Hi svee,

    No i dint copied, i tried by exporting the objects and imported that to my custom folder, but i dint export the SDE_ORA_AbsenceEvent_full(full load) workflow to my custom folder just exported the SDE_ORA_AbsenceEvent workflow alone.

    Whether i need to import the SDE_ORA_AbsenceEvent_full(full load) workflow also to my custom folder

    Yes my task is pointing towards the custom folder that i have created in repository manager in DAC.

    Regards,
    Saro
  • 5. Re: Variable not in select list issue
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Can you let me know which (Full or Incr) workflow is running when you are testing this?
    If DAC is running Incr workflow then problem could be something else if its running Full load workflow then you need to move that to custom folder and follow my comments as what I said earlier

    Btw: Can you send me session log to my email
  • 6. Re: Variable not in select list issue
    Saro Explorer
    Currently Being Moderated
    Hi Svee,
    Can you let me know which (Full or Incr) workflow is running when you are testing this?
    i think SDE_ORA_AbsenceEvent workflow(Incr) is been running, since i can find this as i seen that workflow is been executing in workflow monitor while running DAC as it is failing.

    Hence im having both(Full and Incr) workflow in my custom folder

    SDE_ORA_AbsenceEvent
    SDE_ORA_AbsenceEvent_Full
    Can you send me session log to my email
    I have mailed U
    :)

    Hence im performing only the source to staging and not performing the staging to target just inorder to check whether the data is loading in the staging table for the extra date column that i have added.

    Regards,
    Saro
  • 7. Re: Variable not in select list issue
    Saro Explorer
    Currently Being Moderated
    Hi Svee,

    Now the data is loading for the extra column once i have modified(that is added the date column) in the sql query under the source of the session properties in my wf like you said
    Open session properties->source->look for SQL
    Thanks svee.

    Regards,
    Saro

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points