I need to stack data from 2 tables into one single table the...

[robert fuschetto]

I need to stack data from 2 tables into one single table then aggregate do a chart. We have done this many times using the MORE command. To use MORE, all column names in the resulting table must have the same NAME and TYPE.

Further, up till now, the column names in both tables were always the same anyway. But in this case the department the user will be prompted for is known as DEPT in table1 and DEPARTMENT in table2.

Since we will prompt via auto creating controls on an HTML page, we need them to be also be similarly named so excess controls are not created. Hope this makes sense.

Anyway, as mentioned, in the first table the column is called DEPT (A5). In the second table it is called DEPARTMENT (A5),

I created a hold file for for table1 with DEPT. It is also used in a WHERE as a Dynamic Multi Select.

For table2 to I first defined DEPT / A5 = table2.DEPARTMENT.

When I go to use table2.DEPARTMENT in my WHERE condition, when I say Equals/Paramterthe DYMAMIC option is not there so I cannot make it MultiSelect.

Can you not use Multi select on a DEFINED column in a WHERE statement

[Manoj Chaurasia]

Are you using App Studio or InfoAssist

[robert fuschetto]

We are using App Studio!

[Manoj Chaurasia]

Robert

I am using

 

and it allowed me to create a Dynamic multiselect parameter with a DEFINEd field

[robert fuschetto]

intersting, may I ask how you did it I used the GUI, to define my fields (top of snip) the theGUI to add the WHERE on my definebut under parametersDYNAMIC is not there. I usually pick: Dynamic then double click the item and check: Multi Select OR from the drop down. Is there another way

 

image.png1270608 212 KB

[Manoj Chaurasia]

That is interesting I just followed your steps and got the same results. When I did mine there was already a DEFINE in the master file and I used that one and it allows me to see Dynamic.

[robert fuschetto]

we are on 820608 I am toldperhaps a bug

[Manoj Chaurasia]

I will upgrade to the latest 8.2.06 tomorrow and let you know if that works or not.

[Manoj Chaurasia]

Robert

I upgraded to 8.2.06-27 and when the DEFINE is in the fex it still wont allow me to specify a Dynamic parameter but if the DEFINE is in the master file it does.

[robert fuschetto]

Yea, I suspected thatso I sort of have to make an extra hold file w/the Define then proceed thanks!

[robert fuschetto]

or will that not work eitheronce it is in a hold file (alpha)or is that hold file considered a masterfile etc.

[Manoj Chaurasia]

You can use the APP HOLD to save the master file to an app folder in your APP PATH one time and update it with a DEFINE. I tried it and it allowed me to use that DEFINE with a Dyanamic parameter.

One other idea is to use SET ASNAMES = ON and when you reference the DEPARTMENT field in creating your hold file make the AS DEPT and then you should just be able to reference that field directly in the filter.