Liveware Publishing Support Forum

Hi,__How do you get around ...I am using SQL unions.____Say .. select aa__ FROM fin_accounts__ WHERE accounts = ^&Enter_Account^__ union__ select bb__ FROM fi_accounts__ WHERE accounts = ^&Enter_Account^____when I paste this to Arpeggio new user sql, it would say that the column is invalid.__If I ran via sql prompt, I do not have problem.__1) If I remove the where clause on the accounts and place this in the filter, it does not give me the correct results.____It seems that arpeggio does not know how to interpret oracle^s "&" variable.____Thanks____Bert__

You could use:____select aa__FROM fin_accounts__WHERE accounts = <<ACCT123>>__union__select bb__FROM fi_accounts__WHERE accounts = <<ACCT123>>____so that the variable is a hard coded account number. When you run this report from runtime, you add an RI_REPLACE to your control table and enter the actual account you want to use into the RI_REPLACE.____Kathleen__R&R Support

Thanks for the suggestion.____Where can I find "Developing Applications documentation" so I can look at the examples on RI_REPLACE parameter.____I am getting an error "Select parameter substitution error, white space found between comma".__In the user sql, I entered in the where clause ...__account = <<^EACCOUNT^>> - I tried several combination, if I do not use a single quote, it will error out that the column is not found - The only combination is to use ^EACCOUNT^.__In the viewer, I added RI_REPLACE parameter. I tried hard coding __RI_REPLACE=^100060^ or RI_REPLACE=100060 and it gave me the above error. __any ideas?__

Kathleen,____I found the Application documentation in the web.__The <<^EACCOUNT^>> does not work when using RI_REPLACE=10060 or RI_REPLACE=^10060^ in the rss input file.__Maybe this functionality is not available in SQL unions(4 separate unions with single common where clause using accounts=<<^EACCOUNT^>>)

Kathleen,__I was able to get the RI_REPLACE working. __If there are 4 table unions, even though I define only one variable <<^EACCOUNT^>>, RI_REPLACE needs 4 entries ..__RI_REPLACE=<<^100060^>>,<<^10060^>>,<<^10060^>>,<<^10060^>>__The last question - how do you pass the parameter during runtime?__Can you substitute a variable in RI_REPLACE=<<^finacc^>>,<<^finacc^>>,<<^finacc^>>,<<^finacc^>>______

You cannot substitute a variable name within the control table.__Your application would need to take the contents of the variable and place it literally into the RI_REPLACE value.____Kathleen__R&R Support

Thanks for the prompt response.__I have to create a filter report that passes the account number and then run the final report based on the output of the filter report.__Another question - RI_SORT - for SQL is this functionality available? I tried hardcoding RI_SORT1=fieldname it is complaining that the column is invalid. Is this arpeggio limitation with SQL?____