How to keep sql format in the pervasive view 1

[Tianjin]

I created a view in pervasive control center by typing sql script as following with proper line feed.
SELECT MIMOH.WOHID, MIMOMD.WOMDID, MIMOMD.ITEM, (MIMOMD.REQQTY-MIMOMD.ENDQTY)*-1 AS MOQTY, MIMOH.ORDERDATE,
MIMOH.STARTDATE, MIMOH.ENDDATE
FROM MIMOH, MIMOMD WHERE MIMOH.WOHID = MIMOMD.WOHID
AND MIMOH.MOSTAT=1

As soon as I saved the view and went back, the sql script turned into a completely different looking and format with everything in a single line. It is very hard to read and edit. (notice that sql script shows in multiple lines down here as text wrap automatically, but in control center it doesn't)
CREATE VIEW "MIMO2" AS SELECT "T1" ."WOHID" ,"T2" ."WOMDID" ,"T2" ."ITEM" ,("T2" ."REQQTY" -"T2" ."ENDQTY" )*-1 "MOQTY" ,"T1" ."ORDERDATE" ,"T1" ."STARTDATE" ,"T1" ."ENDDATE" FROM "MIMOH" "T1" ,"MIMOMD" "T2" WHERE "T1" ."WOHID" = "T2" ."WOHID" AND "T1" ."MOSTAT" = 1

Could anyone here show me how to deal with this.

Thanks

Tianjin

 

[mirtheil]

I do not believe there is a way to do what you are looking for. The line breaks aren't stored in the database (nor should it, in my opinion). I tested with SQL Server 2008 and while it has line breaks, it does not keep the ones a user might set. SQL Server 2008 puts line breaks for certain key words like "FROM" and "WHERE" even if you don't put one in.

I could see an enhancement for the Pervasive Control Center to have line breaks on certain key words (like "FROM" and "WHERE") but storing arbitrary line breaks might be a little tougher.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Tianjin]

HI Mirtheil,

Thank you for answering my question. but I still do not understand why sql script can not keep line breaks in the place as I entered those line breaks to make script in multiple lines in the view edit interface while I was creating the view. However, as soon as I saved and run the view (view runs just perfect without any syntax error) , the script converted into a complete different looking with line break disappeared and some strange words being added, "T1" "T2" for example. can you explain what "T1" "T2" is for and why the script need to be converted.

And also this is not truth for a regular query script. in other words, the sql script always stay in the same looking as what I entered in the query edit interface. It never convert script into other looking before and after save or run the query. (query edit interface bring up by clicking "create a new sql document" command button in control center)

Thanks

tianjin

 

[mirtheil]

However, as soon as I saved and run the view (view runs just perfect without any syntax error) , the script converted into a complete different looking with line break disappeared and some strange words being added, "T1" "T2" for example. can you explain what "T1" "T2" is for and why the script need to be converted.

The "T1" and "T2" are probably table identifiers. Why it's converted is something that only Pervasive can answer. My best guess is that it's better for optimization.

And also this is not truth for a regular query script. in other words, the sql script always stay in the same looking as what I entered in the query edit interface. It never convert script into other looking before and after save or run the query. (query edit interface bring up by clicking "create a new sql document" command button in control center)

"Regular" query documents are stored as text files while a view is stored in the database. They cannot really be compared in terms of formatting.

If you are really concerned with the query formatting, then you should save the "CREATE VIEW" statements as Query documents(SQL files). Personally, I don't worry about the formatting once I issue the CREATE VIEW.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Tianjin]

Hi Mirtheil,

Thank you for the explanation and suggestion.
That works for me.

Tianjin

 

[mirtheil]

Thanks for the star. Glad I could help.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com