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  iWay Software Product Forum on Focal Point    Inserting a variable into SQL
Go
New
Search
Notify
Tools
Reply
  
Inserting a variable into SQL
 Login/Join
 
Platinum Member
posted
I have the following code:

TABLE FILE XXX
PRINT
A
B
COMPUTE C/D18.2C = A - B ;
ON TABLE SET PAGE NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDTBL4 FORMAT FOCUS
END
-RUN

DEFINE FILE HOLDTBL4
C_DISP/D18.2C = C ;
END

SQL SQLMSS
INSERT INTO XXX
(KEY1
,KEY2
,AMOUNT)
VALUES (1,114,C_DISP)
END

But WF thinks C_DISP is a column name and gives me an error. If I put quotes around C_DISP
then WF thinks it's a literal and can't insert into a numeric field.

Is there a solution to this?


7.7.02
Windows
EXCEL, PDF, CSV, TEXT
 
Posts: 106 | Registered: June 25, 2009Reply With QuoteReport This Post
Master
posted Hide Post
The 2 pieces of this puzzle I worked on (seperately) pretty extensively last week. There may be more than one way to skin this cat, but here's my 2¢...

TABLE FILE XXX
PRINT
	A
	B
	COMPUTE C/D18.2C = A - B ;

ON TABLE SET PAGE NOLEAD
ON TABLE NOTOTAL
-*ON TABLE HOLD AS HOLDTBL4 FORMAT FOCUS
ON TABLE HOLD AS HOLDTBL4 FORMAT ALPHA
END
-RUN


-READ HOLDTBL4 &C.D18.2C.
-RUN


-* Make it visible to Dialog Manager
-SET C_DISP = C;


SET EMGSRV=OFF
SET SQLENGINE = SQLMSS
SQL SET SERVER Your_data_connection_name
-RUN
SQL

INSERT INTO XXX
(KEY1
,KEY2
,AMOUNT)
VALUES (1,114,&C_DISP)

;
END


Hope this helps you on your way.

- ABT


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thanks for the response ABT, but when I execute this code, I get:

(FOC339) DIALOGUE MANAGER -READ FAILED: CHECK FILEDEF OR ALLOCATION FOR: -READ
HOLDTBL4 &C.D18.2C.


7.7.02
Windows
EXCEL, PDF, CSV, TEXT
 
Posts: 106 | Registered: June 25, 2009Reply With QuoteReport This Post
Master
posted Hide Post
Maybe the Decimal data type (with it's decimal) is throwing things off. Can you make an edit so that its an alpha field just for error checking purposes? Also, I spotted a typo in the next section, it should read: -SET &C_DISP = &C;

Suggest:

DEFINE FILE XXX
   COMPUTE C_1/D18.2C = A - B ;
   C/A10 = EDIT(C_1);
END

TABLE FILE XXX
PRINT
	A
	B
-*	COMPUTE C/D18.2C = A - B ;
	C

ON TABLE SET PAGE NOLEAD
ON TABLE NOTOTAL
-*ON TABLE HOLD AS HOLDTBL4 FORMAT FOCUS
ON TABLE HOLD AS HOLDTBL4 FORMAT ALPHA
END
-RUN


-READ HOLDTBL4 &C.A10.
-RUN


-* Make it visible to Dialog Manager
-SET &C_DISP = &C;


SET EMGSRV=OFF
SET SQLENGINE = SQLMSS
SQL SET SERVER Your_data_connection_name
-RUN
SQL

SELECT '&C_DISP' AS 'My Output'

-- INSERT INTO XXX
-- (KEY1
-- ,KEY2
-- ,AMOUNT)
-- VALUES (1,114,&C_DISP)

;
END


The edit may need some work, but I think you get the idea. Note the 'Select' statement will just dump the variable value to the screen, better for error checking than running it through the INSERT routine.

- ABT


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
It appears that the format in the read (D18.2C) is causing the problem. But, I have to insert this as a numeric in the database, so converting to alpha does me no good.


7.7.02
Windows
EXCEL, PDF, CSV, TEXT
 
Posts: 106 | Registered: June 25, 2009Reply With QuoteReport This Post
Master
posted Hide Post
Here's a CAR example. Note, I have used Alpha examples as sanity checks. You can use these as a jumping off point. I didn't say it was pretty...

DEFINE FILE CAR
	MARKUP/D8.2C = RETAIL_COST - DEALER_COST;
END

TABLE FILE CAR 
PRINT
	DEALER_COST
	RETAIL_COST
	MARKUP
WHERE RECORDLIMIT EQ 1;
ON TABLE HOLD AS MY_PROFIT_MARGIN_1 FORMAT ALPHA
END
-RUN


-* The HOLD file is Format Alpha, which is all the fields strung
-* together.  You have to edit out the piece you want.  In this
-* case the 2 'COST' fields are D7.

DEFINE FILE MY_PROFIT_MARGIN_1
	MARKUP/A6 = EDIT(MARKUP_TEMP,'$$$$$$$$$$$$$$999999999999');
END

TABLE FILE MY_PROFIT_MARGIN_1
PRINT
	MARKUP
ON TABLE HOLD AS MY_PROFIT_MARGIN FORMAT ALPHA
END
-RUN



-READ MY_PROFIT_MARGIN &MARKUP.A6.
-RUN


-SET &DM_MARKUP = &MARKUP;
-TYPE &DM_MARKUP


-* Make it visible to Dialog Manager

SET EMGSRV=OFF
SET SQLENGINE = SQLMSS
SQL SET SERVER Some_SQL_Server_Data_Connection
-RUN
SQL

SELECT '&DM_MARKUP' AS 'My Output'

;
END


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
When I convert to alpha, it will do the -READ and insert into the table, but I get an erroneous number.


7.7.02
Windows
EXCEL, PDF, CSV, TEXT
 
Posts: 106 | Registered: June 25, 2009Reply With QuoteReport This Post
Master
posted Hide Post
Does the CAR example work? I found out in my case that since I had a variable length field, it 'helped me out' by prepending a numeric onto the data that said how long that variable length field was. It wasn't until I did a ?FF or a PRINT * on the hold file that I saw what was happening. Maybe this is your issue?

- ABT


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Unless you set DMPRECISION, Dialogue Manager will default to storing numeric data as integers without decimals. The code below should work, but you will only get the first MARKUP value from the CAR file inserted into your table, because there is only one read of the save file. If you want to process all of the markup values placed in the save file, then you must introduce a repeat loop to do so. The second code example shows how that might work.

SET DMPRECISION = 2
SET HOLDLIST = PRINTONLY

TABLE FILE CAR
 PRINT
 COMPUTE MARKUP/D18.2C = RETAIL_COST - DEALER_COST ;
 ON TABLE SAVE AS SAVEFILE
END

-RUN
-READ SAVEFILE &C_DISP.18
-*TYPE &C_DISP

SET SQLENGINE = SQLMSS
SQL SET SERVER <your_data_connection_name>

SQL
INSERT INTO XXX
(KEY1
,KEY2
,AMOUNT)
VALUES (1,114,&C_DISP);
END

With repeat loop to process all data in the save file:

SET DMPRECISION = 2
SET HOLDLIST = PRINTONLY

TABLE FILE CAR
 PRINT
 COMPUTE MARKUP/D18.2C = RETAIL_COST - DEALER_COST ;
 ON TABLE SAVE AS SAVEFILE
END

-RUN
-SET &RECCOUNT = &LINES ;

SET SQLENGINE = SQLMSS
SQL SET SERVER <your_data_connection_name>
-RUN

-REPEAT :ENDREPEAT1 &RECCOUNT TIMES
-READ SAVEFILE NOCLOSE &C_DISP.18
-*TYPE &C_DISP

SQL
INSERT INTO XXX
(KEY1
,KEY2
,AMOUNT)
VALUES (1,114,&C_DISP);
END

-RUN
-:ENDREPEAT1


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Master
posted Hide Post
Dan,
Thanks for bringing that up, my piece was in the middle of a loop too (setting environmental variables for all items in a table). I did fail to mention this.

- ABT


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Inserting a variable into SQL

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