The filter not returning any records when using Ask Question, but got record return when writing SQL directly. I am hooked metabase with MSSQL.
Hi @tomato
Without more details it’s impossible to know.
Please post “Diagnostic Info” from Admin > Troubleshooting.
And perhaps you’re seeing the same problem as this topic: The default date of the date filter
Hi, it not related to The default date of the date filter issue, the issue I am facing is there are data returned when running native SQL vs asking questing again the same table with the same where clause, adding filter when asking question, and no result displayed).
Please post “Diagnostic Info” from Admin > Troubleshooting.
Which version of SQL Server are you using?
When using the Query Browser, then click the Editor-icon in top-right corner, and then “View the SQL”-button - please post both that and the Native query you manually created.
Do I need to turn on more detail logging before running thoes?
@tomato No, I’m not asking for logs. I’m asking for “Diagnostic Info”, your SQL Server version, and the two queries you’re running.
{
“browser-info”: {
“language”: “en-US”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.92 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“java.runtime.name”: “Java™ SE Runtime Environment”,
“java.runtime.version”: “1.8.0_211-b12”,
“java.vendor”: “Oracle Corporation”,
“java.vendor.url”: “http://java.oracle.com/”,
“java.version”: “1.8.0_211”,
“java.vm.name”: “Java HotSpot™ Client VM”,
“java.vm.version”: “25.211-b12”,
“os.name”: “Windows 10”,
“os.version”: “10.0”,
“user.language”: “en”,
“user.timezone”: “America/Los_Angeles”
},
“metabase-info”: {
“databases”: [
“h2”,
“sqlserver”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“tag”: “v0.34.3”,
“date”: “2020-02-25”,
“branch”: “release-0.34.x”,
“hash”: “1a83edb”
},
“settings”: {
“report-timezone”: “US/Pacific”
}
}
}
select * from tat_time where tk_empl_uno = 1673
SELECT TOP 1048576 “TimeModel”.“Time”.“ACTION_CODE” AS “ACTION_CODE”, “TimeModel”.“Time”.“ACTIVITY_CODE” AS “ACTIVITY_CODE”,
“TimeModel”.“Time”.“ACTIVITY_COUNT” AS “ACTIVITY_COUNT”, “TimeModel”.“Time”.“ADJ_AU_EMPL_UNO” AS “ADJ_AU_EMPL_UNO”, “TimeModel”.“Time”.“ADJ_CODE” AS “ADJ_CODE”,
“TimeModel”.“Time”.“ADJ_DE_EMPL_UNO” AS “ADJ_DE_EMPL_UNO”, “TimeModel”.“Time”.“ADMIN_EDITOR_REVIEWED” AS “ADMIN_EDITOR_REVIEWED”, “TimeModel”.“Time”.“ADV_PREMDISC_AMT”
AS “ADV_PREMDISC_AMT”, “TimeModel”.“Time”.“AU_EMPL_UNO” AS “AU_EMPL_UNO”, “TimeModel”.“Time”.“BASE_AMT” AS “BASE_AMT”, “TimeModel”.“Time”.“BASE_HRS”
AS “BASE_HRS”, “TimeModel”.“Time”.“BASE_HRS_UNROUNDED” AS “BASE_HRS_UNROUNDED”, “TimeModel”.“Time”.“BILL_PRINT” AS “BILL_PRINT”, “TimeModel”.“Time”.“BILL_SORT_SEQ”
AS “BILL_SORT_SEQ”, “TimeModel”.“Time”.“BILL_TRAN_UNO” AS “BILL_TRAN_UNO”, “TimeModel”.“Time”.“BILLABLE_FLAG” AS “BILLABLE_FLAG”, “TimeModel”.“Time”.“BILLED_AMT” AS “BILLED_AMT”, “TimeModel”.“Time”.“BILLED_EMPL_UNO” AS “BILLED_EMPL_UNO”, “TimeModel”.“Time”.“BILLED_HRS” AS “BILLED_HRS”, “TimeModel”.“Time”.“BILLED_TAX_AMT” AS “BILLED_TAX_AMT”, “TimeModel”.“Time”.“BILLED_TAX_PCT” AS “BILLED_TAX_PCT”, “TimeModel”.“Time”.“CALC_WIP” AS “CALC_WIP”, “TimeModel”.“Time”.“CLIENT_MATTER_DISPLAY” AS “CLIENT_MATTER_DISPLAY”, “TimeModel”.“Time”.“CLIENT_UNO” AS “CLIENT_UNO”, “TimeModel”.“Time”.“CTS_PREMDISC_AMT” AS “CTS_PREMDISC_AMT”, “TimeModel”.“Time”.“CURRENCY_CODE” AS “CURRENCY_CODE”, “TimeModel”.“Time”.“DELAYED_SPLIT_NUM” AS “DELAYED_SPLIT_NUM”, “TimeModel”.“Time”.“DEPT” AS “DEPT”, “TimeModel”.“Time”.“DISB_UNO” AS “DISB_UNO”, “TimeModel”.“Time”.“DOE_STATUS” AS “DOE_STATUS”, “TimeModel”.“Time”.“DRUNO” AS “DRUNO”, “TimeModel”.“Time”.“ENTRY_EMPL_UNO” AS “ENTRY_EMPL_UNO”, “TimeModel”.“Time”.“ENTRY_STATUS” AS “ENTRY_STATUS”, “TimeModel”.“Time”.“FROM_POSTED_VIEW” AS “FROM_POSTED_VIEW”, “TimeModel”.“Time”.“GROSS_AMT” AS “GROSS_AMT”, “TimeModel”.“Time”.“HAS_ERRORS” AS “HAS_ERRORS”, “TimeModel”.“Time”.“HOLD_DATE” AS “HOLD_DATE”, “TimeModel”.“Time”.“IMPORT_NUM” AS “IMPORT_NUM”, “TimeModel”.“Time”.“IS_WIP” AS “IS_WIP”, “TimeModel”.“Time”.“LAST_MODIFIED” AS “LAST_MODIFIED”, “TimeModel”.“Time”.“LATEST_PERIOD” AS “LATEST_PERIOD”, “TimeModel”.“Time”.“LOCATION_CODE” AS “LOCATION_CODE”, “TimeModel”.“Time”.“MATTER_UNO” AS “MATTER_UNO”, “TimeModel”.“Time”.“MERGE_TIME_UNO” AS “MERGE_TIME_UNO”, “TimeModel”.“Time”.“NAR_TEXT” AS “NAR_TEXT”, “TimeModel”.“Time”.“NAR_TEXT_ID” AS “NAR_TEXT_ID”, “TimeModel”.“Time”.“NEEDS_UI_VALIDATION” AS “NEEDS_UI_VALIDATION”, “TimeModel”.“Time”.“OFFC” AS “OFFC”, “TimeModel”.“Time”.“ORIGTASK_NUM” AS “ORIGTASK_NUM”, “TimeModel”.“Time”.“PA_MOD_COUNT” AS “PA_MOD_COUNT”, “TimeModel”.“Time”.“PA_SESSION_UNO” AS “PA_SESSION_UNO”, “TimeModel”.“Time”.“PB_TEXT” AS “PB_TEXT”, “TimeModel”.“Time”.“PB_TEXT_ID” AS “PB_TEXT_ID”, “TimeModel”.“Time”.“PERIOD” AS “PERIOD”, “TimeModel”.“Time”.“PHASE_CODE” AS “PHASE_CODE”, “TimeModel”.“Time”.“PHTASK_UNO” AS “PHTASK_UNO”, “TimeModel”.“Time”.“POST_DATE” AS “POST_DATE”, “TimeModel”.“Time”.“PRINTED” AS “PRINTED”, “TimeModel”.“Time”.“PROF” AS “PROF”, “TimeModel”.“Time”.“PROJ” AS “PROJ”, “TimeModel”.“Time”.“RANK_CODE” AS “RANK_CODE”, “TimeModel”.“Time”.“RATE_METHOD” AS “RATE_METHOD”, “TimeModel”.“Time”.“RETAINED_BILLED_AMT” AS “RETAINED_BILLED_AMT”, “TimeModel”.“Time”.“RETAINED_BILLED_HRS” AS “RETAINED_BILLED_HRS”, “TimeModel”.“Time”.“SOURCE” AS “SOURCE”, “TimeModel”.“Time”.“SPLIT_NUM” AS “SPLIT_NUM”, “TimeModel”.“Time”.“START_TIME” AS “START_TIME”, “TimeModel”.“Time”.“STD_AMT” AS “STD_AMT”, “TimeModel”.“Time”.“SUPER_EMPL_UNO” AS “SUPER_EMPL_UNO”, “TimeModel”.“Time”.“TASK_CODE” AS “TASK_CODE”, “TimeModel”.“Time”.“TAXABLE” AS “TAXABLE”, “TimeModel”.“Time”.“TIME_UNO” AS “TIME_UNO”, “TimeModel”.“Time”.“TK_EMPL_UNO” AS “TK_EMPL_UNO”, “TimeModel”.“Time”.“TOBILL_AMT” AS “TOBILL_AMT”, “TimeModel”.“Time”.“TOBILL_EMPL_UNO” AS “TOBILL_EMPL_UNO”, “TimeModel”.“Time”.“TOBILL_HRS” AS “TOBILL_HRS”, “TimeModel”.“Time”.“TRAN_DATE” AS “TRAN_DATE”, “TimeModel”.“Time”.“TRANS_MOD_UNO” AS “TRANS_MOD_UNO”, “TimeModel”.“Time”.“TRANSFERRED” AS “TRANSFERRED”, “TimeModel”.“Time”.“UNSETTLED_BAL” AS “UNSETTLED_BAL”, “TimeModel”.“Time”.“VALIDATION_ACTION” AS “VALIDATION_ACTION”, “TimeModel”.“Time”.“WIP_STATUS” AS “WIP_STATUS”, “TimeModel”.“Time”.“WORKING_TKPR” AS “WORKING_TKPR”
FROM “TimeModel”.“Time”
WHERE “TimeModel”.“Time”.“TK_EMPL_UNO” = 1673
here are the two queries, the table name seem different, but they are the same and metabase added schema (TimeModel), I compared the fields to confirm
Metabase added the schema TimeModel, which you can also add to your Native query (SQL Server is likely adding that for you automatically), but you’re querying a different table.
With Native you’re querying tat_time, whereas it’s Time with Query Browser.
If you try querying the same table, then I’m sure you’re getting the same result.
Enable query logging (is it called Query Profiling perhaps?) on your SQL Server, then you can see the exact query being made on your database.
I am not able to see tat_time in the TimeModel schema when using query browser?
@tomato You should, but I don’t know if you have given it a different name. See “Browse Data” > “Learn about our data” > (your-db-name) > “Tables in (your-db-name)” > click through all the tables, which will show “Actual name in database” when viewing the details for a table.
I am able to see the tat_time table when search the table name, but when I using query browser, the table just showed as Time under TimeModel. I did turned off the friendly name option.