INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to keep sql format in the pervasive view

How to keep sql format in the pervasive view

(OP)
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
 

RE: How to keep sql format in the pervasive view

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

RE: How to keep sql format in the pervasive view

(OP)
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
 

RE: How to keep sql format in the pervasive view

Quote:

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.

Quote:

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

RE: How to keep sql format in the pervasive view

(OP)
Hi Mirtheil,

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

Tianjin
 

RE: How to keep sql format in the pervasive view

Thanks for the star.  Glad I could help.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close