Returning Column Name(s) That Contain Data
Jun 25, 2007
say i have some columns and a person can vote either once, twice or 3 times ( checkbox style control ) and in the database it looks something like this:
eg: ID.... vote1..... vote2.... vote3
1 0 0 1
2 1 0 1
for ID = 2, what type of query would i have to write to return the column names vote1 & vote3 ( vote2 is not null )
Cheers!!!
View 1 Replies
ADVERTISEMENT
Oct 16, 2007
SQL 2005 9.00.3402.00 (x64) As Above really when I select * OR select a single column from the view the wrong column data is returned. in SQL Management Studio when I expand the Columns of the view it reflects the old table structure not the new table structure. I can easily fix by compiling the view again but this would mean I would have to recompile all referencing views when I make a change to table structures. I've tried various DBCC Clean Buffers & drop cache with no effect. Is there a command to recompile all views & poss stored procs in a database. Any help or explanation would be appreciated GW
View 12 Replies
View Related
Aug 1, 2006
Hi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?
View 5 Replies
View Related
Mar 12, 2008
If a Select is done on a column whose data type is nvarchar(16) and contains only numerals (UPC numbers) the select does not return the record.
1. Query with numerals in nvarchar column works as long as multiple records are returned (LIKE '012%')
2. Numeric (INT only one tested) columns works as expected
3. String columns with alpha data works as expected
4. Problem only exist when running in Device Emulator and/or actual device.
5. Same test on desktop app runs as expected.
6. Windows Mobile 6, Vista Ultimate
7. Same results when when connection to device from SSMS
8. SQL Servers comes on
Previous thread discussion of this problem (I thought that Parameters corrected problem, but not in all cases???)
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.ce&mid=0cd9cd3a-f9b0-477f-b1e7-c27eb76158ae
Here is the complete code:
SqlCeConnection _conn = null;
_conn = new SqlCeConnection(@"Data Source=program FilesTestResultSetevsoft.sdf;");
_conn.Open();
// DOES NOT WORK *** This statement does not return the record (it exist)
string _sql = "SELECT * FROM Product where RegDescr='0123456' ";
// works correctly
string _sql = "SELECT * FROM PRODUCT where ProdNum = 6523 ";
// works correctly *** as long as multiple records are returned
string _sql = "SELECT * FROM PRODUCT where RegDescr LIKE '01%' ";
// works correctly
string _sql = "SELECT * FROM PRODUCT where RegDescr='BACARDI SILVER RAZZ'";
SqlCeCommand _cmd = _conn.CreateCommand();
SqlCeDataReader _rdr;
_cmd.CommandText = _sql;
_cmd.CommandType = CommandType.Text;
// Same results using ExecuteResultSet or ExecuteReader
//_rdr = _cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
_rdr = _cmd.ExecuteReader();
listBox1.Items.Add("In the while loop");
while (_rdr.Read())
{
listBox1.Items.Add(_rdr.GetValue(1) + " / " + _rdr.GetValue(3));
}
listBox1.Items.Add("Done");
View 1 Replies
View Related
Nov 4, 2015
#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):
View 6 Replies
View Related
Jun 9, 2004
I am currently using IDENT_CURRENT to return the Id of a new row in SQL 2000, but I am looking for a similar way to do this in SQL 7. Ihave no experience with SQL 7
Anyone remember how they did this ?
View 1 Replies
View Related
Oct 30, 2006
In this query
SELECT E1.id, E2.id
FROM productHeadings AS B1, productHeadings AS E1,
productHeadings AS E2
WHERE B1.id = E1.parent_id
AND E1.id = E2.parent_id
and B1.id = 1
the result is 2 id columns... is there a way to return all id in 1 column?!
View 3 Replies
View Related
Dec 24, 2003
Hello, { Merry xmas to all few hours early but hey !! }
I'm trynig to create an application that will connect to any database that i specify and return the colums names within any table that i specify.
I have done the code that establishes the connection but im a bit stumped on how to get the colum names from a tabel and was wondering if anybody had any sourse code for this in VB.
Thanks
Rob
ps: have a good new year !!
View 3 Replies
View Related
Sep 7, 2012
My table has a column called Period i want to get a list of different periods example:
Period
1
2
31
1
2
4
12
31
2
then run an sql statement and should return you the following
Period
1
2
4
12
31
View 3 Replies
View Related
Feb 24, 2008
How do I create a select query which returns multiple columns from one actual DB column?
DB structure
ID (int), photo (nvarchar(50)), name (nvarchar(50))
Sample data
1, 'photo1.jpg', 'john smith'
2, 'photo2.jpg', 'jane doe'
3, 'photo3.jpg', 'bob brown'
4, 'photo4.jpg', 'mary brown'
5, 'photo5.jpg', 'sue smith'
6, 'photo6.jpg', 'bob rogers'
...
Required output
pic_col_1, name_col_1, pic_col_2, name_col_2, pic_col_3, name_col_3
photo1.jpg, john smith, photo2.jpg, jane doe, photo3.jpg, bob brown
photo4.jpg, mary brown, photo5.jpg, sue smith, photo6.jpg, bob rogers
Normally, I would just query the data and have the client data loop over the dataset to create the required output, however in this application it is not an option...
Thanks,
Steve
View 3 Replies
View Related
Jan 24, 2005
Hi ,,
How to write the Sql Query to return the next generated Identity from the Sql server database.
View 1 Replies
View Related
May 1, 2008
I am trying to get the schema of database using the getschema method. However, the schema that is being returned does not include the description. I have added in table and column descriptions for some of my tables and columns but the dataset returned does not include the description column.
Any idea on how to get the description to be output?
Dim testCn As System.Data.OleDb.OleDbConnection
testCn = New System.Data.OleDb.OleDbConnection(step2.GetRevEngConn.ConnectionString)
Dim testDS As DataTable
testCn.Open()
testDS = testCn.GetSchema("TABLES")
testds.writexml("c:schematables.xml")
testDS = testCn.GetSchema("COLUMNS")
testDS.writexml("C:schemacol.xml")
testCn.Close()
- <Columns>
<TABLE_CATALOG>XDMDB_LAPTOP</TABLE_CATALOG>
<TABLE_SCHEMA>dbo</TABLE_SCHEMA>
<TABLE_NAME>PropertyValue</TABLE_NAME>
<COLUMN_NAME>property_value</COLUMN_NAME>
<ORDINAL_POSITION>6</ORDINAL_POSITION>
<COLUMN_HASDEFAULT>false</COLUMN_HASDEFAULT>
<COLUMN_FLAGS>230</COLUMN_FLAGS>
<IS_NULLABLE>true</IS_NULLABLE>
<DATA_TYPE>130</DATA_TYPE>
<CHARACTER_MAXIMUM_LENGTH>0</CHARACTER_MAXIMUM_LENGTH>
<CHARACTER_OCTET_LENGTH>0</CHARACTER_OCTET_LENGTH>
<CHARACTER_SET_CATALOG>master</CHARACTER_SET_CATALOG>
<CHARACTER_SET_SCHEMA>dbo</CHARACTER_SET_SCHEMA>
<CHARACTER_SET_NAME>iso_1</CHARACTER_SET_NAME>
<COLLATION_CATALOG>master</COLLATION_CATALOG>
<COLLATION_SCHEMA>dbo</COLLATION_SCHEMA>
<COLLATION_NAME>SQL_Latin1_General_CP1_CI_AS</COLLATION_NAME>
<COLUMN_LCID>1033</COLUMN_LCID>
<COLUMN_COMPFLAGS>196609</COLUMN_COMPFLAGS>
<COLUMN_SORTID>52</COLUMN_SORTID>
<COLUMN_TDSCOLLATION>CQTQADQ=</COLUMN_TDSCOLLATION>
<IS_COMPUTED>false</IS_COMPUTED>
</Columns>
View 2 Replies
View Related
Mar 23, 2006
I have the following 2 Sql queries. They both are rowcounts of the same column but based on different criteria. What I want to do is return the two results side by side in separate columns:
-- Subscriptions since Sept. 24th
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate is not null
and c.clientunsubscribe = 0
and c.validemailaddr = 1
and s.unsubscribe = 0
-- Subscriptions in February
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate BETWEEN '2006-02-01 00:00:00.000' AND '2006-03-01 00:00:00.000'
AND c.clientunsubscribe = 0
AND c.validemailaddr = 1
AND s.unsubscribe = 0
It seems like a UNION ALL should work but it just returns the results in one column. I tried changing the count by specifying a different column for each but that doesn't work either. I also tried writing it as one query and using alias to differentiate the two tables but that just gives me syntax errors. I suspect there is a more elegant way to do this but I'm at a loss. Any help would be greatly appreciated!
Caeanis
View 1 Replies
View Related
May 7, 2014
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
View 9 Replies
View Related
Jan 15, 2007
I cannot manage to fetch the new timestamp value inside a TSQL Transaction. I have tried to Select "@LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match. If they do, the Update begins as a Transaction. I send @LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@NewLastChanged"): 1 select @TimestampCheck = LastChanged from ADD_Address where AddressId=@AddressId
2
3 if @TimestampCheck is null
4 begin
5 RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1) -- AddressId does not exist.
6 return -1
7 end
8 else if @TimestampCheck <> @LastChanged
9 begin
10 RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1)
11 return -1
12 end
13
14
15 Begin Tran Address
16
17 Update ADD_Address
18 set StreetNumber= @StreetNumber, AddressLine1=@AddressLine1, StreetTypeId=@StreetTypeId, AddressLine2=@AddressLine2, AddressLine3=@AddressLine3, CityId=@CityId, StateProvidenceId=@StateProvidenceId, ZipCode=@ZipCode, CreateId=@CreateId, CreateDate=@CreateDate
19 where AddressId= @AddressId
20
21 select @error_code = @@ERROR, @AddressId= scope_identity()
22
23 if @error_code = 0
24 begin
25 commit tran Address
26
27 select @LastChanged = LastChanged
28 from ADD_Address
29 where AddressId = @AddressId
30
31 if @LastChanged is null
32 begin
33 RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1)
34 return -1
35 end
36 if @LastChanged = @TimestampCheck
37 begin
38 RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1)
39 return -1
40 end
41 return 0I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?Thank you,jspurlin
View 1 Replies
View Related
Nov 26, 2015
I am writing a query and have the bulk of it already written.
I am looking at a table that contains customer orders. There is a column named Customer_Order.Status Available values for this column is R, F, H, and C.
I'd like for my query to return all lines that have the value R, F, H.
My where clause is written like this
WHERE CUSTOMER_ORDER.SITE_ID = 'XXX' AND CUSTOMER_ORDER.STATUS = ('R','H','F')
I know I'm missing something....
View 3 Replies
View Related
Aug 10, 2015
I am doing a distinct count on a related table's column, but get an out of memory error if I run it for the entire table (works great for just a few rows when filtered down).The error I get is: "We couldn't get data from the external source.The operation has been cancelled because there is not enough memory available for the application. If using 32-bit version of the product consider upgrading.
I know I can add a related column and that works fine...but that seems to me like I've defeated the purpose, I have a good and proper lookup table, and should be able to run my query against its relationship.Here is the query and details below *Note I supplied a scaled down sample, on my actual model I receive these errors, not in the sample as it has only a few rows
List Workers Distinct Project Customers:=CALCULATE(DISTINCTCOUNT(Projects[CustomerID]),'WorkersToProjects')
Other measure which returns no errors, but included for completeness:
List Workers Projects:=CALCULATE(DISTINCTCOUNT([ProjectID]),ISBLANK(WorkersToProjects[ProjectID])=FALSE())
My goal here is to allow the user to view the workers assigned to a project, but also get counts of the workers assigned to the CUSTOMER of a project. For example, suppose we lose a customer, we want to see how many workers would be impacted by that, so a count of projects per worker is not useful there, we need to see a count of workers per project's customer (owner of project whom project work is being done for)The question being: How can I accomplish this:
1. WITHOUT adding a calculated column to WorkersToProjects (of Projects.CustomerID)
2. WITH better performance?
There must be a better way to write this DAX to still get the correct answer?*Pic of pivot table, again, the numbers are accurate but the formula used to List Workers Distinct Project Customers measure does NOT scale :( 3 count for red , the number of Projects John has and 2 count for blue, the unique customers/owners of those projects "Veridian Dynamics" and "Massive Dynamic". URL....
View 3 Replies
View Related
Feb 20, 2007
Hello,
Im currently working on a asp.net file hosting wesite, and im being faced with some problems
I currently have 4 sql tables. DownloadTable, MusicTable, ImageTable and VideoTable. Each of those tables contain a UserName column, a fileSize column and sme other columns. What i want to do is add up all the values in the fileSize column, for each table, and then add them up with the tables, and return one value, and all this happens where the UserName column corresponds to the UserName of the currently logged on User.
I already have an sql statement that performs this exact thing. It is as follow
select TotalDownLoadSize = sum(DownloadSize) + (select sum(VideoSize) from VideoTable where ([UserName] = @UserName ) ) + (select sum(ImageSize) from Images where ([UserName] = @UserName) ) + (select sum(MusicSize) from MusicTable where ([UserName] = @UserName) ) from DownloadTable where ([UserName] = @UserName)
But the problem is that all of the tables have to have a value in there size columns for the corresponding user, for this sql statement to return something.
For example, lets say i logged in as jon. If, for the UserName jon, the sum of DownloadTable returned 200, the sum of VideoTable returned 300, the sum of MusicTable returned 100. The sql statement i stated above will return 4 instead of 600, if the sum of ImageTable returned zero.
Is there way around this?
Im not sure if ive been cleared enough, please feel free to request more info as needed.
Thank you very much, and thx in advance.
View 5 Replies
View Related
Feb 22, 2007
I have a number of tables, and I need to create a summary of the data.Now I have two choices. I could create a stored procedure that creates a temp table with the filters applied. This would require 12 selects for each year (we have 3 years of data so far). This means 36 selects so far that fill a temp table.The second choice is to create a table with the required columns and rows. As I am esspecially cross-joining 4 tables, the table would have about 10 x 10 x 12 x A rows, where A is a variable that will grow quickly. I can then keep this table up-to-date using triggers for each insert. Then all I need to do is use an aggregate funtion on the relevant filter.My question is, which is more efficient. Creating a stored procedure that creates the table dynamically, or have a table with thousands of rows and using an aggregate function on these.PS I am using SQL Server 2000Jag
View 1 Replies
View Related
Oct 21, 2007
Hi! I have a sql query in stored procedure: SELECT Salutation + ' ' + FirstName + ' ' + LastName AS fullname
Ok, this returns a value if salutation is not null, but if the salutation is null it doesn't return any value, I was thinking if the saluation is null then I would atleast get the firstname and last name. Any help appreciated on this.
View 4 Replies
View Related
Apr 12, 2004
SELECT @@TEXTSIZE
SET TEXTSIZE 4096
SELECT @@TEXTSIZE
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(emailTemplates.Data)
FROM emailTemplates WHERE [Name] = 'AccountInfoReceipt1'
READTEXT emailTemplates.Data @ptrval 0 2000
GO
Thats my code. and it never returns the entire email template. why?
thx in adv
View 2 Replies
View Related
Apr 2, 2006
I have a stored procedure (in SQL Server 2005 Express) that returns a string. The problem is when I call it from my web page I get only the first character of the string. This is my SP:
ALTER PROCEDURE dbo.usp_CalcDeliveryCharge
@mintDistance int,
@mintCustomer_ID int = 0,
@mintRate int = 0 OUTPUT,
@mstrZone nchar(10) = null OUTPUT
AS
/* SET NOCOUNT ON */
SELECT @mintRate=RATE,
@mstrZone=ZONE
FROM tblRates
WHERE Customer_ID=@mintCustomer_ID
AND Mile_Range_Min <= @mintDistance
AND Mile_Range_Max >= @mintDistance
And this is my code:
sql_Command.CommandText = "usp_CalcDeliveryCharge"
sql_Command.CommandType = CommandType.StoredProcedure
sql_Command.Parameters.Clear()
sql_Command.Parameters.AddWithValue("@mintDistance", intApproxMiles)
sql_Command.Parameters.AddWithValue("@mintCustomer_ID", Profile.CompanyID)
sql_Conn.Open()
sql_Reader = sql_Command.ExecuteReader()
While (sql_Reader.Read())
Me.lblZone.Text = sql_Reader.Item(0).ToString
End While
sql_Conn.Close()
sql_Reader.Close()
sql_Command.Dispose()
View 1 Replies
View Related
Apr 17, 2008
I have two databases with table structures exactly the same. The query in one database works and returns correctly. The same query (copied and pasted) in another database returns the correct records but completely ignores the order by. Even stranger, when you are creating the query under modify and run it, it is fine. But when I right-click on the query and go to 'Open View' then the query does not return correctly. Below is the query:
SELECT TOP (100) PERCENT pf.description AS incentive, CAST(i.targetc AS FLOAT) AS targetc, i.targetp, b.basis, d.description, i.iSelect, i.direction, pf.pValue,
pf.pFigure
FROM dbo.PerfFactorWeights AS pfw INNER JOIN
dbo.PerfFactors AS pf ON pfw.incentiveID = pf.incentiveID INNER JOIN
dbo.Incentive AS i ON pfw.incentiveID = i.incentiveID AND pfw.basis = i.basisID INNER JOIN
dbo.Basis AS b ON i.basisID = b.basisID INNER JOIN
dbo.Division AS d ON pfw.division = d.uniqueID
ORDER BY incentive, d.uniqueID, b.basis
View 4 Replies
View Related
May 29, 2008
Hello everyone,
I set up a View in SQL Server 2005. The syntax checks ok, however, when I execute it, it doesn't return any data.
This is my Query:
SELECT DATEPART(hh, Time) AS Time, COUNT([Recipient-Address]) AS [CountOfRecipient-Address], ROUND(SUM([Total-bytes]) / 1048576, 2)
AS [SumOfTotal-Bytes]
FROM dbo.TrackingLog
WHERE (RIGHT([Recipient-Address], LEN([Recipient-Address]) - PATINDEX([Recipient-Address], '@')) IN
(SELECT Domains
FROM dbo.Domains))
GROUP BY DATEPART(hh, Time)
The part that I am most concerned about is the WHERE section. If I remove it, I get some data returned. If I don't, obviously I don't get anything back.
Any suggestion would be great.
Thanks
View 4 Replies
View Related
Apr 9, 2007
I'm having trouble with the display of data when I test my asp page. I basically retrieves or pulls data from different tables in a database. Based on an ID number (which I can change manually by typing in a value and appending it to the URL), it shows GuestName, GuestDescription and URL as text link. I can get it to do that much. But for some reason it's not showing the URL associated with the Guest Name. I dont think it's the display section of my code. I think it's the Select statement.
Here is what the output looks like in my Firefox and IE browsers when I test. Some data has been changed to protect it's integrity:
(Name in bold) Mark Crisp's author of The Bush Lexicon. His new book...
Related Links:
John Doe interviewed by Susan Smith, 123 News
What it should say in the link is this: Mark Crisp's blog (as text link)
When I test for different data I just change the end of the URL like this:
defaultprogram4.asp?ID=1234 (this number represents an ID column in a table)
Code Snippet
<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:webserviceCompanyCompany.UDL"
set rs = Server.CreateObject("ADODB.Recordset")
id=request.querystring("id") 'If this line is commented out the page will be blank.
'However you can still append a record number to the end of the URL and display that one.
IF id <> "" then id=id else id="1234" end if 'This line shows the default record of 1234. If this line is commented out the page will ONLY show the default record but will NOT allow you
'to append a different number
strSQL = "SELECT *, T_Programs.ID AS Expr1, T_ProgramGuests.ProgramID AS Expr2, T_ProgramGuests.GuestName AS Expr3, T_ProgramGuests.GuestDescription AS Expr4, T_ProgramLinks.URL AS Expr5, T_ProgramLinks.Description AS Expr6 FROM T_ProgramGuests CROSS JOIN T_Programs CROSS JOIN T_ProgramLinks WHERE (T_ProgramGuests.ProgramID = '" & id & "')"
rs.Open strSQL,con 'open a connection to the database
%>
<br />
<strong><% Response.Write RS ("GuestName") %> </strong> <% Response.Write RS("GuestDescription") %><br /><br />
Related Links:<br />
<li class='basic'><A HREF="<%= RS("URL") %>"><%= RS("Description") %></A></li>
<!-- END OF THE GUESTS AND LINKS SECTION -->
<%
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>
Bottom line here is this: Why would I be seeing one name but a link not associated with that name? It's as though it's reading a name from the ProgramGuests table and a URL from the
ProgramLinks table (Except that: it shows a completely different unrelated URL to that name).
What am I missing?
View 1 Replies
View Related
Apr 19, 2006
i want to return certain number of columns frm table using store procedure .and i hav to load those returned data into text boxes which i created in asp.net page.is there a way
View 2 Replies
View Related
Jul 14, 2007
Hello,
I am trying to use the data returned by a webservice that our application team has built.
What method should they use to return the data to the caller ?
If they try to retun a data set the SSIS is saying that it is not a well formated XML.
The webservice is using an API call to HIS, can I make this call using C# or some other mechanisam directly from SSIS?
View 3 Replies
View Related
Jan 12, 2008
Hello,
I recently created a linked server to db2. When I do a join on the linked server it is returning to much data.
select * from table1 left join [linkedServer]..Schema.Table as tb1 on table1.id=tb1.id
For now, I had to first get the ids I wanted from table1 and then use a subquery to join table1 on the a result set produced from a query to the linked server that only specified those ids. Below is an example
select * from table1 left join (select col1 from [linkedServer]..Schema.table where id=1 or id=2) as tb1 on table1.id=tb1.id
I'm guessing I have some setting wrong. One of the biggest reasons for me to use the four part name is so I can do joins like the first example.
Please let me know if you have any ideas.
Thanks
View 4 Replies
View Related
Jan 18, 2008
Hi all,
I have a simple query like this:
SELECT crop, ItemNo, LotNo, Site, Process, IntID, ObsDetail, ObsValue,
(CASE WHEN (rtrim(crop) = '010' OR
rtrim(crop) = '010W' OR
rtrim(crop) = '019' OR
rtrim(crop) = '019W' OR
rtrim(crop) = '018') THEN 'CORN' WHEN (rtrim(crop) = '0150' OR
rtrim(crop) = '0159' OR
rtrim(crop) = '0150W' OR
rtrim(crop) = '159W' OR
rtrim(crop) = '158') THEN 'RICE' ELSE '' END) AS Product, TestStartDate, TestEndDate, ObsString
FROM V_LAB
WHERE (LotNo >= @lotStart) AND (LotNo <= @lotEnd)
If I run this query and filled the lotStart and lotEnd parameter with: G0424MK
Then, below's the data returned. (I run the query in management studio)
010W
30A97-CLN-UNT-BULK
G0424MK
IDPMLG01
AFTER CLEANER
4982
PRCT MOIST
10.35
CORN
NULL
010W
30A97-CLN-UNT-BULK
G0424MK
IDPMLG01
AFTER CLEANER
4982
PRCT GOOD
97.5
CORN
NULL
010W
30A97-CLN-UNT-BULK
G0424MK
IDPMLG01
AFTER CLEANER
4982
PRCENTMEAN
97.75
CORN
NULL
I'd only going to retrive 3 data that i'd like to show on the report:
1. Moisture Content, with formula:
=IIf(RTRIM(Fields!ObsDetail.Value) = "PRCT MOIST", Fields!ObsValue.Value, cdec(0.00))
the ObsValue value after i run on the report preview with LotStart and LotEnd G0424MK is: 10.35 (this is correct)
<!--[if !supportLists]-->2. Physical Damage, with formula:
=Iif(rtrim(Fields!ObsDetail.Value) = "PRCT GOOD", Fields!ObsValue.Value, cdec(0.00))
the ObsValue value after i run on the report preview with LotStart and LotEnd G0424MK sebesar: 97.50 (this is correct)
<!--[if !supportLists]-->3. Warm Test, with formula:
=Iif(rtrim(Fields!ObsDetail.Value) = "PRCENTMEAN", Fields!ObsValue.Value, cdec(0.00))
the ObsValue value after i run on the report preview with LotStart and LotEnd G0424MK sebesar: 0.00 (this is wrong) because it should be shown 97.75
anybody knows which part i made my mistake?
Thanks!
Addin
View 6 Replies
View Related
Aug 20, 2014
I have a table with Million plus records. Due to Running Totals article, I have been able to calculate the Trial_Balance for all months.
Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I need to be able to do this for multiple account types. So the two datasets that need to be included in logic is actindx and Calendar_Month.
For actindx of 2 and Calendar_Month of 2014-01-01The Trial_Balance_Debit is 19585.46 This would make the Beginning_Balance of actindx 2 and Calendar_Month of 2014-02-01 19585.46
I am trying to do some type of self join, but not sure how to include each actindx number differently.
Table creation and data insert is below.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TrialBalance](
[Trial_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
[Code] ....
View 7 Replies
View Related
Feb 26, 2007
Hi
I have coded the simple login page in vb .net which calls the stored proc to verify whether the user login details exists in the database. The stored procudure returns data back when I execute it in the SQL SERVER Management studio. But when I execute the stored proc in the 'Run stored Proc' wizard' , it is not retuning any data back. Connection string works fine as another SQL select command returns data in the same page.. I have included the VB code . Please help me to sort out this problem.Thank you.
If Not ((txtuser.Text = "") Or (txtpassword.Text = "")) Then
Dim conn As New SqlConnection()
conn.ConnectionString = Session("constr")
conn.Open()
Dim cmd As New SqlCommand("dbo.CheckLogin", conn)
cmd.CommandType = CommandType.StoredProcedure
' Create a SqlParameter for each parameter in the stored procedure.
Dim usernameParam As New SqlParameter("@userName", SqlDbType.VarChar, 10)
usernameParam.Value = Trim(txtuser.Text)
Dim pswdParam As New SqlParameter("@password", SqlDbType.NVarChar, 10)
pswdParam.Value = Trim(txtpassword.Text)
Dim useridParam As New SqlParameter("@userid", SqlDbType.NChar, 5)
Dim usercodeParam As New SqlParameter("@usercode", SqlDbType.VarChar, 10)
Dim levelParam As New SqlParameter("@levelname", SqlDbType.VarChar, 50)
'IMPORTANT - must set Direction as Output
useridParam.Direction = ParameterDirection.Output
usercodeParam.Direction = ParameterDirection.Output
levelParam.Direction = ParameterDirection.Output
'Finally, add the parameter to the Command's Parameters collection
cmd.Parameters.Add(usernameParam)
cmd.Parameters.Add(pswdParam)
cmd.Parameters.Add(useridParam)
cmd.Parameters.Add(usercodeParam)
cmd.Parameters.Add(levelParam)
Dim reader1 As SqlDataReader
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Try
reader1 = cmd.ExecuteReader
Using reader1
If reader1.Read Then
Response.Write(CStr(reader1.Read))
Session("userid") = reader1.GetValue(0)
Session("usercode") = CStr(usercodeParam.Value)
Session("level") = CStr(levelParam.Value)
Server.Transfer("home.aspx")
Else
ErrorLbl.Text = "Inavlid Login. Please Try logging again" & Session("userid") & Session("usercode") & Session("level")
End If
End Using
Catch ex As InvalidOperationException
ErrorLbl.Text = ex.ToString()
End Try
Finally
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
End Try
Else
ErrorLbl.Text = "Please enter you username and password"
End If
View 5 Replies
View Related
Jul 23, 2005
I am using SQL7 Query Analyzer. A simple select * from myMLSview andthen I save results as a .csv file has the commas messed up in quite afew places. The data is messed up before I save it to the .csv file. Sothere are blank spaces being added here and there causing them to readas 'add comma' when saving to .csv format. Even the column data ismessed up... example: ,photo_mod_time,photo_mo,d_date, should read,photo_mod_time,photo_mod_date,I have a view on a remote MS-SQL7 Server, the view has about 200columns and the data returned from the my querys can be from 10 to 25MBin size. Connection is via Roadrunner/cable. Should I use anothermethod, maybe command line? Any suggestions?
View 3 Replies
View Related
Feb 12, 2008
Hello,
I am trying to get records related to a Device_ID with the following conditions:
IF LastModified_Date BETWEEN (@p_datetime_StartDate AND @p_datetime_EndDate) OUTPUT
ELSE OUTPUT ALL.
I want records between the range of date selected, or all the records if no date entered. Can somebody help me ASAP. Here is what I have so far. I just don't know where to put the IF...THEN ELSE.
SELECT Device_ID,
Action,
Username,
LastModified_Date,
FROM ActivityMonitorActionLogs
WHERE Device_ID = ISNULL (@p_int_Device_ID, AL.Device_ID)
AND LastModified_Date BETWEEN (@p_datetime_StartDate AND @p_datetime_EndDate)
Thanks a lot!!!
Mylene
View 3 Replies
View Related
