Re: How create a view from a SQL string in a Stored Procedure?

Ove wrote: > > Hi. > Please help me! > I am the only one at the office that not is on vacation. > I don't know anything about Informix and Stored procedures, but i must > create a view in the database > that displays data from several tables in order to get a proper ground for > statistics in Excel (which i am good at). > My SQL string that produces my data is: Just add: CREATE VIEW my_new_view AS > SELECT supercustomer.errandid, supercustomer.createdby, > supercustomer.datecreate, supercustomer.datefinish, descisionlog.code,> customer.segment, creditdescision.typeof, credit.wantedcredit, credit.credit > FROM (((credit INNER JOIN creditdescision ON credit.errandid > creditdescision.errandid) INNER JOIN customer ON credit.errandid > customer.errandid) INNER JOIN descisionlog ON credit.errandid > descisionlog.errandid) INNER JOIN supercustomer ON descisionlog.errandid > supercustomer.errandid; Optionally you can rename the columns in the view by adding a parenthesised list of column names following the view name. However, this query is NOT in Informix supported syntax. There is no INNER JOIN clause, unless you intend an OUTER join which just uses the keyword OUTER before the joined table in the FROM list. I'm guessing what you intend inner joins here so try this: create view my_new_view ( errandid, createdby, datecreate, datefinish, code, segment, typeof, wantedcredit )as select sc.errandid, sc.createdby, sc.datecreate, sc.datefinish, dl.code, c.segment, cd.typeof, cr.wantedcredit, cr.credit from credit cr, creditdecision cd, customer c, decisionlog dl, supercustomer sc where cr.errandid = cd.errandid and cr.errandid = c.errandid and cr.errandid = dl.errandid and dl.errandid = dl.errandid; > When i run this in Access i get my table properly but now i want it via ODBC > to Excel and i want to call a view instead of this complex string (it hangs > my computer). > My call from Excel is: > iChannel = SQLOpen(DSN=TEST) > sSQL = "SELECT * FROM MY_NEW_VIEW" > SQLExecQuery iChannel; sSQL > SQLRetrieve iChannel; My_Destination_In_Excel > I have access to the database via Telnet and wants to know how to create > MY_NEW_VIEW from there. Art S. Kagel