What would be the equivalent in SQL??
-
I am having to build queries on a new database through an ODBC (our company just bought new POS software) to do reports. One of the DBA's at the company showed me how to access some of the data in MS Access. I am trying to do the equivalent in SQL (We are using MS SQL 2000 and do not need an upgrade for what we use it to do). The first query is this:
SELECT TICKET.[TICKET-STORE], TICKET.[TICKET-ACCT-MANAGER], TICKET.[TICKET-TYPE], TICKET.[TICKET-CONTRACT-DATE], TICKET.[TICKET-NEXT-DUE-DATE], TICKET.[TICKET-LAST-PAID-DATE], DateDiff('d',Now()-1,[TICKET-NEXT-DUE-DATE]) AS Expr1, TICKET.[TICKET-TICKET-NBR]
FROM TICKET
WHERE (((TICKET.[TICKET-TYPE])="O"));Which is named qryactive. Then all of the other queries select from qryactive. My question is - Is there any more efficient way in MSSQL other than building a table out of that query then selecting from it? The second is the use of TRANSFORM in MS Access. There is not an equivalent in MSSQL 2000. I read a few articles on how to possibly do it, but I get lost. Could someone point me in the right direction?
Jude
-
I am having to build queries on a new database through an ODBC (our company just bought new POS software) to do reports. One of the DBA's at the company showed me how to access some of the data in MS Access. I am trying to do the equivalent in SQL (We are using MS SQL 2000 and do not need an upgrade for what we use it to do). The first query is this:
SELECT TICKET.[TICKET-STORE], TICKET.[TICKET-ACCT-MANAGER], TICKET.[TICKET-TYPE], TICKET.[TICKET-CONTRACT-DATE], TICKET.[TICKET-NEXT-DUE-DATE], TICKET.[TICKET-LAST-PAID-DATE], DateDiff('d',Now()-1,[TICKET-NEXT-DUE-DATE]) AS Expr1, TICKET.[TICKET-TICKET-NBR]
FROM TICKET
WHERE (((TICKET.[TICKET-TYPE])="O"));Which is named qryactive. Then all of the other queries select from qryactive. My question is - Is there any more efficient way in MSSQL other than building a table out of that query then selecting from it? The second is the use of TRANSFORM in MS Access. There is not an equivalent in MSSQL 2000. I read a few articles on how to possibly do it, but I get lost. Could someone point me in the right direction?
Jude
- A more efficient way to handle the query based on the results of another query is to build a MS-SQL view. In your case I belive the view would be equal to "qryactive". The other queries would reference this view just like a table in the "from" clause. 2) I believe the equivalent to TRANSFORM would be PIVOT in SQL-Server. However, I'm not 100% sure that PIVOT is available in SQL 2000.
-
- A more efficient way to handle the query based on the results of another query is to build a MS-SQL view. In your case I belive the view would be equal to "qryactive". The other queries would reference this view just like a table in the "from" clause. 2) I believe the equivalent to TRANSFORM would be PIVOT in SQL-Server. However, I'm not 100% sure that PIVOT is available in SQL 2000.
Yes you are right. View is to rescue and PIVOT is available since SQL SERVER version 2005. In 2000, Pivoting is archived via CASE statements e.g. http://jdixon.dotnetdevelopersjournal.com/pivot_table_data_in_sql_server_2000_and_2005.htm[^] :)
Niladri Biswas
-
Yes you are right. View is to rescue and PIVOT is available since SQL SERVER version 2005. In 2000, Pivoting is archived via CASE statements e.g. http://jdixon.dotnetdevelopersjournal.com/pivot_table_data_in_sql_server_2000_and_2005.htm[^] :)
Niladri Biswas
Thanx a bunch!
Jude