pragma_table_info and view
(1.1) Originally by RB Smissaert (RBSmissaert) with edits by Richard Hipp (drh) on 2020-12-02 19:12:58 from 1.0 [source]
Could anybody tell me why this SQL gives me zero rows? There are a number of views in the database.
SELECT
m.type as Object_Type,
m.name as Object_Name,
p.cid as Col_ID,
p.name as Col_Name,
p.type as Col_Type,
p.pk as Col_Is_PK,
p.dflt_value as Col_Default_Val,
p.[notnull] as Col_Is_Not_Null
FROM sqlite_master m inner join pragma_table_info(Object_Name) p
WHERE Object_Type <> 'index'
ORDER BY Object_Name, Col_ID
RBS
Edit: formatting
(2.1) By Keith Medcalf (kmedcalf) on 2020-12-02 19:29:55 edited from 2.0 in reply to 1.1 [link] [source]
Works for me. You have provided insufficient information for meaningful diagnosis.
sqlite> SELECT
...> m.type as Object_Type,
...> m.name as Object_Name,
...> p.cid as Col_ID,
...> p.name as Col_Name,
...> p.type as Col_Type,
...> p.pk as Col_Is_PK,
...> p.dflt_value as Col_Default_Val,
...> p.[notnull] as Col_Is_Not_Null
...> FROM sqlite_master m inner join pragma_table_info(Object_Name) p
...> WHERE Object_Type <> 'index'
...> ORDER BY Object_Name, Col_ID
...>
...> ;
─────────────┬──────────────┬────────┬───────────────┬──────────┬───────────┬─────────────────┬─────────────────┐
Object_Type │ Object_Name │ Col_ID │ Col_Name │ Col_Type │ Col_Is_PK │ Col_Default_Val │ Col_Is_Not_Null │
─────────────┼──────────────┼────────┼───────────────┼──────────┼───────────┼─────────────────┼─────────────────┤
table │ CanCases │ 0 │ Location │ text │ 1 │ │ 1 │
table │ CanCases │ 1 │ Date │ text │ 2 │ │ 1 │
table │ CanCases │ 2 │ Test │ integer │ 0 │ │ 0 │
table │ CanCases │ 3 │ Cases │ integer │ 0 │ │ 0 │
table │ CanCases │ 4 │ Recovered │ integer │ 0 │ │ 0 │
table │ CanCases │ 5 │ ChgActive │ integer │ 0 │ │ 0 │
table │ CanCases │ 6 │ ChgHosp │ integer │ 0 │ │ 0 │
table │ CanCases │ 7 │ ChgICU │ integer │ 0 │ │ 0 │
table │ CanCases │ 8 │ ChgDeath │ integer │ 0 │ │ 0 │
table │ CanCases │ 9 │ NetActive │ integer │ 0 │ │ 0 │
table │ CanCases │ 10 │ NetHosp │ integer │ 0 │ │ 0 │
table │ CanCases │ 11 │ NetICU │ integer │ 0 │ │ 0 │
table │ CanCases │ 12 │ NetDeath │ integer │ 0 │ │ 0 │
table │ Cases │ 0 │ date │ text │ 0 │ │ 1 │
table │ Cases │ 1 │ zone │ text │ 0 │ │ 1 │
table │ Cases │ 2 │ gender │ text │ 0 │ │ 1 │
table │ Cases │ 3 │ age │ text │ 0 │ │ 1 │
table │ Cases │ 4 │ status │ text │ 0 │ │ 1 │
table │ Cases │ 5 │ cnt │ integer │ 0 │ │ 1 │
view │ Summary │ 0 │ date │ │ 0 │ │ 0 │
view │ Summary │ 1 │ active │ │ 0 │ │ 0 │
view │ Summary │ 2 │ recovered │ │ 0 │ │ 0 │
view │ Summary │ 3 │ died │ │ 0 │ │ 0 │
view │ Summary │ 4 │ RollActive │ │ 0 │ │ 0 │
view │ Summary │ 5 │ RollRecovered │ │ 0 │ │ 0 │
view │ Summary │ 6 │ RollDied │ │ 0 │ │ 0 │
view │ Summary │ 7 │ CumeActive │ │ 0 │ │ 0 │
view │ Summary │ 8 │ CumeRecovered │ │ 0 │ │ 0 │
view │ Summary │ 9 │ CumeDied │ │ 0 │ │ 0 │
view │ SummaryCGY │ 0 │ date │ │ 0 │ │ 0 │
view │ SummaryCGY │ 1 │ active │ │ 0 │ │ 0 │
view │ SummaryCGY │ 2 │ recovered │ │ 0 │ │ 0 │
view │ SummaryCGY │ 3 │ died │ │ 0 │ │ 0 │
view │ SummaryCGY │ 4 │ RollActive │ │ 0 │ │ 0 │
view │ SummaryCGY │ 5 │ RollRecovered │ │ 0 │ │ 0 │
view │ SummaryCGY │ 6 │ RollDied │ │ 0 │ │ 0 │
view │ SummaryCGY │ 7 │ CumeActive │ │ 0 │ │ 0 │
view │ SummaryCGY │ 8 │ CumeRecovered │ │ 0 │ │ 0 │
view │ SummaryCGY │ 9 │ CumeDied │ │ 0 │ │ 0 │
view │ SummaryEDM │ 0 │ date │ │ 0 │ │ 0 │
view │ SummaryEDM │ 1 │ active │ │ 0 │ │ 0 │
view │ SummaryEDM │ 2 │ recovered │ │ 0 │ │ 0 │
view │ SummaryEDM │ 3 │ died │ │ 0 │ │ 0 │
view │ SummaryEDM │ 4 │ RollActive │ │ 0 │ │ 0 │
view │ SummaryEDM │ 5 │ RollRecovered │ │ 0 │ │ 0 │
view │ SummaryEDM │ 6 │ RollDied │ │ 0 │ │ 0 │
view │ SummaryEDM │ 7 │ CumeActive │ │ 0 │ │ 0 │
view │ SummaryEDM │ 8 │ CumeRecovered │ │ 0 │ │ 0 │
view │ SummaryEDM │ 9 │ CumeDied │ │ 0 │ │ 0 │
table │ sqlite_stat1 │ 0 │ tbl │ │ 0 │ │ 0 │
table │ sqlite_stat1 │ 1 │ idx │ │ 0 │ │ 0 │
table │ sqlite_stat1 │ 2 │ stat │ │ 0 │ │ 0 │
table │ sqlite_stat4 │ 0 │ tbl │ │ 0 │ │ 0 │
table │ sqlite_stat4 │ 1 │ idx │ │ 0 │ │ 0 │
table │ sqlite_stat4 │ 2 │ neq │ │ 0 │ │ 0 │
table │ sqlite_stat4 │ 3 │ nlt │ │ 0 │ │ 0 │
table │ sqlite_stat4 │ 4 │ ndlt │ │ 0 │ │ 0 │
table │ sqlite_stat4 │ 5 │ sample │ │ 0 │ │ 0 │
─────────────┴──────────────┴────────┴───────────────┴──────────┴───────────┴─────────────────┴─────────────────┘
sqlite>
Have you tried changing "inner join" to "cross join" to see if that makes a difference?
(3) By RB Smissaert (RBSmissaert) on 2020-12-02 20:26:14 in reply to 2.1 [link] [source]
Problem solved. There were 3 views referring to a table that doesn't exist anymore. Dropping those views solved the problem. Just need to fix the bug that caused the SQLite error not to show up.
RBS