Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] " in SQL. How do I get rid of them?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] " in SQL. How do I get rid of them?
 Login/Join
 
Håkan
Guru
posted
Anybody has c clue how I force WF NOT to produce double quotes around column names when tranlating to SQL? Normally it's no problem, but if you define a column as referring to a function, you get a -205 error, saying it can't find the column.

Tia

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS DS 8.0.06/08 DS/AS
WebFOCUS RS 8.0.08 (Linux/IBM i)
WebFOCUS Client 8.0.06 (Linux)
 
Posts: 319 | Location: Stockholm, Sweden | Registered: February 04, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
It would be very nice to know the answer to this question.

I also hate the fact that when you're tracing the SQL, it is generated with "e; where the quotes are. I know we live in the web world, but do quotes really have to be displayed as "e;

I say: Get rid of the quotes!


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
debbiej
Member
posted Hide Post
Can we quote you on that?

Ooh, sorry, I couldn't resist. Smiler


WebFOCUS 7.6.11 on Win2003 Server
WebFOCUS 7.7.03 on Win2003 Server
Published, AdHoc, ReportCaster
Output in all variants of Excel
 
Posts: 29 | Location: Ravenna, OH | Registered: December 10, 2003Report This Post
BIGBMN
Silver Member
posted Hide Post
I think what you are doing is issuing a TABLE request against an SQL master file description which then gets translated in to SQL. For example, I have a mfd called EMPLOYEEMSS which describes the SQL Server sample table of EMPLOYEE.
If I issue the following request and then "view the source" of the output, I can see the SQL generated below:

SET TRACEUSER = ON
SET TRACEON = STMTRACE//CLIENT
TABLE FILE EMPLOYEEMSS
PRINT *
END

14.02.19 AE SELECT T1."emp_id",T1."fname",T1."minit",T1."lname",
14.02.19 AE T1."job_id",T1."job_lvl",T1."pub_id",T1."hire_date" FROM
14.02.19 AE pubs.dbo.employee T1;
0 NUMBER OF RECORDS IN TABLE= 43 LINES= 43

Or, If I use an SQL non-passthru request, it does essentially the same thing.

The " within the output is just the quote character, in an escaped format. There are underlying reasons why an escaped value is used instead of the actual quote character for html output.

I’m unclear however, about the error you are running in to. Can you elaborate on the -205 in showing a simple example?

Regards, Ben Naphtali
 
Posts: 34 | Location: 2 Penn Plaza | Registered: July 29, 2004Report This Post
Håkan
Guru
posted Hide Post
I was trying to put a function as the alias in the Master file, like MAX(column_name). The weird thing was that WF generated the correct SQL, but with the quotes, which made DB2 to reject the query. So, instead of WF creating something like T1."MAX(column_name)" I would like to have T1.MAX(columnd_name).


WebFOCUS DS 8.0.06/08 DS/AS
WebFOCUS RS 8.0.08 (Linux/IBM i)
WebFOCUS Client 8.0.06 (Linux)
 
Posts: 319 | Location: Stockholm, Sweden | Registered: February 04, 2004Report This Post
BIGBMN
Silver Member
posted Hide Post
Ahh....Very clever, but....
The ALIAS within a SQL Master File Description is intended to be the real database fieldname, not a function. The quotes are placed around the fieldnames, to account for errors that might occur with special (meta) characters within the fieldnames, and currently there is no way in turning this off.
You could use a MAX.fieldname within the TABLE request.
Regards, ben Naphtali
 
Posts: 34 | Location: 2 Penn Plaza | Registered: July 29, 2004Report This Post
Tony A
Expert
posted Hide Post
Ben,

How likely would an NFR to switch them off be accepted?

I know that functions can be very useful as H has intimated, and I suspect that he has targeted one of the fairly innocuous ones, but others such as datepart functions could be very useful especially as it is difficult to write the underlying supporting parser within any interpreted language to always produce efficient SQL (and yes, I know that refining DB design and indices etc. can often help).

For my own part, I tend to use SQL passthru all the time to avoid having to update MFDs and also having to live with (sometimes) inefficient code, when I can achieve sub-selects, multitudes of disparate joins etc. by coding the SQL myself.

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
BIGBMN
Silver Member
posted Hide Post
Can't really say. If this is something you think many people will need, then please submit this as a NFR with Customer Support Services.
If more control is needed to send SQL, you have the alternative of sending direct SQL requests. Or, you can use the various constructs with TABLE. Something like this, sounds to me like a hybrid functionality adding an SQL function within an MFD.
Regards, Ben Naphtali
 
Posts: 34 | Location: 2 Penn Plaza | Registered: July 29, 2004Report This Post
Håkan
Guru
posted Hide Post
The reason we tried to access a function in the MFD was making a special MFD for a couple of Reporting Objects with clustered joins. That's why it was not an option using SQL. As Tony guessed, the MAX example was not real life, it was just a test to see whether it was possible. Our goal was to access a site-specific function.


WebFOCUS DS 8.0.06/08 DS/AS
WebFOCUS RS 8.0.08 (Linux/IBM i)
WebFOCUS Client 8.0.06 (Linux)
 
Posts: 319 | Location: Stockholm, Sweden | Registered: February 04, 2004Report This Post
Tony A
Expert
posted Hide Post
H,

The only alternative is to create an alternate view in your RDBMS and then create metadata for it within WF.

But then you knew that!

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Håkan
Guru
posted Hide Post
Tony,

I know, so I've submitted a NFR.

I think the best thing would be to have a new SET parameter, eg. SET QOUTE=OFF. Second best would be to have a new parameter in the .acx file.

Håkan


WebFOCUS DS 8.0.06/08 DS/AS
WebFOCUS RS 8.0.08 (Linux/IBM i)
WebFOCUS Client 8.0.06 (Linux)
 
Posts: 319 | Location: Stockholm, Sweden | Registered: February 04, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
It's 2015 and I'm still looking for this - how to get the data adapter to not generate double quotes around table column names.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<FreSte>
posted
I know, it's a workaround (and you probably have this already implemented), but the following tracing generates a file with the SQL.
And then write a report on this file to get the SQL and to get rid of these quotes.


-Fred-


  
SET TRACESTAMP = OFF
SET TRACEUSER  = C:\ibi\apps\baseapp\tracefile.trc
SET TRACEUSER  = ON
SET TRACEOFF   = ALL
SET TRACEON    = SQLAGGR/1/FSTRACE
SET TRACEON    = STMTRACE/1/FSTRACE
SET XRETRIEVAL = OFF

TABLE FILE [master]
  PRINT *
END
 
Report This Post
Tony A
Expert
posted Hide Post
I'd guess that it is because SQL column names could contain blank spaces.

From my experience I've not (yet?) hit or heard of a problem with this other than what Håkan posted 9 years ago.


T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
Tony, it should be an option. In the WebFOCUS GUI there's an option to generate code with or without table name prefixes. It would be nice to have an option to not generate the double quotes.

Håkan mentioned he opened an NFR nine years ago. It's probably been relegated to the trash bin dust heap of history, like some of my NFRs.

Back to work...

This message has been edited. Last edited by: Francis Mariani,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Tony A
Expert
posted Hide Post
quote:
it should be an option

Hmmm, I'm not so sure given many of the end user report writers that I've come across. I wouldn't expect them to have to think(?) about turning quotes on or off.

For "true" developers then I can see the potential but then not all EURW are true developers ....


T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
I could be wrong but there's been a concerted effort to ignore "true" developers. Users über alles!


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] " in SQL. How do I get rid of them?

Copyright © 1996-2020 Information Builders