Microsoft Database Query Engines and Literals

Posted By: theogray_com | Mar 4th, 2007 @ 2:57 AM
page 1 of 1
Comments: 2 | Views: 1543
#Mar 4th, 2007 @ 2:57 AM
theogray_com
theogray_com
Madness is all in the mind

There appears to be something up with Microsoft's database engines, and I can't easily find anything on Google about it so thought I'd stick a post together and see if anyone else has found the same problem.

I have so far tried this against SQL Server 2000, Access 2003 and Access 2007, and all give the same result.

The idea: Add a literal string field to a resultset, based on the setting of a boolean flag in the data....

The setup:

CREATE TABLE [TABLE1] (
  [Description] [nvarchar] (255) NULL ,
  [Flag] [bit] NOT NULL
)

CREATE VIEW Q_FLAGSET AS
  SELECT Description, 'Extra Info to display, ONLY when flag set' AS Result
  FROM TABLE1
  WHERE (Flag <> 0)

CREATE VIEW Q_FLAG_RESULT AS
  SELECT TABLE1.*, Q_FLAGSET.Result
  FROM TABLE1 LEFT OUTER JOIN Q_FLAGSET ON TABLE1.Description = Q_FLAGSET.Description

INSERT INTO TABLE1 ([Description],[Flag]) VALUES ('Flag Is Set',1)
INSERT INTO TABLE1 ([Description],[Flag]) VALUES ('Flag Is Not Set',0)

The Expected result from Q_FLAG_RESULT:

[Description], [Flag], [Result]
'Flag Is Set', 1, 'Extra Info to display, ONLY when flag set'
'Flag Is Not Set', 0, <NULL>

The Actual result from Q_FLAG_RESULT:

[Description], [Flag], [Result]
'Flag Is Set', 1, 'Extra Info to display, ONLY when flag set'
'Flag Is Not Set', 0, 'Extra Info to display, ONLY when flag set'

If you also include Q_FLAGSET.Description AS FlagSetDescription as part of the returned fields of Q_FLAG_RESULT, that field returns <NULL> as expected, which leads me to the workaround I have ended up using...

I added in a third query which returns the string liternal, so that Q_FLAGSET appears (to the query engines) to be returning data from a field, i.e.

CREATE Q_RETURN_LITERAL AS
  SELECT DISTINCT TABLE1.Flag, 'Extra Info to display, ONLY when flag set' AS Result
  FROM TABLE1
  WHERE (Flag<>0)

CREATE Q_FLAGSET AS
  SELECT TABLE1.Description, Q_RETURN_LITERAL.Result
  FROM TABLE1 INNER JOIN Q_RETURN_LITERAL ON TABLE1.Flag = Q_RETURN_LITERAL.Flag
  WHERE (TABLE1.Flag<>0)

Soooo.... am I crazy to think the first setup should have worked? And is this "feature" of SQL Server and Access documented anywhere?

#Mar 4th, 2007 @ 8:42 PM
kidzi
kidzi
Your joins are a bit off.
Add the Flag to return from the view.

Then in your Q_FLAG_RESULT, join the Q_FLAGSET on the flag value instead of the description (table1.Flag = Q_FLAGSET.Flag)

Then when you join, it will join on the flag, which will be 0, and filtered out returning null.

Otherwise, the select from table1 will always return one row since there is one entry <> 0, so a left outer join on that table with the description will never be null. 



#Mar 5th, 2007 @ 7:38 AM
theogray_com
theogray_com
Madness is all in the mind

Thanks kidzi, I guess I did overthink things originally, and using Flag directly (i.e. cutting out Q_FLAGSET completely and just joining Q_RETURN_LITERAL) does work as expected.

But the issue with what is being returned from my (bad!) example still remains. If you change Q_FLAG_RESULT to include Q_FLAGSET.Description, ie:

SELECT TABLE1.*, Q_FLAGSET.Result, Q_FLAGSET.Description AS FlagsetDescription
FROM TABLE1 LEFT OUTER JOIN Q_FLAGSET ON TABLE1.Description = Q_FLAGSET.Description

Then FlagsetDescription returns the expected <NULL>, but Result still returns the literal even though there is no joined record to return from:

[Description], [Flag], [Result], [FlagsetDescription]
'Flag Is Set', 1, 'Extra Info to display, ONLY when flag set', 'Flag Is Set'
'Flag Is Not Set', 0, 'Extra Info to display, ONLY when flag set', <NULL>

page 1 of 1
Comments: 2 | Views: 1543