Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] DEFINE field -- possible to detect type of a database field?
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] DEFINE field -- possible to detect type of a database field?
 Login/Join
 
Master
posted
We have a routine that loads data from an uploaded Excel spreadsheet. Lots of columns with numbers. The problem comes when one of the columns has no entry. The data is theoretically a number, but when the file is uploaded via the portal interface WebFOCUS creates an A1V format for the no-entry column instead of an I5 like it does all the other columns. The result is that some of our number columns aren't numbers in WF.

So . . . convert type, right? The problem is we don't know what columns this will happen to in advance.

So here's the question -- is there a function (or any other way) to detect if a field is a character type or a number type, so that it can be converted only when it needs to be converted? The IF statement would more or less go like this --

DEFINE FILE INPUT_EXCEL
  COLUMN3_FIXED/I5 = IF COLUMN3 is an alpha type instead of a number type THEN EDIT(COLUMN3)
                                                                          ELSE      COLUMN3;
END

This message has been edited. Last edited by: FP Mod Chuck,



 
Posts: 924 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
https://webfocusinfocenter.inf...ang/source/edit2.htm

Alternatively, you could add a row of dummy values with the right formats always populated and filter it out in the master based on the value of one of the fields. That way all columns will have a legitimate format but the dummy row is eliminated in all reports.


WebFOCUS 8206, Unix, Windows
 
Posts: 1660 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Master
posted Hide Post
Thanks for replying David!

That is an examination of the contents of an alpha string. The question I'm asking is a step higher -- is the field defined as an alpha string? From run to run I don't know the format of a particular field.

You too Babak! But EDIT2 balks when you send it an Alpha format. I'm either getting an alpha or an integer, I don't know which. If there was a WF function (or iWay) that simply said, "don't care what it is, I'll convert to alpha" we'd be set. But I don't think that exists?



 
Posts: 924 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Can you put a single row with dummy values and filter them out? That way the upload will format the columns correctly. You'll just have to add a WHERE test to omit that one row and I think you can do that using DBA techniques.


WebFOCUS 8206, Unix, Windows
 
Posts: 1660 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
When you ask for format of the field, is that the Master File column format or the data content ?

For a Master File column format you can query the catalog with SYSCOLUM to get the USAGE, then change the code.

For content, the format should always be ALPHA and then you can use the function PATTERN to determine of there are alpha chars in the string and convert appropriately.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
file is uploaded via the portal interface

Questions:
Do any of the rows in the column actually have a value?
Depending upon the answer to the first question, what sample size are you using during the upload and could you increase it to all rows?

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5601 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Master
posted Hide Post
It's the format defined in the Master File. The problem is that the master file is dynamic. It shouldn't be, as it's the same each time someone uploads a file. But the hitch is that columns that don't have a number entry in them default to character instead of integer.

The end-user uploads an Excel file through the portal. When they do a master file is created by WF automatically. It's a short little thing, maybe 25 rows. But it's user-driven so we're trying to make it as simple as possible for them, and bullet-proof in implementation.

That master file does not have a dependable structure in spite of it being the exact same thing each month, due to this odd behavior in the WF software that creates the master file. This was going to be easy-peasy but we have DEFINEs that total up rows and these errant text fields in the defines throw an error.

So yeah, the original question still stands -- any way to query the format of a field in WebFOCUS? As it stands we're likely going to have to re-engineer the process, or put a macro into the spreadsheet that forces zeros into the fields.



 
Posts: 924 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Could you possibly take advantage of APP PATH? e.g. where does the synonym get written when the user uploads a file and can you force an APP PREPENDPATH to their domain to ensure that the synonym you want to use gets read first?

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5601 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Master
posted Hide Post
You're saying build a static Master File.



 
Posts: 924 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
If the column is supposed to be a number but is read as alpha because it was empty then you could create an IF THEN ELSE to check if the column is missing. If the column is missing, skip the calculation or change it to numeric format, otherwise, it could be used in a calculation. If you have anything other than a missing value then, your user has loaded a real alpha field into a number anyways.

Let's say FIELD1 has nothing in it when it's loaded from Excel. WebFOCUS uploads it into an Alpha field and makes it a null value. Now, let's just calculate a new field in DEFINE that checks to see if the value is null. If it is, then you know it's supposed to be 0 or set it to whatever you want. Otherwise, just set it to the original field value.

 
DEFINE FILE TESTFILE
FIELD1_X/I9=IF FIELD1 IS MISSING THEN 0 ELSE FIELD1;
END
TABLE FILE TESTFILE
PRINT FIELD1 FIELD1_X FIELD2 FIELD3
END 


WebFOCUS 8206, Unix, Windows
 
Posts: 1660 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Master
posted Hide Post
quote:
Originally posted by BabakNYC:
If the column is supposed to be a number but is read as alpha because it was empty then you could create an IF THEN ELSE to check if the column is missing. If the column is missing, skip the calculation or change it to numeric format, otherwise, it could be used in a calculation. If you have anything other than a missing value then, your user has loaded a real alpha field into a number anyways.

Let's say FIELD1 has nothing in it when it's loaded from Excel. WebFOCUS uploads it into an Alpha field and makes it a null value. Now, let's just calculate a new field in DEFINE that checks to see if the value is null. If it is, then you know it's supposed to be 0 or set it to whatever you want. Otherwise, just set it to the original field value.

 
DEFINE FILE TESTFILE
FIELD1_X/I9=IF FIELD1 IS MISSING THEN 0 ELSE FIELD1;
END
TABLE FILE TESTFILE
PRINT FIELD1 FIELD1_X FIELD2 FIELD3
END 


We gave this one a shot and couldn't make it work without throwing an error when the field is an integer.



 
Posts: 924 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Check SYSCOLUM, and handle accordingly.

Should be easy


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Master
posted Hide Post
quote:
Originally posted by Waz:
Check SYSCOLUM, and handle accordingly.

Should be easy


SYSCOLUM is giving me information on about a dozen tables, none of which I've ever heard of. That's all. Is there a way to get it to expand its horizons to include master files I've created?



 
Posts: 924 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
 
Posts: 1660 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
Before continuing, please advise what version of WF you are using - it could mean a different recommendation! Until you let us know, I am assuming that you are using 8.2.03 at least.

Your very first comment states "We have a routine that loads data from an uploaded Excel spreadsheet". Is this a WebFOCUS routine - I am guessing so otherwise why would you be asking about changing synonym columns!!!

So,
  • How are your users uploading the Excel S/S?
  • Where are they loading it to?
  • Are they using the same file name each time?


The synonym that is created will have a DATASET attribute that identifies the uploaded file location, so you could make a copy of that synonym and delete that dataset attribute. Then make modifications to the USAGE attributes and place the resulting synonym in an app folder that the users do not have access to.

In your "routine that loads data" (assuming it is WebFOCUS), issue an APP PREPENDPATH to the app folder containing your static copy of the synonym (created above) and follow that with a FILEDEF to the location of the uploaded Excel S/S.

Better still, rather than them upload it each time, just tell them to save it to a central network drive that your "routine that loads data" points at. Then your WebFOCUS load routine could just pick it up from the same place each time.

If you wanted to get clever, you could interrogate the network drive folder to identify the latest file and then dynamically issue the FILEDEF. You could then change the extension of the file so that you do not pick it up a second time.

So many different ways of doing this but, IMHO, changing the synonym each time is not the way to go.

Just my "two penneth" worth.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5601 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Master
posted Hide Post
quote:


SYSCOLUM -- Alright, so, that's half a mile of source code. I'll boil it down to what you need --


TABLE FILE CAR
PRINT *
ON TABLE HOLD AS EDWARDS
END


TABLE FILE SYSCOLUM
PRINT USAGE
BY TBNAME
BY NAME
WHERE TBNAME EQ 'EDWARDS';
END



This produces what I'm looking for, for hold files and for static masters.



Oddly enough, this does not work --

-* Code that does not work:
TABLE FILE SYSCOLUM
PRINT *
WHERE TBNAME EQ 'EDWARDS';
END

Doesn't work at all. Doesn't return rows. Don't know why, but don't care much. Thanks for the tip. This gives me what I need.



 
Posts: 924 | Location: At the Mast | Registered: May 17, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
SYSCOLUM is a multisegment master, and if you PRINT * it will try to pull from all segments, some of which will have not data.

If you are going to use PRINT * (Never recommended), use PRINT SEG.NAME to get the fields in the NAME segment, etc.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Guru
posted Hide Post

For what it's worth, you can also use CHECK FILE to bring back the formats

    CHECK FILE car HOLD AS INFO
    TABLE FILE INFO
    PRINT FORMAT 
    BY SUFFIX     
    BY FIELDNAME 
    ON TABLE SET PAGE-NUM OFF
    END

Returns:

SUFFIX FIELDNAME FORMAT
FOC ACCEL D6
BHP D6
BODYTYPE A12
CAR A16
COUNTRY A10
DEALER_COST D7
FUEL_CAP D6.1
HEIGHT D5
LENGTH D5
MODEL A24
MPG D6
RETAIL_COST D7
RPM I5
SALES I6
SEATS I3
STANDARD A40
WARRANTY A40
WEIGHT D6
WHEELBASE D6.1
WIDTH D5

This message has been edited. Last edited by: Hallway,


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 431 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] DEFINE field -- possible to detect type of a database field?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.