Database connect - SQL Statement

[lombert]

Hi

I'm looking for the SQL Statement to use in the Database connect.

I have found and used this;

INSERT table_name SET key1 = 'value1' , key2 = 'value2' ... ...

But can't I use 'update' and 'delete' to? Are there any more?
'Select' is used only in 'Define ..' / 'Database', or?

Have looked in the online manuals but have not find anything more..

And have anyone more trix or shortcuts to set SQL Statement, put them here to..

edit:
Tried some more and 'Delete' is this;

DELETE FROM table_name WHERE key = 'value'

If I find the rest I will update the post.

[sander]

Some things I do with SQL, since you asked for trix, maybe it helps.

I created a view in SQL called OrdersTotal, so I get all of my order information attached with just one query at the begin of a flow. That way you gather all your order information with one SQL query instead of multiple.

Code: Select all

select * from OrdersTotal where OrderDate>=CONVERT(char(10), GetDate()-7,126) and CustomerID=20118
and (UpdateURL LIKE 'http%')
and Status<>1

Result log, stripped, I get like 40 lines of order information, so e.g. creating a JDF is very easy then.

Code: Select all

<Column Name="Ordernumber" DataType="INTEGER">1613475</Column>
<Column Name="Quantity" DataType="INTEGER">1</Column>
<Column Name="Filename" DataType="STRING">TSJ_2114-5285.pdf</Column>

Update can be used, I do use it:

Code: Select all

update OrdersUpdate set Status=1 where Ordernumber=[Metadata.Integer:Path="//Column[@Name='Ordernumber']",Dataset="XML",Model="XML"]

In a Hold Job I wait till a filename is found in the database before it continues in a flow:

Code: Select all

[Database.Text:SQL="select Filename from OrdersSwitch where Filename='[Job.Name]' and OrderDate>=CONVERT(char(10), GetDate()-30,126)",Connection="SQL"]

I use database connect to create a CSV log file with all order information, so I can use this with SmartStream Designer VDP so I can create whatever I want with information from the database.

[Dave23]

alright sander,

apologies i know this is an old thread i also have created a view with just the orders i need to process, but how do you query at the start of flow? do you use database connect?
also exactly how do i get all the order info out into a csv file from the database connect log? i have tried this by but all i seem to get is a red cog warning on the database connect element which says warning: job has started.? do i pass a csv file through database module?

cheers

[Dave23]

Apologies me being stupid, it was an error with my odbc connection. All sorted.

Cheers