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.
Database connect - SQL Statement
Database connect - SQL Statement
Between jobs!
Re: Database connect - SQL Statement
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.
Result log, stripped, I get like 40 lines of order information, so e.g. creating a JDF is very easy then.
Update can be used, I do use it:
In a Hold Job I wait till a filename is found in the database before it continues in a flow:
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.
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
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"]
Re: Database connect - SQL Statement
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
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
Re: Database connect - SQL Statement
Apologies me being stupid, it was an error with my odbc connection. All sorted.
Cheers
Cheers