FOR EACH ROW procedure for INSERT

[jane_lj]

Hi,

I wrote this procedure for INSERT in Teiid Designer, when I save, no errors, everything looks fine.

FOR EACH ROW

BEGIN ATOMIC

          DECLARE double VARIABLES.elevation;

          DECLARE double VARIABLES.xSurfaceCoordinate;

          DECLARE double VARIABLES.ySurfaceCoordinate;

          SELECT Petrel_SM.PetrelBorehole.WorkingReferenceLevelOffset AS elevation, WellHeadX AS xSurfaceCoordinate, WellHeadY AS ySurfaceCoordinate FROM PetrelBorehole WHERE Petrel_SM.PetrelBorehole.UWI = "NEW".uwi;

          INSERT INTO Petrel_SM.PetrelBoreholeTrajectory (Petrel_SM.PetrelBoreholeTrajectory.UWI, TrajectoryZList, MDList, TrajectoryXList, TrajectoryYList, TrajectoryZUnit, MDUnit, TrajectoryXYUnit, IsSelected) VALUES ("NEW".uwi, computeAbsoluteZ_FloatInDoubleOut("NEW".z, VARIABLES.elevation), "NEW".md, computeTrajectoryXYList_FloatInDoubleOut("NEW".x_off, xSurfaceCoordinate), computeTrajectoryXYList_FloatInDoubleOut("NEW".y_off, ySurfaceCoordinate), "NEW".z_uom, "NEW".md_uom, "NEW".x_off_uom, "NEW".is_selected);

END

But when I run a INSERT statemet against Veiw Model， nothing got inserted, and Teiid didn't throw any errors, exceptions. And Teiid didn't push down the insert request to my real data source.

Looks like Teiid didn't do anything.

Can you anything wrong in my INSERT procedure?

Thanks.

[shawkins]

What does your teiid insert look like?  Is it returning a non zero update count?

Also in the above it seems like the select statement has no effect.

Steve

[jane_lj]

Steve,

The select statement purpose is to query three values and save into 3 vairalbes I declare.

The syntax is wrong?

Thanks.

[shawkins]

The select does not modify any variables.  You may have seen that MetaMatrix/Teiid used the SELECT INTO clause to target loading into a table, whereas the sql spec uses INTO to load into variables.  Ideally we should support that syntax as well, but for now the workaround is to use a temp table:

INSERT INTO #TEMP SELECT ...

Then later you can use a scalar subquery to get a value:

... = (SELECT COL FROM #TEMP)

[jane_lj]

Thank you very much.