Let’s learn the use of query-specific columns in APEX.

We are writing the query for a report we have used query-specific column names and validate the query and then we also have used generic column names and only try to parse the query at runtime well what are we used by default well by default um where you go there if we edit our page and go back to page seven let me go back to my report I click on my report you can see here it says use generic column names now and all this is saying is that I know right now at say at save time or compile time although we’re not really compiling but at definition time I know exactly the columns that I need in this report and I know exactly how they should be how they should look I know there I know what their headings are going to be I know what they’re out where their value is coming from I know I understand the full scope of my query.

If that’s true then you can say and use generic column names now and what that means is that gives that opens up the opportunity for you to kind of come over here and you can click on a column and you can change kind of how the column should display you can change attributes and all this stuff’s going to work so in other words by default you have named columns but you have a generic report template so in other words every time you add a column here adds to your query and you can configure metadata about it an apex will roll that into the generic column and it will be visible in the report does that kind of make sense so this is all but what we get by default them but we can change this you can make things even more complex on yourselves so one thing that you can do is um you can change the type we can make it a classic report based on a function.

Let me go ahead and actually whoops let me copy this code I don’t remember if it goes away or not oops yep it does go away so whenever you’re basing code on APL sequel function let me just pop this up and you can still return a static query if you like so, in other words, all I really have to do here and let me go pull up my magnifying glass all I really have to do here is and I could say return there’s my string and there’s the end of my string I’m going to run into an issue here so I’m going to take this time to show you and kind of the quote operator so if I use and I don’t know how well this is actually going to work in the syntax highlighter but if you put a queue in front of a string literal and you can then pick any special character you want after the tick so I’m going to use a tidy and then if I go to the end of my string there you go I can add a tilde here and basically what this tells Oracle is my string this begins here and ends here.

Now it’s important to note that you need to make sure that your string does not contain a tilde character but what that also means is it does not care if there are six kinds of embedded in the middle of my statement if I click validate right everything’s fine it does, however, blow up the syntax highlighter, but that’s okay so here’s a static query doesn’t change you okay everything still looks the same everything’s still good here um because I know that my columns aren’t really changing even though it’s coming from a function I know that everything is going to be the same but what if I wanted to implement some logic where if I want to do something similar to an interactive report where the user can kind of define the user can define which columns to select but maybe in a way that’s very different from interactive reports.

So it’s not uncommon for this to instead of being a string literal like this to be something like em query package dot gets amp query and then let me get my magnifying glass get em query and then you’ll just you’ll pass in something like pee name and you’ll say whatever p2e name and then you’ll say P maybe a department and you’ll pass in p2 depth now and then maybe also there’s some extra logic that may know maybe you have in this API where you say something like pee a user and then you’re actually going to pass in the app using the current person that’s logged in and maybe depending on whose logged in you’re going to do all sorts of stuff here to kind of change the columns that are returned the order that they’re returned right maybe there’s some conditional stuff going on.

So, in other words, you can dynamically / do as long as your produce along as this function returns a valid sequel statement everything’s good to go unfortunately that’s just not what we have here we go okay I actually just say cancel so then if my columns then are controlled by a function like that well then what I have to do is I have to then switch over to using generic column names, in other words, I don’t know what exactly how two out I don’t know how many columns are going to come back out of this I don’t know the order that they’re going to be used or they’re going to come back in I don’t know anything because that’s all just kind of run time stuff so what I need to do is I need to at least know the max number of columns so here I have a 123456789 column so I’m going to say the max number of columns.

I can get is nine and what that’s going to do is if I look over here on the left-hand side notice that I just have these super generic column references ok and there should be let me find it Oh actually it’s probably under attributes an option here for column headings okay so remember we know that we could have 9 but you know maybe by the user’s preferences or the way that we produced the sequel statement maybe we only get four right so what we can do is if we go to the report attributes the heading type we can change the form custom headings and we can change this to a PL sequel function body and so in a similar vein and we need to return and just like the other function has to return a valid sequel query we just have to return a colon-delimited list of headings to use so I’m going to cheat and I’m just I’m not going to actually use a make this bigger for you there we go.

So you would see something like this type you’d see em pre-packaged and you’d say and get column headings and again you’d probably pass in some information but I’m going to cheat and I’m just going to say out lips here I forget the column so that I’m just going to say call one call to call three call four five six seven eight nine okay so here I’m actually returning column headings let me validate that it likes it save and run so here I have called 1 2 3 4 5 & 9 but if I was to dynamically kind of if I had code that dynamically changed the number of columns selected well I would have similar code that dynamically changed the headings to use right there any questions kind of wear this type of code can lead you or kind of what this is useful for or why would you even want to do this definitely, so this is perfect on for maybe you want to implement.

What if here’s good’s an example what if I had and I wanted to use as a very basic kind of query language for my company right we don’t want to necessarily use sequel and we just wanted to provide the end-user to have a kind of way of providing a sequel statement that we ultimately execute so at compile time or when we build the app we have no clue what sequel statement and is going to come at us right we know that we can put limitations on it we can say well we’re only going to report up to 20 columns and as far as you know whatever you want to put in here will query the data for you so in other words at the top of this page and this is put a text area on this report and we’re actually we’re just going to call this bad boy query where does that go perfect.

So I’m going to do something here and I’m just going to say I’m also going to add a button to this page call this go and the last thing that I’m going to do is, I’m going to change my report and we’re going to do ampersand dot P pages this seven query and I only I need to do something different here I need to say and I only want this report to show up or actually and we’ll just run it let’s see what happens it should bark at me yeah, so the query was was possible and didn’t run.

Leave a Comment