Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Help

Author

Topic

PeeJay2583
Starting Member

40 Posts

Posted - 2011-06-06 : 13:26:07

I have an SQL specific query from Access database which i wanted to convert into SQL Server as we have switched to SQL recently however I am unable to work around couple of things.

Access Query looks like below:

Select Format(date,"ww") as date_new,table1.detected_date,table1.id,table2.Cause,IIf(NVA_TYPE IS NULL,"N",NVA_TYPE) AS NVA Determination,table1.Period
From table1 left join table2 on table1.rootcause= table2.cause where table1.detected_date > #12/31/2009#.

Can anyone help in converting this query to fit SQL Server by changing it as I know IIF doesn't work for sql neither double quotes however there must be a work around.

Please let me know if there is any.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-06-06 : 13:57:13

1)For the IIF - look at the CASE expression
2)For the date format - I would output the date as raw -and format at the presentation/application layer
If you post the DDL of the tables and some sample data , this would assist

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-06 : 22:23:59

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

What you posted has vague generic names like “id” of nothing in particular. You need to re-do the DDL first. Here is a quick re-write:

SELECT Table1.new_something_date, Table1.detection_date,
Table1.generic_vague_id, Table2.something_cause,
COALESCE (nva_type, 'N') AS nva_type,
Table1.something_period
FROM Table1
LEFT OUTER JOIN
Table2
ON Table1.root_something_cause = Table2.something_cause
WHERE Table1.detection_date > '2009-12-31';

Why are something_causes split across two tables? Why is the nva code allowed to be NULL instead of defaulting to a missing value?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources