Query with temperorary tables?


ik have the following tables





An order can have more than one orderline and more than one chargeorderline. You can store a text at orderlevel, orderlinelevel and at chargelinelevel (is not mandatory). I want to create a report which can show all these 3 text per order (and only shows an order when a text is available). I tried it with the following query which put the text for the orderlines and the text for the chargeorderlines in a temperorary table first. But this doesn't seem to work. Does anyone has an idea

Posted On: Monday 19th of November 2012 11:50:26 PM Total Views:  104
View Complete with Replies

Related Messages:

multiple selections from a listbox into a query?   (103 Views)
Originally posted by : geniene ( have two list boxes that I have populated and two text boxes. The user has the option to select from both list boxes and enter two dates to query a sql database and display a resultset on another asp page.It works as-is, but I want to offer the capability to make more than one selection in the list box. I have the list box multiple property set, but can't figure out how to formulate the code to grasp more than one selection
Desc query in Oracle   (83 Views)
Originally posted by : klchan ( tb_name is a query statementuse in Oracle to get column name, datatype,etc.Anybody know why using this query in ASP isnot workin
Is it possible to use a variable in the SQL query?   (66 Views)
Originally posted by : Chrisann ( want to use a procedure in ASP which passes a variable into the query, therefore saving precious coding SELECT * FROM table WHERE field LIKE 'variable%'I don't even know if this is possible, so any help will be useful. Cheers!Chrisann
Initiating a query on each "OnChange" of a combo box.   (55 Views)
Originally posted by : Shai ( want on each "OnChange" to initiate a query
Sql query statement help   (116 Views)
In access, I have a query ("today's sales summary") which summarizes the total sales of 5 product categories for that given day. The data is being pulled from "table a". I need to add to this, I guess maybe through an SQL statement. The current one line summary displays as follows: total prod a - total prod b - total prod c - total prod d - total prod e. Underneath those field names are the total unit sales for each of those categories. What SQL language would be best to add the following 2 rows of info: The average selling price for each product category which is a fixed amount located in a separate table ("table b"). Then I need another row inserted to calculate total unit sales for each category multiplied by their average selling prices. Any suggestions on how to make this work would be greatly appreciated.
Multiple price and currency query   (88 Views)
I'm using MYSQL database with vbscript. i need help to create my sql query Products Table --------------------------------- id item price currency --------------------------------- 1 item1 100 USD 2 item2 125 EURO 3 item3 80 EURO 4 item4 90 USD 5 item5 50 USD 6 item6 130 USD 7 item7 180 USD 8 item8 130 EURO 9 item9 90 GBP 10 item10 120 GBP search form data: price_from = request("price_from") price_to = request("price_to") search_Currency = request("currency") For example, search items between 70 - 125 in currency 'Euro'. In that case result must be listed as below, exchange rates : eurusd = 1,26 gbpeur = 1,12 item2 125 EURO item9 112 EURO item6 108 EURO item3 80 EURO item1 79 EURO item4 71 EURO I used following sql but it doesn't work. ------------------------------------------ if search_currency = "EUR" then sql = Select * from Products where currency = 'USD' and (price / "&eurusd&" > "&price_from&" and price / "&eurusd&" < "&price_to&" ) " sql = sql & " and currency = 'EUR' and (price > "&price_from&" and price < "&price_to&" ) " sql = sql & " and currency = 'GBP' and (price * "&gbpeur&" > "&price_from&" and price * "&gbpeur&" < "&price_to&" ) " end if Thank you
Extract query   (99 Views)
other then the getString method is there other way to extract data out from the table like get by column.. i am using oleDb connection by the way
Complix sql query   (97 Views)
hi, i have a basic knowledge about SQL and i have to create a complex query, first i have a table (Equipment) contains Fields Equipment_Name Equipment_id the second table Circuit_link Circuit_name(this Field is not unique) From_Equipment_name To_Equipment_name finally i have a variable that contains CIRCUIT_NAME i want to get Equipment_id from Equipment table where Equipment_Name = From_Equipment_name or Equipment_Name =To_Equipment_name and CIRCUIT_NAME = &variable !! i want to use this SQL statement in asp page.
Add COUNT function to query   (64 Views)
I have a query that I need to add a little more functionality to. First of all, I have a website that tracks live show setlists for a band. This query looks for songs that were played at one point over the last two years (in this case 2004 and 2005) and sees if they were played this year. This query returns the results of songs that were played previously, but have not been played this year. I want this query to now give me songs that were played at least twice in one of the previous years (as in twice in 2004 or twice in 2005) and then see if that song was played this year. There are a lot of songs that the band may play once for a special occassion (i.e. Prince's 1999 for NYE 1999) and then never play again. I want those to be exceptions and not included in my query. How can I add that COUNT function to this query Code: SELECT DISTINCT showsongs.song_id, s_name FROM shows, showsongs, song, album WHERE shows.show_id = showsongs.show_id AND song.song_id = showsongs.song_id AND album.album_id = song.album_id AND year( date ) =2005 AND song.album_id !=26 AND showsongs.song_id NOT IN ( SELECT showsongs.song_id FROM shows, showsongs, song WHERE shows.show_id = showsongs.show_id AND song.song_id = showsongs.song_id AND year( date ) =2006 ) UNION SELECT DISTINCT showsongs.song_id, s_name FROM shows, showsongs, song, album WHERE shows.show_id = showsongs.show_id AND song.song_id = showsongs.song_id AND album.album_id = song.album_id AND year( date ) =2004 AND song.album_id !=26 AND showsongs.song_id NOT IN ( SELECT showsongs.song_id FROM shows, showsongs, song WHERE shows.show_id = showsongs.show_id AND song.song_id = showsongs.song_id AND year( date ) =2006 ) ORDER BY s_name; (This is the generated query, not the actual code with the variables.)
Slow query   (70 Views)
I am running this query on a MySQL database and feeding it back to a .aspx page. SELECT lot, productid, granprocesstime, granerh, granyieldprod, granyieldrecon, dryoutletairtemp, Qacheck,id FROM aprdata WHERE id IN (SELECT Max( FROM aprdata WHERE Deleted = ' ' and lot = 1060001) My problem is it seems to run instantly (
Same SQL query but different order   (80 Views)
I have to use 9 different SQL and 7 of them could possibly be the same but in different order. Do I really have to create 9 SQL connections or if there's another way to group all of them in ONE SQL query and change the order by another way SQL="SELECT * FROM Stats ORDER BY Change DESC" SQL2="SELECT * FROM Stats ORDER BY Today DESC" SQL3="SELECT * FROM Stats ORDER BY Record DESC" SQL4="SELECT * FROM Stats ORDER BY Worst" SQL5="SELECT * FROM Stats WHERE RecoBeat = 'Y' " SQL6="SELECT * FROM Stats WHERE WorsBeat = 'Y' " SQL7="SELECT * FROM Stats ORDER BY Nick" SQL8="SELECT * FROM Stats ORDER BY DifKL DESC" SQL9="SELECT * FROM Stats ORDER BY BT DESC"
user based query   (79 Views)
Greetings I have a problem regarding giving the users the selection of selecting the fields and the type of query so that they can view the flexgrid report. Please help me
sql query   (74 Views)
i need an sql query to take 2 columns from a table. the condition is i want to select only those values for theses columns which are repeated in the table. for example table 1 contains the following data: rec_no col1 col2 col3 1 sat 1 val1 2 mon 6 val2 3 sat 1 val3 4 mon 7 val4 5 tues 2 val5 6 mon 6 val6 7 sat 1 val7 here i want to select col1 and col2. the records which satisfy my criterea are sat 1 (this pair of values is repeated in rec_no 1, 3 and 7) mon 6 (this pair of values is repeated in rec_no 2,6) since both these values are repeated more than once in the table.
query and subqueries   (106 Views)
I have 3 tables like the following ones: EMP_TABLE emp_id name city HIST_TABLE emp_id prof_category date days_contract CAT_TABLE prof_category_id description I'd like a query to know the emp_id, name, prof_category and final date of the contract(date+days_contract) of each employee at the Max(date) of end of contract
Need a subquery?   (250 Views)
I am try to create SQL to get a specific result. I have three tables that are linked. Account Table ROW_ID AccntName BU_ID Inventory Table AccntID ProductID ExpMonth ExpYear Product Table ROW_ID ProductName SELECT "Account"."BU_ID", "Account"."AccntName", "Inventory"."ExpMonth", "Inventory"."ExpYear", "Product"."ProdName" FROM "SIAM"."Account" "Account", "SIAM"."Inventory" "Inventory", "SIAM"."Product" "Product" WHERE "Account"."ROW_ID" = "Inventory"."AccntID" AND "Inventory"."ProductID" = "Product"."ROW_ID" AND "Inventory"."ExpMonth" = 12. AND "Inventory"."ExpYear" = 2005. AND "Account"."BU_ID" = '1-O6S0' AND ("Product"."NAME" = 'Peanut Butter' OR "Product"."NAME" = 'Grape Jelly' OR "Product"."NAME" = 'Wheat Bread') ORDER BY "Account"."NAME" ASC With this I get all products for each account that have an expiration date of 12/2005 but what I want is the Accounts that have Peanut Butter or Grape Jelly that expire on 12/2005 and also have Wheat Bread that may or may not expire on 12/2005. To state it another way any account that has Wheat Bread that expires on 3/2005 and Peanut Butter expires on 12/2005 or another account may have Wheat Bread that expires 2/2005 and Grape Jelly expires on 12/2005. I have tried a subquery but have not been successful in getting the result I need. Any suggestions on how to do this in SQL Rickie
Updateable query error   (106 Views)
I get this error: operation must use an updateable query (line 43) on this code: ... 'Create the INSERT statement for a brand new client. SQLINSERT="INSERT INTO Clients (fname, lname, email, carID, tranID, gradeID) " SQLINSERT=SQLINSERT & "VALUES (" SQLINSERT=SQLINSERT & "'" & fname & "', " SQLINSERT=SQLINSERT & "'" & lname & "', " SQLINSERT=SQLINSERT & "'" & email & "', " SQLINSERT=SQLINSERT & carID & ", " SQLINSERT=SQLINSERT & tranID & ", " SQLINSERT=SQLINSERT & gradeID & ") " set connupdate = server.createobject("ADODB.Connection") "DNS=parking" connupdate.execute(SQLINSERT) ... Where line 43 is: connupdate.execute(SQLINSERT) any clues Clients is the table name, parking is the odbc datasource name, etc.
Upsizing from Access to SQL Server (query strings)   (76 Views)
Originally posted by : Dave Stewart ( have two ASP apps currently running with Access data sources but I need to upsize the data to SQL server.Is there a script or easy way to convert my access sql strings for ADO to the proper syntax for SQL Server or do I have to just manually edit each of the hundreds of string
Split a query result onto several pages   (117 Views)
Originally posted by : Tim Russell ( have seen this done loads of places but I'm not sure of the code (being a complete newbie )I have a page hit tracker that logs hits to a database. I also have a webpage to view the results This page works but it retrieves all of the entries on to one page. What is the easiest way to split this down across several pages, does anyone have any code I can read (No not just copy and use I actually want to understand what I'm doing!).Ta muchoTim
Sql query help/problem   (143 Views)
Hi all, hope you can help, I have a problem with an SQL command, and hope you can help me out. Kinda new to SQL so finding the answer difficult to find, but I think it's to do with an UPDATE statement (at a guess) I have a function that executes a stored procedure (spUpdateStoreDetails) that updates a table, but when i run the procedure only one of the two columns gets updated (fk_windowID). I need both the fk_window and fk_floorplans to get updated here is the query CREATE PROCEDURE [dbo].[spUpdateStoreDetails] @iStoreID DECIMAL ,@sOpensMonday VARCHAR(8) ,@sClosesMonday VARCHAR(8) ,@sOpensTuesday VARCHAR(8) ,@sClosesTuesday VARCHAR(8) ,@sOpensWednesday VARCHAR(8) ,@sClosesWednesday VARCHAR(8) ,@sOpensThursday VARCHAR(8) ,@sClosesThursday VARCHAR(8) ,@sOpensFriday VARCHAR(8) ,@sClosesFriday VARCHAR(8) ,@sOpensSaturday VARCHAR(8) ,@sClosesSaturday VARCHAR(8) ,@sOpensSunday VARCHAR(8) ,@sClosesSunday VARCHAR(8) ,@sTelNumber VARCHAR(50) ,@sFaxNumber VARCHAR(50) ,@sEmailAddress VARCHAR(250) ,@sSocialProfileAddress VARCHAR(250) ,@sPostalAddress VARCHAR(500) ,@iWindowID DECIMAL ,@ifloorID DECIMAL AS BEGIN SET NOCOUNT ON IF EXISTS (SELECT fk_storeID FROM tblStoreDetails WHERE fk_storeID = @iStoreID) BEGIN UPDATE tblStoreDetails SET OpensMonday = @sOpensMonday ,ClosesMonday = @sClosesMonday ,OpensTuesday = @sOpensTuesday ,ClosesTuesday = @sClosesTuesday ,OpensWednesday = @sOpensWednesday ,ClosesWednesday = @sClosesWednesday ,OpensThursday = @sOpensThursday ,ClosesThursday = @sClosesThursday ,OpensFriday = @sOpensFriday ,ClosesFriday = @sClosesFriday ,OpensSaturday = @sOpensSaturday ,ClosesSaturday = @sClosesSaturday ,OpensSunday = @sOpensSunday ,ClosesSunday = @sClosesSunday ,TelNumber = @sTelNumber ,FaxNumber = @sFaxNumber ,EmailAddress = @sEmailAddress ,SocialProfileAddress = @sSocialProfileAddress ,PostalAddress = @sPostalAddress ,fk_windowID = @iWindowID ,fk_floorplansID = @ifloorID WHERE fk_StoreID = @iStoreID END ELSE BEGIN INSERT INTO tblStoreDetails ( fk_StoreID ,OpensMonday ,ClosesMonday ,OpensTuesday ,ClosesTuesday ,OpensWednesday ,ClosesWednesday ,OpensThursday ,ClosesThursday ,OpensFriday ,ClosesFriday ,OpensSaturday ,ClosesSaturday ,OpensSunday ,ClosesSunday ,TelNumber ,FaxNumber ,EmailAddress ,SocialProfileAddress ,PostalAddress ,fk_windowID ,fk_floorplansID ) VALUES ( @iStoreID ,@sOpensMonday ,@sClosesMonday ,@sOpensTuesday ,@sClosesTuesday ,@sOpensWednesday ,@sClosesWednesday ,@sOpensThursday ,@sClosesThursday ,@sOpensFriday ,@sClosesFriday ,@sOpensSaturday ,@sClosesSaturday ,@sOpensSunday ,@sClosesSunday ,@sTelNumber ,@sFaxNumber ,@sEmailAddress ,@sSocialProfileAddress ,@sPostalAddress ,@iWindowID ,@ifloorID ) END END Please can someone have a look at this and tell me if there is a problem with this query, or could if it could be somehting else if you need more information please shout. kind regards MG
Need some help with a query asap please   (152 Views)
i have a temp table with data I want to insert records into the first table(tbl1) and use the ID field that is auto inc in tbl1 and insert some other data from tblTemp into tbl2 so i created a column to hold the identity from tbl1 in tblTemp named tbl1ID now I need to loop through the records inserting them and get the scope_identity of each record and insert that into the tblTemp.tbl1ID column help please how would I put this together Code: INSERT INTO tbl1 ( col1,col2 ) select col1,col2 FROM tblTemp and now i need to for each record INSERT INTO tblTemp SELECT @tbl1ID = SCOPE_IDENTITY() FROM tbl1