Re: SQL Question

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 08/23/04

  • Next message: Anith Sen: "Re: anyone can give solution for this problem" 
    Date: Mon, 23 Aug 2004 20:55:04 +0530

    >>
    WITH temp (var1 var2) as
    (
    select field1, field2
    from table1
    )
    <<

    above syntax's equivalent in sql server is:

    select field1 as var1,field2 as var2 into temp from table1

    --the query remain the same :
    select tb1.field2, tb2.var2
    from
    temp as tb1,
    table2 as tb2
    where
    tb1.var1 = tb2.field1

    --using ansi syntax you can have query as:

    select tb1.var2, tb2.var2
    from temp as tb1 join table2 as tb2
    on tb1.var1 = tb2.field1

    --without creating the temporary table you can make use of SELECT statement
    as an inline view as follows:

    select tb1.field2, tb2.var2
    from (select field1,field2 from table1) as tb1 join table2 as tb2
    on tb1.field1 = tb2.field1 

    -- 
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com
  • Next message: Anith Sen: "Re: anyone can give solution for this problem"

    Relevant Pages

    • Re: Truncation Issue w/ Strings
      ... I was getting the results from an access query, so I ended up having to ... create a temp table, and insert the rows into a memo field in the temp ... "fdhsjak hfdjksh fdshfjkdlsh fjdshfj hdslfh dshfjlshjfhdsjflhdsjlfh ... dsjlkhfjdshfjdha jfdhj f " & _ ...
      (microsoft.public.vb.general.discussion)
    • Re: query unique count for field
      ... I am not looking for a 1 to n sequence across the whole ... Start with such a temp table empty of ... >> Having succeeded with Michel's advice, I now have a query with the correct ... >>> where table Iotas has a single field, iota, with values from 1 to nnn, ...
      (microsoft.public.access.queries)
    • Re: deleting columns in table
      ... Doug Steele, Microsoft Access MVP ... To delete the contents of the temp table, you need a Delete query: ...
      (microsoft.public.access.modulesdaovba)
    • Re: comparing a record in one table against a record in another using VBA
      ... , then the 2nd query ... copies everything in Temp to Main on the condition that the primary ... Then I've called these queries using VBA ... I have 3 tables storing information about people; Main, Temp and Dupl. ...
      (comp.databases.ms-access)
    • Re: How to Create Local Temporary Table
      ... hold a result set from one query to be used in another query. ... With the improvements in Oracle 9i and 10g and what Oracle has added to its SQL feature set, one can most often get by with just a single SQL statement, which is more efficient than writing data to a table and then reading it back again. ... I can see a developer storing data into a temp table on the first pass and then going through the result set for a second pass. ... Amongst other things PL/SQL provides BULK COLLECT INTO and FOR ALL to speed up processing of VARRAY. ...
      (comp.databases.oracle.server)