OBIEE: Casting String to date then date to string
FILTER("source"."recordCount" USING "source"."snapshot_date" =
EVALUATE('TO_CHAR(%1, ''YYYYMMDD'')', TIMESTAMPADD(SQL_TSI_DAY, -7, EVALUATE('TO_DATE(%1, %2)', "source"."snapshot_date" , 'YYYYMMDD'))))
So i have this piece of code here. I know some will say "Just use the AGO function" But somehow it's causing problems because of it's connection with other tables so what I'm trying to achieve here is like a remake. The process goes this way:
The snapshot_date there is actually in varchar format and not date. So it's like "20131016" and I'm trying to change it to a date then subtract 7 days from it using the TIMESTAMPADD function and then finally returning it back to varchar to use it with FILTER.
This snippet somehow works when testing the FILTER using hardcoded values like "20131016" for example but when tested out with the code above all the row are blank. On paper, the process i assumed would happen goes lke this. "20131016" turns to a date with a format of 20131016 (yyyymmdd) and then less 7 days: 20131009 and then turned into char again "20131009" to be used in the filter.
But somehow that doesn't happen. I think the data format is not applying either to the string->date or the date->string conversion. which results to the values not getting a match at all.
Anyone have any idea what's wrong with my code?
By the way I've already tried to use CAST instead of EVALUATE or TO_TIMEDATE with the same result. Oh and this goes to the formula of the column in BMM.
Thanks
obiee
asked Feb 25 '15 at 15:56
user4606397user4606397
1112
add a comment |
FILTER("source"."recordCount" USING "source"."snapshot_date" =
EVALUATE('TO_CHAR(%1, ''YYYYMMDD'')', TIMESTAMPADD(SQL_TSI_DAY, -7, EVALUATE('TO_DATE(%1, %2)', "source"."snapshot_date" , 'YYYYMMDD'))))
So i have this piece of code here. I know some will say "Just use the AGO function" But somehow it's causing problems because of it's connection with other tables so what I'm trying to achieve here is like a remake. The process goes this way:
The snapshot_date there is actually in varchar format and not date. So it's like "20131016" and I'm trying to change it to a date then subtract 7 days from it using the TIMESTAMPADD function and then finally returning it back to varchar to use it with FILTER.
This snippet somehow works when testing the FILTER using hardcoded values like "20131016" for example but when tested out with the code above all the row are blank. On paper, the process i assumed would happen goes lke this. "20131016" turns to a date with a format of 20131016 (yyyymmdd) and then less 7 days: 20131009 and then turned into char again "20131009" to be used in the filter.
But somehow that doesn't happen. I think the data format is not applying either to the string->date or the date->string conversion. which results to the values not getting a match at all.
Anyone have any idea what's wrong with my code?
By the way I've already tried to use CAST instead of EVALUATE or TO_TIMEDATE with the same result. Oh and this goes to the formula of the column in BMM.
Thanks
obiee
asked Feb 25 '15 at 15:56
user4606397user4606397
1112
add a comment |
FILTER("source"."recordCount" USING "source"."snapshot_date" =
EVALUATE('TO_CHAR(%1, ''YYYYMMDD'')', TIMESTAMPADD(SQL_TSI_DAY, -7, EVALUATE('TO_DATE(%1, %2)', "source"."snapshot_date" , 'YYYYMMDD'))))
So i have this piece of code here. I know some will say "Just use the AGO function" But somehow it's causing problems because of it's connection with other tables so what I'm trying to achieve here is like a remake. The process goes this way:
The snapshot_date there is actually in varchar format and not date. So it's like "20131016" and I'm trying to change it to a date then subtract 7 days from it using the TIMESTAMPADD function and then finally returning it back to varchar to use it with FILTER.
This snippet somehow works when testing the FILTER using hardcoded values like "20131016" for example but when tested out with the code above all the row are blank. On paper, the process i assumed would happen goes lke this. "20131016" turns to a date with a format of 20131016 (yyyymmdd) and then less 7 days: 20131009 and then turned into char again "20131009" to be used in the filter.
But somehow that doesn't happen. I think the data format is not applying either to the string->date or the date->string conversion. which results to the values not getting a match at all.
Anyone have any idea what's wrong with my code?
By the way I've already tried to use CAST instead of EVALUATE or TO_TIMEDATE with the same result. Oh and this goes to the formula of the column in BMM.
Thanks
obiee
asked Feb 25 '15 at 15:56
user4606397user4606397
1112
FILTER("source"."recordCount" USING "source"."snapshot_date" =
EVALUATE('TO_CHAR(%1, ''YYYYMMDD'')', TIMESTAMPADD(SQL_TSI_DAY, -7, EVALUATE('TO_DATE(%1, %2)', "source"."snapshot_date" , 'YYYYMMDD'))))
So i have this piece of code here. I know some will say "Just use the AGO function" But somehow it's causing problems because of it's connection with other tables so what I'm trying to achieve here is like a remake. The process goes this way:
The snapshot_date there is actually in varchar format and not date. So it's like "20131016" and I'm trying to change it to a date then subtract 7 days from it using the TIMESTAMPADD function and then finally returning it back to varchar to use it with FILTER.
This snippet somehow works when testing the FILTER using hardcoded values like "20131016" for example but when tested out with the code above all the row are blank. On paper, the process i assumed would happen goes lke this. "20131016" turns to a date with a format of 20131016 (yyyymmdd) and then less 7 days: 20131009 and then turned into char again "20131009" to be used in the filter.
But somehow that doesn't happen. I think the data format is not applying either to the string->date or the date->string conversion. which results to the values not getting a match at all.
Anyone have any idea what's wrong with my code?
By the way I've already tried to use CAST instead of EVALUATE or TO_TIMEDATE with the same result. Oh and this goes to the formula of the column in BMM.
Thanks
obiee
obiee
asked Feb 25 '15 at 15:56
user4606397user4606397
1112
asked Feb 25 '15 at 15:56
user4606397user4606397
1112
asked Feb 25 '15 at 15:56
user4606397user4606397
1112
asked Feb 25 '15 at 15:56
user4606397user4606397
1112
asked Feb 25 '15 at 15:56
user4606397user4606397
1112
1112
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You might get some clues by looking at the SQL generated by the BI Server. I can't see any issues with your column expression, so I wouldn't limit your debugging to that alone.
A query returning nulls is often caused by incorrect levels being set (especially on logical table sources, but potentially on a measure column too). This will often result in some form of SELECT NULL FROM ... in the physical SQL.
answered Feb 26 '15 at 13:23
jackohugjackohug
43429
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f28723590%2fobiee-casting-string-to-date-then-date-to-string%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You might get some clues by looking at the SQL generated by the BI Server. I can't see any issues with your column expression, so I wouldn't limit your debugging to that alone.
A query returning nulls is often caused by incorrect levels being set (especially on logical table sources, but potentially on a measure column too). This will often result in some form of SELECT NULL FROM ... in the physical SQL.
answered Feb 26 '15 at 13:23
jackohugjackohug
43429
add a comment |
You might get some clues by looking at the SQL generated by the BI Server. I can't see any issues with your column expression, so I wouldn't limit your debugging to that alone.
A query returning nulls is often caused by incorrect levels being set (especially on logical table sources, but potentially on a measure column too). This will often result in some form of SELECT NULL FROM ... in the physical SQL.
answered Feb 26 '15 at 13:23
jackohugjackohug
43429
add a comment |
You might get some clues by looking at the SQL generated by the BI Server. I can't see any issues with your column expression, so I wouldn't limit your debugging to that alone.
A query returning nulls is often caused by incorrect levels being set (especially on logical table sources, but potentially on a measure column too). This will often result in some form of SELECT NULL FROM ... in the physical SQL.
answered Feb 26 '15 at 13:23
jackohugjackohug
43429
You might get some clues by looking at the SQL generated by the BI Server. I can't see any issues with your column expression, so I wouldn't limit your debugging to that alone.
A query returning nulls is often caused by incorrect levels being set (especially on logical table sources, but potentially on a measure column too). This will often result in some form of SELECT NULL FROM ... in the physical SQL.
answered Feb 26 '15 at 13:23
jackohugjackohug
43429
answered Feb 26 '15 at 13:23
jackohugjackohug
43429
answered Feb 26 '15 at 13:23
jackohugjackohug
43429
answered Feb 26 '15 at 13:23
jackohugjackohug
43429
43429
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f28723590%2fobiee-casting-string-to-date-then-date-to-string%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
