Create view 1

[PeteAmsterdam]

I need to read a "flat" file with (hundreds) of sql.

The file is very simple:

A status field
A record type field
A key field
A data field

I'd like to define a view for each type of DS so I can use them freely via Sql without having to
hardcode positional substring in the statements.

Basically I know how to do it:

CREATE VIEW MUTABCAB0F (TACDCABINA, TADSCABINA, TADATARILA)
AS SELECT
SUBSTR(TBCDELEME, 1, 5) ,
SUBSTR(TBDATITAB, 1, 50) ,
DATE(SUBSTR(TBDATITAB, 51, 10))
FROM MUTABEL00F
WHERE TBSETTORE = 'CAB'

The view is working but it's really poor since the fields generated are not referenced to the
original DS.

How can I copy the characteristics of one element to another of the CREATE TABLE on CREATE VIEW?

Thanks in advance
Pete

 

[NoCoolHandle]

Pete, a couple of things I am not clear on.

1. Are you importing the "flat file" into SQL and then querying it? or Are you setting it up as a linked server?

2. When you say "file with (hundreds) of sql." do you mean you have hundreds of "rows" of data, or is it sql statements (querys)

3. "each type of DS so I can use them " do you mean dataset?

Rob

 

[PeteAmsterdam]

Rob,
3 2 1
Yes Data Set, sorry,
rows of data,
flat file and then query

Thanks,
Pete

 

[NoCoolHandle]

Pete,

from your responses it sounds like you have Imported your Flat Files into SQL Tables, and then you are running querys on the tables, possibly via views.

A couple more questions.
1. What would a couple of rows of data look like?
2. Do you import these tables often (every time the query is run) or is it a daily/weekly import?
3. Have you looked at indexed views?
4. How are you imorting the data (dts/bcp/bulkinsert)?

Sorry Still no answers (but i believe you are correct your problem is in teh area of having to do substrings on every row you return via the query)

Also I wasn't sure about what you ment by

How can I copy the characteristics of one element to another of the CREATE TABLE on CREATE VIEW?

Do you already have the table structure and you need to dyanmaicly script and execute create view statements>?

Rob

 

[PeteAmsterdam]

1. the file is continously updated.

2. when I says "hundreds Sql" I mean each doing something like this:

select * from x,y where x.field1 = y.field1 and y.field2 in('01' '03') order by....

The point is that the "flat" file contains a lot of data very usefull to select records and I
don't want to substring all the time with hardocde positions.

Thanks in advance
Pete

 

[NoCoolHandle]

Pete, I think I am getting a clearer picture..

Your Flat File contains a list of sqlcommands...
You read the FlatFile into a SQL table?
and then loop through the FlatFile(table) reading the commands and executing them.

What you want to do (please correct me when wrong) is build a series of views (Precompiled sql statements) into sql based on the commands.

At the moment you are looking for an instance of a keyword like select or insert and then using substring to pull the command out of the row.. (maybe more than one row or maybe more than 1 command per row.)

Q. Does this need to be a SQL solution or can the parsing of the strings occour in an outside application or script (vb or java)?

Q. If this is a SQL solution do you just want a way to script a the creation of the View from a string that you have parsed out?


Sorry to ask more questions and not give much back (yet)



Rob