LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dhrobertson

Hi,

I want to be able to do delta loads of information from an oracle database. There is a field called invoice_date which is a datetime field and I want to use this to filter on for each run.

With the ERP node you can pass through fields and use this as part of a filter to filter the SAP data. I need to be able to do the same with an Oracle DB.
I have used DB Query nodes to initially get the data out of the Oracle DB which is fine. after each run I will write a DateTime stamp to a brd and use this as the field for filtering each subsequent run.
I cant see a way of adding input pins to a DB Query node so am I right in thinking I would actually need to use the DB Execute node to pass through a timestamp field via a input pin to the node and then somehow query this field in the SQL code in the node?

I'm not sure how the SQL code would look in the node for DB Execute node.

eg. to get back all data where country_code == FR or GB from a table called DS_DIRECT_SALES in the DB Query node I write:

Select * from "DS_DIRECT_SALES" where COUNTRY_CODE = 'GB' or COUNTRY_CODE = 'FR'


how would I write this in a DB Execute node using a timestamp field as an additional filter?

something like this where runtime is a field which is input to the DB Execute node:

SqlNonSelect("select * from DS_DIRECT_SALES where COUNTRY_CODE = 'GB' or COUNTRY_CODE ='FR' and INVOICE_DATE >= runtime ")

using the SqlNonSelect statement, how do I reference the Database correctly and also how to I reference the runtime input field? ie. where do the ' ' or "" go in the statement for it to work correctly?

any ideas greatly appreciate. I'm sure this is a simple problem. if someone could help I'd appreciate it.

thanks

Douglas

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Yes, you'd use DBExecute. You want the SqlSelect function.

sqlSelect() is for when you use the SELECT statement,
sqlNonSelect is for anything else such as UPDATE, INSERT, etc.

I like to do it this way:
The part in there about ":1" is dependent entirely on which database engine you are using. Oracle uses ":n", Teradata uses "?", etc.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dhrobertson

Stony,

I have another query I hope you can help with...
so this time I'm working with SQL server and not Oracle. from your last sentence above you suggest that the :n or ? is dependant of the database engine.... what is used for SQL server?

the reason I ask is that I used the following statements to try and insert 10 rows of data into a SQL table starting with a truncate. It only insert 1 row and that row is the final row of the 10. I'm assuming it is something to do with my syntax? If I use a DB Store node it insert all 10 rows. just wondering what I'm missing to get this statement below to work on a SQL server database?:

query1 = "truncate table dbo.DIRECT_SALES"
query2 = "insert into dbo.DIRECT_SALES (COUNTRY_CODE,SAP_PROD_CODE,LOCAL_ITEM_CODE,LOCAL_ ITEM_NAME,D56_PROD_SUBGRP,D56_PROD_SUBLINE) VALUES(:1,:2,:3,:4,:5,:6)"

sqlNonSelect(query1)
sqlNonSelect(query2,COUNTRY_CODE,SAP_PROD_CODE,LOC AL_ITEM_CODE,LOCAL_ITEM_NAME,D56_PROD_SUBGRP,D56_P ROD_SUBLINE)

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

You are executing the TRUNCATE for every incoming record, so only the last record would survive.

I'm pretty certain that for the values, you need to use this:
VALUES(?,?,?,?,?,?)