Simple Query Or Resultset Output
Feb 7, 2008
I'm learning ASP.net, transitioning from ColdFusion. So far so go... but there is 1 thing that I can't seem to find a simple answer to. How can I do a simple output of a query resultset to the screen or to a label?
In CF it would be simple like this:
<cfquery name="getSomeData" datasource="somedatasource">
SELECT somestuff
FROM sometable
WHERE something = something
</cfquery>
<cfoutput>
#getsomedata.somestuff#
</cfoutput>
FYI: using the queryname.column returns only the first row in the CF query result
I have seen examples with:
label.text = somevarname
which doesn't work for the query... but what about the query result?
thanks
View 2 Replies
ADVERTISEMENT
Nov 27, 2014
I need to return a resultset consisting of database errors from SQL Server stored procedure's CATCH CLAUSE but stuck with it. Do I need to use cursors to return resultset and if so, then what is the type declaration for the OUTPUT parameter in my .NET application? I tried 'Object' and 'Variant' but did not work.
I also tried the simple way just using a SELECT statement to return and it works with one stored procedure but not with another as thus in my CATCH CLAUSE:
Code:
while (@I <= @count)
begin
BEGIN TRY
-- delete all previous rows inserted in @customerRow for previous counts @I
delete from @customerRow
-- this is inserting the current row that we want to save in database
insert into @customerRow
[Code] .....
This does not work when select is made in the CATCH block, ie it returns no rows to my .NET application:
Code:
begin catch
IF @@TranCount > 0 or XACT_STATE()= -1 ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
SELECT @ErrorMessage = ERROR_MESSAGE();
[Code] ....
Just to summarize the code and where the problem is. The code that works uses a SELECT * from temporary table not in the CATCH block and this works while the one that uses the same SELECT * from temporary table inside the CATCH does not return anything! Strange. The Wrong one is used in another Stored procedure from the Right one btw so am wondering why the same code does not work in those two different situations.
View 3 Replies
View Related
Oct 29, 2014
I got some xml that is essentially an html table that I need to turn into a standard table resultset from a stored proc. If you take this xml and save it as html that is the desired resultset I am looking for. I realize the <td> tags repeat so I would just prefer 'col' + positional index for the col name. Keep in mind that <td> could be is 1 to n.
<table>
<tr>
<td>cell1</td>
<td>cell2</td>
<td>cell3</td>
[Code] .....
This is my attempt but I can't figure out how to get separate cols
declare @GridData xml = '<table><tr><td>cell1</td><td>cell2</td><td>cell3</td></tr><tr><td>cell4</td><td>cell5</td><td>cell6</td></tr><tr><td>cell7</td><td>cell8</td><td>cell8</td></tr></table>'
select T.C.value('.', 'nvarchar(max)')
from @GridData.nodes('//tr') T(C)
View 6 Replies
View Related
Oct 10, 2012
I am creating a simple SSRS table report through Report Builder. My dataset is looking for the stored procedure . When I execute the Stored procedure through SSMS I get resutset for certain parameters. I execute the dataset  (Store procedure) through query designer in dataset properties and I get results back. But when I try to run the report and see the preview, I do not get any results displayed. I been looking on the same issue form last 3-4 days and have not found any clue.
Following is the stored procedure I am using. Also I am passing multivalued parameter through report as well, and I am using spilt function to seperate the libraryid I am reading from parameter values. This works fine. I have similar kind of four other reports and with different stored procedure which exactly follow the same method , like multivalue parameters and other criteria are also very similar. All other reports works just fine.. This perticular report has issue for displying results, following is the stored procedure I am usingÂ
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
View 4 Replies
View Related
Mar 31, 2005
Hi
I have 2 Sql statements within a stored procedure.But second one crashes my app - I'm getting object not found
I need some help when writing several querys into the same sp, do I have to define some thing special?
thanks
View 5 Replies
View Related
Dec 25, 2006
While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.
For example,the output of the Aggregate Transformation may be 960216 ,but the
'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.
I'm sure the Group By of the Aggregate Transformation is right!
But ,when I set the "keyscale" property of the transformation,the results match!
In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.
Thanks for your advice.
View 2 Replies
View Related
Sep 25, 2006
Hi everyone,
I am new to T-SQL and am trying to do some simple database for fun. Here is my problem and hope you guys can drop me some hint or solution:
OS: Win XP pro
SQL Server 2005 Express
Tool: SQLCMD in command prompt
I created a database and then create a table. Then I tried : select * from contract_Info, it gave me this:
1> CREATE TABLE Contract_Info
2> (
3> Order_No varchar(50) NOT NULL ,
4> Company varchar(255),
5> Product_Name varchar(255) NOT NULL,
6> Discount_Rate varchar(50), --Discount_Rate (%) -> Discount_Rate
7> Status varchar(50) NOT NULL,
8> Quantity varchar(50) NOT NULL,
9> Remainder varchar(50),
10> Deleted bit
11> )
12>
14>
15>
16> INSERT INTO Contract_Info values ('00000', 'DEFAULT','DEFAULT','0','Cancelle
d','0','0',0)
17>
18> go
(1 rows affected)
1> select * from contract_Info
2> go
Order_No Company
Product_Name
Discount_Rate Status
Quantity Rema
inder Deleted
-------------------------------------------------- -----------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------------ -------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- -------------------------------------------------- --------------------------
------------------------ -------------------------------------------------- ----
---------------------------------------------- -------
00000 DEFAULT
DEFAULT
0 Cancelled
0 0
0
(1 rows affected)
1>
In SQL Express Management , the output looks perfectly fine
What is missing??
Thanks for your time
View 6 Replies
View Related
Feb 17, 2005
Hello everyone,
I have a stored procedure that supplies rows for a front-end DataGrid that allows custom paging. The stored procedure must return the requested "page" of rows as identified by a sproc argument. Currently, I'm loading the the query's result set into a temporary table that has an identity column as primary key. I then run a second query against the temp table that uses the identity column value to strip out the requested "page" of rows and return them to the front-end DataGrid.
I'd like to eliminate the temporary table. To do so I would need to create the equivalent of an identity column in the query's sorted results and reference this value in the WHERE clause in order to return only the requested rows.
Does anyone know of a way to generate a sequential number (starting at 1) within a query (and after the rows have been sorted by the ORDER BY)? I don't think this can be done but I put it out for those who may know better.
Thanks for your help. If you know this is impossible, that would be helpful feedback as well.
BlackCatBone
View 3 Replies
View Related
Apr 13, 2007
Hi All,
I am really in a great trouble. My requirement is quite complex, as I feel, it may be quite simple for some of you.
Here is my problem -
Actually I am doing a project, where client has a specific requirement. i.e. he want's to build a query on runtime for selecting particular record he wants, so that we have provided a user interface where he can select any datasource e.g. SQL, Oracle, Excel etc. He also specifies the database name. He is displayed all the tables and columns under those tables. He selects columns from those table boxes and write the query he wants, with where clause, if required.
I am saving these query in a table, storing column names in another table where we map those oringinal column names with columns of another table, wehre we want to store actual result set of the prepared query by the user.
for examaple ..
if user preapare query like - 'SELECT CUST_ID, CUST_NAME FROM CUSTOMER
WHERE CUST_ID = 10'
In case of above query I will store CUST_ID in column COL1 of table TAB1 and CUST_NAME in COL2 of table TAB1, like that we have such fifty columns in that table. Now question is here every thing is dynamic data provider, database, tables, columns and where clause, then how can get the result set out of those queries and store that query output in the that storage table with columns col1, col2 and so on, upto 50 columns.
Please help me on this, as it is so urgent.
I will be very much thankful to you people.
Thanks & regards,
Praveen Kadam
View 3 Replies
View Related
Sep 20, 2007
Hello!
I want to make a very simple package: Export all rows in a table to a flat file.
This package I can create pretty much by only using the wizards.
Now to my problems:
1) I need the output to have this format:
H20070920161522
DS3 Plastpall trippelkrage 40 1
E00000000003
H is a header post, in this case with date and time following.
D is a details post, that is all the rows that was exported.
E is and end post, containing only the number of rows in the file, including H and E posts.
2) I need to set the file name dynamically, preferably using date and time to name the file.
I´ve done this very same thing in T-SQL, like so:
Code Snippet
USE AVK
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
SELECT *
FROM tempProducts
GO
CREATE VIEW EXPORT_ORDERS
AS
SELECT 1 AS ROW_ORDER, 'H' + REPLACE(CONVERT(char(8), GETDATE(), 112) + CONVERT(char(8), GETDATE(), 108), ':', '') AS Data_Line
UNION ALL
SELECT 2 AS ROW_ORDER, 'D' + COALESCE (CONVERT(char(10), LBTyp), '') + COALESCE (CONVERT(char(50), Description), '') + COALESCE (CONVERT(char(5),
Volume), '') AS Data_Line
FROM dbo.tempProducts
UNION ALL
SELECT 3 AS ROW_ORDER, 'E' + RIGHT('0000000000' + RTRIM(CONVERT(char(13), COUNT(*) + 2)), 11) AS Data_Line
FROM dbo.tempProducts AS tempProducts_1
GO
IF @@ROWCOUNT > 0
BEGIN
BEGIN TRANSACTION
SELECT *
FROM tempProducts
DECLARE @date char(8)
DECLARE @time char(8)
DECLARE @sql VARCHAR(150)
SELECT @date = CONVERT(char(8), getdate(),112)
SELECT @time = CONVERT(char(8), getdate(),108)
SELECT @time = REPLACE(@time,':','')
DECLARE @dt char(14)
SELECT @dt = @date + '_' + @time
SELECT @sql = 'bcp "SELECT Data_Line FROM avk..EXPORT_ORDERS ORDER BY ROW_ORDER" queryout "c:AVK_' + @dt + '.txt" -c -t -U sa -P dalla'
EXEC master..xp_cmdshell @sql
--WAITFOR DELAY '0:00:10';
DELETE
FROM tempProducts
COMMIT TRANSACTION
END
DROP VIEW EXPORT_ORDERS
GO
But I´m sure it can be done in SSIS aswell, giving me some nice options for i.e. error handling aswell.
Pointers please
View 5 Replies
View Related
Jul 23, 2005
Hi!Server info -Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual MemorySQL Server 2000 Enterprise Edition + SP3 running on this.Required result -Create a SQL Script that will contain a set of create, update, insert& delete statements (about 17500 lines including blank lines) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI have increased the page size to 2.5 GB and restarted the server.Running the sp now generated the correct output a few times but gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @AttrRef int,@TestID int,@PrtTestRef int,@AttrType tinyint,@EdtblSw tinyint,@NmValRef int,@SrtTypeRef int,@AttrStr varchar(20),@TestStr varchar(20),@PrtTestStr varchar(20),@AttrTypeStr varchar(20),@EdtblStr varchar(20),@NmValStr varchar(20),@SrtTypeStr varchar(20),@TestRef int,@Seq int,@PrtRef int,@Value varchar(255),@TermDate datetime,@AttrID int,@DefSw tinyint,@WantSw tinyint,@TestRefStr varchar(20),@SeqStr varchar(20),@PrtStr varchar(20),@TermDateStr varchar(255),@AttrIDStr varchar(20),@DefStr varchar(20),@WantStr varchar(20),@LanRef int,@LanStr varchar(20),@Code varchar(20),@Desc varchar(255),@MultiCode varchar(20),@MultiDesc varchar(255),@InhSw tinyint,@InhStr varchar(20),@InhFrom int,@InhFromStr varchar(20),@Lan_TestRef int,@ActSw tinyint,@ActSwStr varchar(20)SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')INSERT INTO #tbl_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@Value = ISNULL(@Value, 'NULL'),@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')SELECT @Value = '''' + @Value + ''''WHERE @Value <> 'NULL'INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),@Code = ISNULL(@Code, 'NULL'),@Desc = ISNULL(@Desc, 'NULL'),@MultiCode = ISNULL(@MultiCode, 'NULL'),@MultiDesc = ISNULL(@MultiDesc, 'NULL'),@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')SELECT @Code = REPLACE(@Code, '''',''''''),@Desc = REPLACE(@Desc, '''','''''') ,@MultiCode = REPLACE(@MultiCode, '''','''''') ,@MultiDesc = REPLACE(@MultiDesc, '''','''''')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn
View 5 Replies
View Related
Jan 7, 2008
Hello,
I have the following query which grabs monthly usage data which is logged to a database table from a web page:
SELECT Button = CASE ButtonClicked
WHEN 1 THEN '1st Button'
WHEN 2 THEN '2nd Button'
WHEN 3 THEN '3rd Button'
WHEN 4 THEN '4th Button'
WHEN 5 THEN '5th Button'
WHEN 6 THEN '6th Button'
WHEN 7 THEN '7th Button'
WHEN 8 THEN '8th Button'
WHEN 9 THEN '9th Button'
ELSE 'TOTAL'
END,
COUNT(*) AS [Times Clicked]
FROM WebPageUsageLog (NOLOCK)
WHERE DateClicked BETWEEN @firstOfMonth AND @lastOfMonth
GROUP BY ButtonClicked WITH ROLLUP
ORDER BY ButtonClicked
The results look like this:
TOTAL 303
1st Button 53
2nd Button 177
3rd Button 10
4th Button 4
6th Button 18
7th Button 19
8th Button 21
9th Button 1
If a button is never clicked in a given month, it never gets logged to the table. In this example, the 5th button was not clicked during the month of December, so it does not appear in the results. I want to modify my query so it displays the name of the button and a zero (in this case "5th Button 0") in the results for any buttons that were not clicked. For some reason I am drawing a blank on how to do this. Thanks in advance.
-Dave
View 3 Replies
View Related
Aug 30, 2006
I've built a simple custom data flow transformation component following the Hands On Lab (http://www.microsoft.com/downloads/details.aspx?familyid=1C2A7DD2-3EC3-4641-9407-A5A337BEA7D3&displaylang=en) and the Books Online (ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/dtsref9/html/adc70cc5-f79c-4bb6-8387-f0f2cdfaad11.htm and ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/dtsref9/html/b694d21f-9919-402d-9192-666c6449b0b7.htm).
All it is supposed to do is create an output column and set its value to the result of calling a web service method (the transformation is synchronous). Everything seems fine, but when I run the data flow task that contains it, it doesn't generate any output. The Visual Studio debugger displays it as yellow, with 1,385 rows going into it, but the data viewer attached to its output is empty. The output metadata looks just like I expect: all of my input columns plus the new column, correctly typed. No validation or run-time warnings or errors are reported.
I'll include the entire C# file below, which only overrrides the ProvideComponentProperties, Validate, PreExecute, ProcessInput, and PostExecute methods of the parent PipelineComponent class.
Since this is effectively a specialization of the DerivedColumn transformation, could I inherit from the class that implements the DC component instead of PipelineComponent? How do I even find out what that class is?
Thanks! Here's the code:
using System;
// using System.Collections.Generic;
// using System.Text;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
namespace CustomComponents
{
[DtsPipelineComponent(DisplayName = "GID", ComponentType = ComponentType.Transform)]
public class GidComponent : PipelineComponent
{
///
/// Column indexes for faster processing.
///
private int[] inputColumnBufferIndex;
private int outputColumnBufferIndex;
///
/// The GID web service.
///
private GID.WS_PDF.PDFProcessService gidService = null;
///
/// Called to initialize/reset the component.
///
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
// Remove any existing metadata:
base.RemoveAllInputsOutputsAndCustomProperties();
// Create the input and the output:
IDTSInput90 input = this.ComponentMetaData.InputCollection.New();
input.Name = "Input";
IDTSOutput90 output = this.ComponentMetaData.OutputCollection.New();
output.Name = "Output";
// The output is synchronous with the input:
output.SynchronousInputID = input.ID;
// Create the GID output column (16-character Unicode string):
IDTSOutputColumn90 outputColumn = output.OutputColumnCollection.New();
outputColumn.Name = "GID";
outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, 16, 0, 0, 0);
}
///
/// Only 1 input and 1 output with 1 column is supported.
///
///
public override DTSValidationStatus Validate()
{
bool cancel = false;
DTSValidationStatus status = base.Validate();
if (status == DTSValidationStatus.VS_ISVALID)
{
// The input and output are created above and should be exactly as specified
// (unless someone manually edited the persisted XML):
if (ComponentMetaData.InputCollection.Count != 1)
{
this.ComponentMetaData.FireError(0, ComponentMetaData.Name,
"Invalid metadata: component accepts 1 Input.",
string.Empty, 0, out cancel);
status = DTSValidationStatus.VS_ISCORRUPT;
}
else if (ComponentMetaData.OutputCollection.Count != 1)
{
this.ComponentMetaData.FireError(0, ComponentMetaData.Name,
"Invalid metadata: component provides 1 Output.",
string.Empty, 0, out cancel);
status = DTSValidationStatus.VS_ISCORRUPT;
}
else if (ComponentMetaData.OutputCollection[0].OutputColumnCollection.Count != 1)
{
this.ComponentMetaData.FireError(0, ComponentMetaData.Name,
"Invalid metadata: component Output must be 1 column.",
string.Empty, 0, out cancel);
status = DTSValidationStatus.VS_ISCORRUPT;
}
// And the output column should be a Unicode string:
else if ((ComponentMetaData.OutputCollection[0].OutputColumnCollection[0].DataType != DataType.DT_WSTR) ||
(ComponentMetaData.OutputCollection[0].OutputColumnCollection[0].Length != 16))
{
ComponentMetaData.FireError(0, ComponentMetaData.Name,
"Invalid metadata: component Output column data type must be (DT_WSTR, 16).",
string.Empty, 0, out cancel);
status = DTSValidationStatus.VS_ISBROKEN;
}
}
return status;
}
///
/// Called before executing, to cache the buffer column indexes.
///
public override void PreExecute()
{
base.PreExecute();
// Get the index of each input column in the buffer:
IDTSInput90 input = ComponentMetaData.InputCollection[0];
inputColumnBufferIndex = new int[input.InputColumnCollection.Count];
for (int col = 0; col < input.InputColumnCollection.Count; col++)
{
inputColumnBufferIndex[col] = BufferManager.FindColumnByLineageID(input.Buffer, input.InputColumnCollection[col].LineageID);
}
// Get the index of the output column in the buffer:
IDTSOutput90 output = ComponentMetaData.OutputCollection[0];
outputColumnBufferIndex = BufferManager.FindColumnByLineageID(input.Buffer, output.OutputColumnCollection[0].LineageID);
// Get the GID web service:
gidService = new GID.WS_PDF.PDFProcessService();
}
///
/// Called to process the buffer:
/// Get a new GID and save it in the output column.
///
///
///
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
if (! buffer.EndOfRowset)
{
try
{
while (buffer.NextRow())
{
// Set the output column value to a new GID:
buffer.SetString(outputColumnBufferIndex, gidService.getGID());
}
}
catch (System.Exception ex)
{
bool cancel = false;
ComponentMetaData.FireError(0, ComponentMetaData.Name, ex.Message, string.Empty, 0, out cancel);
throw new Exception("Could not process input buffer.");
}
}
}
///
/// Called after executing, to clean up.
///
public override void PostExecute()
{
base.PostExecute();
// Resign from the GID service:
gidService = null;
}
}
}
View 1 Replies
View Related
May 9, 2015
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly..Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
FROM myTable
[code]....
View 5 Replies
View Related
Sep 9, 2015
I wan to print out the dynamic query result so that i can use as a script for some tasks.This is the scenario wher i got stuck, i am not able to print out the result as it return only the last value because of OUTPUT param limitation
Is there any way to print all the 3 INSERT stmt.
IF OBJECT_ID ('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp (Command varchar(8000))
INSERT INTO #temp
SELECT 'INSERT INTO Test1(column1,column2)values(1,2)'
UNION ALL
SELECT 'INSERT INTO Test2(column1,column2)values(1,2)'
[code]....
View 4 Replies
View Related
Jun 22, 2015
I have this query in a stored procedure. I will simplify it so that it shows the issue I am having.
I have two tables. One table has a PK called PRSubLineID. the other table has that in a foreign key.
I did a query like this using the IN Statement
SELECT PRSubLineID, PRSUBLINENumber, f2, f3,
FROM PRSUBLINES PRSL
WHERE PRSL.PRSUBLINENumber LIKE '%test%'
AND PRSL.PRSubLineID NOT IN
(SELECT CSL.PRSUBLineID FROM CtrSubLines CSL)
This is supposed to select those items from one table with a PRSubLineNumber containing the work Test which has a primary key that has not been used as a foreign key in the other table. However I am getting an empty resultset back.
But when I comment out the "And" and end the query after '%test%', Like this:
SELECT PRSubLineID, PRSUBLINENumber, f2, f3,
FROM PRSUBLINES PRSL
WHERE PRSL.PRSUBLINENumber LIKE '%test%'
I show a resultset containing three records whose PRSubLineID' s are 2384, 2385, 2386.
But when I add this query to the query window:
Select * FROM CtrSubLines CSL WHERE CSL.PRSubLineID in (2384, 2385, 2386)
I also get a null resultset. So hoping to decipher what was going on I changed the initial query to look like this.
SELECT PRSubLineID, PRSUBLINENumber, f2, f3,
FROM PRSUBLINES PRSL
WHERE PRSL.PRSUBLINENumber LIKE '%test%'
AND PRSL.PRSubLineID /*NOT*/ IN
(SELECT CSL.PRSUBLineID FROM CtrSubLines CSL)
And this still returned a null result set.
Basically this is saying that my PRSubLineIDs are neither included nor excluded in the set of all values of PRSubLineID in the CtrSubLines table. But that is simply impossible.
View 5 Replies
View Related
Oct 22, 2007
I have the following query in an ExecuteSQL Task:
Insert Into Table2
Select * From Table1 Where Column1Val = '4'
As you can see, I don't need any parameters so I havent configured any. Also, there should not be any result set so I shouldnt need to configure a resultset parameter.
Why is the above query failing with
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly
View 4 Replies
View Related
Oct 1, 2007
I am trying to make a single display page for an author's books.the books page only displays books of a type "type" (novels, non-fiction, etc)I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this: <asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader %>"
SelectCommand="SELECT * FROM [Books] ( If @Type <> "" then WHERE ([Type] = @Type)) ORDER BY [SortDate] DESC"> But it doesn't seem to want to work. I get a "server tag is not well formed" error.
View 2 Replies
View Related
Jan 7, 2008
I have a table called "member" that has two fields (amongst others) called "firstname" and "city"I want to return a list that sorts people who live in the same city, but does not return people who do not have a city in common.For example if this is my data:Karen - ParisMike - RomeTim - Dallas
Jim - ParisChris - DallasJohn - ManhattanSarah - OrlandoDavid - DallasThe query would return this:Dallas - ChrisDallas - DavidDallas - TimParis - JimParis - KarenI have this SQL so far: Select city, firstname From member Order By city, firstnamebut it includes all the cities including those that only have 1 resident.Can someone tell me how to do this?Thanks a lot,Chris
View 2 Replies
View Related
Mar 18, 2008
hi,i have three fields in the database tablep1 p2 rank10% 20% 3 21% 40% 2now i am passing the input as 15%...this 15% is related to 3rd rank.. so i need the o/p rank as 3..if the p1 and p2 datatypes are varchar..then how we can we write the query for that one tgo get the rank as 3.Ramesh
View 2 Replies
View Related
Apr 21, 2005
Hi All
Can anyone tell me what this simple SQL query would be:
Find all the words "black", "dvd" and "player" from any of the fields "Product", "Brand" or "Description".
Many thanks
accelerator
View 2 Replies
View Related
May 15, 2005
I am trying to select players from a database based on the userid not having any user's name in it with an order by ASC. I basically want all the players that haven't been taken. Here is what I came up with and it gives me empty.
strSQLA="SELECT UserID, playerName from Allplayers WHERE UserID = '' ORDER BY playername ASC"
View 4 Replies
View Related
May 28, 2006
I hope it's considered appropriate to post a SQL query question here; it's not I'll gladly post this question elsewhere. That having been said, I've run into a simple but quite tricky SQL query and was wondering if the community might be able to help. Here's what's going on:
I have a "Documents" table. Let's call it DI have a "Benefits" table. Let's call it B.There is a many-to-many relationship between D and B so I have a third table to store the relationships. Let's call that table BD.
When I create a document it creates a row in D. Let's call that D1, D2...DN. When I create a benefit it creates a row in B. Let's call that B1, B2...BN
When I associate a document to a paritcular benefit, let's say that if D1 and B2 are associated, that I created row B2-D1 in my BD table. Now here's what my challenge is. Suppose I have these entries in my tables:
D: D1, D2, D3B: B1, B2, B3
Table BD:B1-D1B2-D1B2-D2
When the user goes to add documents to benefit B1, I want to show the user only documents that are not yet added to B1. In this case, I want to show the user D2 and D3 but not D1 since that one's already added. How do I write a SQL query to do this?
Here's what I've been using, but it shows too many results (e.g. included D1, D2, D3)
SELECT D.*, BD.*FROM Documents AS d LEFT JOIN rel_BenefitsDocuments AS bd ON d.doc_id = bd.doc_idWHERE (benefit_id <> @benefit_id OR benefit_id IS NULL)
Any thoughts?
View 2 Replies
View Related
Mar 7, 2003
Hi,
I want to write a sql query for an asp page which will display only unique rows from the specified column along with the number of count for each unique row.
Example:
Table that I want to query
Last Name || First Name
Gates || Bill
Boyce || Mike
Gates || Bill
Gates || Phil
Results I want:
Last Name || First Name || Count
Gates || Bill || 2
Boyce || Mike || 1
Gates || Phil || 1
Thanks a lot,
Heta
View 2 Replies
View Related
Aug 21, 2004
Hi,
I have a very simple question:
declare @treshold int
set @treshold = 10
SELECT
dbo.fn_Calc(t.column1) as calc,
t.column2
FROM
mytable t
WHERE
dbo.fn_Calc(t.column1) > @treshold
I can't think of a way to get rid of the function call in the WHERE clause.
Is this actualy a problem ?
I mean does realy the function fn_Calc execute 2 times in this statement and isn't this a performance issue then?
thanks
View 2 Replies
View Related
Jan 17, 2002
Hi,
I am new to SQL and this question may be most easiest to many of you. Here is what I need.
I have two identical tables (exactly the same in structure) having a compound primary key with a combination of 3 columns. Can someone give me the most efficient query that fetches all the rows from table1 that are not in table2.
Thanks in advance for your help
Kevin
View 1 Replies
View Related
Oct 21, 2006
This is my query...I am attempting to only return the records for each
sales_contact with the greatest issue_id
select
sc.sales_contact_id
, idd.issue_id
, sr.code
from
sales_contact sc
, invoice i
, invoice_line_item il
, sales_region_special_section srss
, sales_region sr
, issue_date idd
where
sc.sales_contact_id = i.sales_contact
and i.invoice_id = il.invoice
and srss.sales_region = sr.sales_region_id
and il.issue_date = idd.issue_date_id
and srss.invoice_line_item = il.invoice_line_item_id
order by
sc.sales_contact_id
================
heres some of the result set I want only the records with the greatest id
for each unique sales contact...
AF85F32E-8E34-4C40-9468-00148A34E903, 41, N
AF85F32E-8E34-4C40-9468-00148A34E903, 42, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 70, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 34, BC
B44C914E-6001-40CE-8AB6-0126BD572D45, 25, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 26, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 24, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC
B44C914E-6001-40CE-8AB6-0126BD572D45 , 28, NW
this would be an ideal result set using the data above...note there are
situtations where there are n* for a greatest issue id.
AF85F32E-8E34-4C40-9468-00148A34E903, 42, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, NW
thanks in advance for your help.
View 1 Replies
View Related
Jan 7, 2005
This should be easy, I'm just having a brainfart at the moment, can't remember how to do this:
Say I have a bunch of records in a table, with an ID field as Key. I want to return the count of times each ID shows up, so for the following example data:
Code:
-----
| ID |
-----
| 1 |
| 2 |
| 1 |
| 4 |
| 1 |
| 4 |
------
Would return something like:
Code:
----------------
| ID | Count |
----------------
| 1 | 3 |
| 2 | 1 |
| 4 | 2 |
-----------------
This should be obvious but for some reason I can't see it.
When this is done I'm going to join in another table to get a name based on the ID with the highest count. I believe it's faster to do a TOP 1 here rather than after the join, but I want to verify that while I'm at it also.
View 3 Replies
View Related
Sep 26, 2006
OLD_TABLE
CustomerPost_ClosestStore1_ClosestStore2_ClosestStore3
00501_____9339_____9193_____9445
02151_____9161_____9838_____9185
02917_____9788_____9105_____9626
06443_____9644_____9102_____9286
43434_____9258_____9496_____0
NEW_TABLE
CustomerPost_ClosestStore1_ClosestStore2_ClosestStore3
00501_____9339_____9193_____9445
02151_____9161_____9838_____9185
02917_____9788_____9105_____9626
06443_____9644_____9102_____9286
43434_____9258_____9496_____1111
(end result)
FINAL_TABLE
43434_____9258_____9496_____1111
I have two tables ... I am trying to get only the rows that are different.
View 2 Replies
View Related
Sep 19, 2005
Hi folks,
I am a complete newbie to SQL. I have a simple query to make in SQL, but I'm not being able to construct the statement properly. I've already tried googling this info, but with little luck. If someone could help me here, I'd be very thankful.
The problem:
I have a table with a list of questions. All questions have a serial number. The query has to return whether a particular question is the last one in the table or not (according to serial no.)
Logically, this is the query -
Code:
select islast = 0
if count(*) from question where serialno > $serial != 0
else
select islast = 1;
The '$serial' part will get replaced dynamically before query execution through another program.
Any idea how to code this in valid SQL? I would like to make it a simple statement and not a stored procedure. Thanks.
View 5 Replies
View Related
Sep 1, 2006
Hi,
This is probably a simple SQL statement question, so please bear with me.
Basically I have a CustomerMaster with the following fileds
1) CustomerID
2) CustName
3) Address
4) ResTelNo
I want retreive the CustName,Address and ResTelNo if the ResTelNo has occured in the table more than twice.
Could someone please help with this. I would like to use the same query in MS Access as well.
Thanks in advance for your help.
View 2 Replies
View Related
Sep 17, 2004
Hey.. hope someone can help, i desperatly need some assistance. I have a few query issues but first things first.. one is that i have a table of messages and users having an online conversation, so fields are msg id, subject, topic, message, user, reply to and time it was sent. Im trying to do a query that will tell me which user sent the most messages, ive tried using count and stuff like that but not really getting anywhere. It will basically have to count each time a user has spoken and then give me the name of the user that has sent the most messages, ive been playing around with stuff like
SELECT Count(*) AS Expr1
FROM Table1
WHERE User='andro8472' OR User='bumies';
That will count the times those users have spoken but cant get further
any help would be greatly appreciated really stuck at the mo..
thanks
View 4 Replies
View Related
Feb 29, 2008
I'm having a bad day as I just cannot get a query to work and its doing my head in and it should be a simple query.
I'll try and explain what I need.
Have a simple query with one join, this is it so far:
SELECT DISTINCT Replace(StaffName,'''''',' ') AS StaffName,OutReason,OutStartDate
,OutEndDate,OutStartTime,OutEndTime,OutID
FROM vwOutOfOffice
LEFT OUTER JOIN vwStaffList ON OutUser=StaffID
WHERE OutComplete=0
What I'm having trouble with adding to the WHERE is as follows, I need a list of Staff who are out of the office based on the various criteria:
Example
Current date=29/02/2008
Current time=14:00
Start date---End Date----Start Time--End Time--Should Appear
27/02/2008--28/02/2008--09:00------17:00-----No (because of date)
28/02/2008--03/03/2008--08:00------17:00-----Yes
28/02/2008--29/02/2008--17:00------17:00-----Yes
29/02/2008--29/02/2008--12:00------14:00-----Yes
29/02/2008--29/02/2008--14:30------17:00-----No (because of time)
02/03/2008--02/03/2008--08:00------10:00-----No (because of date)
There may be others I've missed but as long as the dates combined with the times match correctly I need the records to appear.
Please help before I end up putting my head through the monitor.
View 2 Replies
View Related
