Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Converting to ANSI SQL

Posted on 2011-05-12
8
Medium Priority
?
442 Views
Last Modified: 2012-05-11
Trying to convert this sql statement to ansi.  I tried to translate the left joins, but when comparing it to the original query, the orginial is pulling up more records than this ANSI one.  

Need help... Thanks...   What am I doing wrong?  Or need to try???  Thanks....

Old query

     begin
SELECT q.categoryID,q.questionID,q.questionNumber,c.choice, c.choice, CASE WHEN a.choice is null THEN 0 ELSE 1 END as isSelected, CASE WHEN a.choicePoint is null THEN 0 ELSE a.choicePoint END as choicePoint
FROM MP_ORMWorksheetAnswer a, MP_ORMTemplateChoice c, MP_ORMTemplateQuestion q           
WHERE q.templateID = @templateID
AND   a.ORMID = @ORMID
AND   a.revNo = @revNo
AND   q.questionID=c.questionID
AND   q.questionID*=a.questionID
AND   c.choice*=a.choice
ORDER BY q.categoryID,q.questionNumber,q.questionID,c.choice

New one (ANSI)...
SELECT q.categoryID,q.questionID,q.questionNumber,c.choice, c.choiceText,
                        CASE WHEN a.choice is null THEN 0 ELSE 1 END as isSelected,
                    CASE WHEN a.choicePoint is null THEN 0 ELSE a.choicePoint END as choicePoint
            FROM MP_ORMTemplateQuestion q           
            INNER JOIN MP_ORMTemplateQuestion q MP_ORMTemplateChoice c
                  ON q.questionID=c.questionID
            LEFT OUTER JOIN MP_ORMWorksheetAnswer a
                  ON q.questionID=a.questionID AND
                        c.choice=a.choice
            WHERE q.templateID = @templateID
            AND   a.ORMID = @ORMID
            AND   a.revNo = @revNo
            ORDER BY q.categoryID,q.questionNumber,q.questionID,c.choice



0
Comment
Question by:StrongD1
  • 4
  • 3
8 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 35747797
Your filters on a.ORMID = @ORMID AND a.revNo = @revNo in the WHERE clause makes the LEFT OUTER JOIN on table 'a' act like an INNER JOIN.  Move those conditions to the JOIN's ON clause and you should see the issue resolved.
0
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 35747822
To illustrate, see:
SELECT q.categoryID,q.questionID,q.questionNumber,c.choice, c.choiceText,
                        CASE WHEN a.choice is null THEN 0 ELSE 1 END as isSelected,
                    CASE WHEN a.choicePoint is null THEN 0 ELSE a.choicePoint END as choicePoint 
            FROM MP_ORMTemplateQuestion q           
            INNER JOIN MP_ORMTemplateQuestion q MP_ORMTemplateChoice c
                  ON q.questionID=c.questionID
            LEFT OUTER JOIN MP_ORMWorksheetAnswer a
                  ON q.questionID=a.questionID AND
                        c.choice=a.choice AND
                        a.ORMID = @ORMID AND
                        a.revNo = @revNo
            WHERE q.templateID = @templateID
            ORDER BY q.categoryID,q.questionNumber,q.questionID,c.choice

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35747902
Can you try this.
SELECT q.categoryID, 
       q.questionID, 
       q.questionNumber, 
       c.choice, 
       c.choice, 
       CASE 
         WHEN a.choice IS NULL THEN 0 
         ELSE 1 
       END AS isSelected, 
       CASE 
         WHEN a.choicePoint IS NULL THEN 0 
         ELSE a.choicePoint 
       END AS choicePoint 
  FROM MP_ORMWorksheetAnswer a 
       INNER JOIN MP_ORMTemplateQuestion q 
         ON q.questionID = a.questionID 
       LEFT JOIN MP_ORMTemplateChoice c 
         ON c.choice = a.choice 
            AND q.questionID = c.questionID 
 WHERE q.templateID = @templateID 
       AND a.ORMID = @ORMID 
       AND a.revNo = @revNo 
 ORDER BY q.categoryID, 
          q.questionNumber, 
          q.questionID, 
          c.choice  

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:StrongD1
ID: 35747981
Thanks guys... I will try them both, let you know the results...


0
 
LVL 1

Author Comment

by:StrongD1
ID: 35748100
Sharath_123

Your query is doing exact what mine did.   It returns less records....  

0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 35748247
I am pretty certain what you are missing are the rows where there is no matching record in 'a', but you can verify by using EXCEPT.  The missing rows, if because of NULL 'a' values, should be 0, 0 for the last two columns.

-----
SELECT q.categoryID,q.questionID,q.questionNumber,c.choice, c.choice, CASE WHEN a.choice is null THEN 0 ELSE 1 END as isSelected, CASE WHEN a.choicePoint is null THEN 0 ELSE a.choicePoint END as choicePoint
FROM MP_ORMWorksheetAnswer a, MP_ORMTemplateChoice c, MP_ORMTemplateQuestion q            
WHERE q.templateID = @templateID
AND   a.ORMID = @ORMID
AND   a.revNo = @revNo
AND   q.questionID=c.questionID
AND   q.questionID*=a.questionID
AND   c.choice*=a.choice

EXCEPT -- finds rows in the top query not in the bottom one

SELECT q.categoryID,q.questionID,q.questionNumber,c.choice, c.choiceText,
                        CASE WHEN a.choice is null THEN 0 ELSE 1 END as isSelected,
                    CASE WHEN a.choicePoint is null THEN 0 ELSE a.choicePoint END as choicePoint
            FROM MP_ORMTemplateQuestion q          
            INNER JOIN MP_ORMTemplateQuestion q MP_ORMTemplateChoice c
                  ON q.questionID=c.questionID
            LEFT OUTER JOIN MP_ORMWorksheetAnswer a
                  ON q.questionID=a.questionID AND
                        c.choice=a.choice
            WHERE q.templateID = @templateID
            AND   a.ORMID = @ORMID
            AND   a.revNo = @revNo
-- order by goes at the END and acts on final selection
ORDER BY categoryID, questionNumber, questionID, choice
-----
0
 
LVL 1

Author Closing Comment

by:StrongD1
ID: 35748270
Excellent!  You saved me from pulling my hair out.   I have several more queries to convert, but I will take what I learned from you and apply it.

If I can't figure it out, I will post another query...  Hopefully you will respond again.  

Thanks again.
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 35749970
You are most welcome and good luck!

Best regards and happy coding,
Kevin
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question