hello
my friends
i want write Select without write filed name
example
mytable(id,name1,family)
orginal selected is
select id,name1 from mytable
but i want write:
select field[0],field[1] from my table
that field[0] point to id and field[1] point to name1
thanx
M.O.H.I.N
There must be a better way to write this where clause. Can somebody help
if @id is null
begin
(bla bla bla) where parentid is null
end
else
begin
(bla bla bla) where parentid = @id
end
Hello,While working through my encryption questions from preivous posts, I amfinding that I may have to resort to use triggers to do the encryption(not that this is the only way, but might be the best way for mycircumstances).I would like to create a trigger that will encrypt the field before thewrite is committed.I've found serveral posts about triggers, but nothing that fits what Iwish to do.Upon an insert or update I want to modify the ssn field with this:cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN asnvarchar(11))) as nvarchar(40))so, ssn '123456789' in SSN would become <something encrypted> in SSNThis is the trigger I have so far, but it is generating an error:CREATE TRIGGER F_Student_SSN.eSSNON F_STUDENT_SSNINSERT, UPDATEAS SELECT cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN asnvarchar(11))) as nvarchar(40))TIARob
View 3 Replies View RelatedI need some help in SSIS Package I am trying to write a byte array to an image (blob) in DB2 destination. I am getting SQL code -290 Invalid Description, if i set the output column to a byte stream. If I set the output column to an Image data type then I get a different error the package will not at that time even process it errors out right away. At least using a byte stream datatype it errors out when it is about to write to the olebd destination. Anybody have success using BLOB fields in SSIS package data flow? Thanks for any help.
View 5 Replies View RelatedI have a table with a column [FileData] of type TEXT or XML. I need to write the content of the column to a file, using sp_OAWrite for example, but the stored procedure who needs to read the content and write it to the file does not work because the limitation is no local variable for those types. What use is that datatype if i can not use it in procedures like this?
Anyone out there who could give me a tip on how to solve that?
I have a web form that is an interface for a database. The code was working fine until a field needed to be added. So I added the new field, updated the data adapter and data set, and when i enter the data into the field, all of the old fields are getting updated, but the new ones aren't. I am at wits end as to why. Can someone please help!!! I am using VS Studio 2000 ASP.NET and C#. The code is below. The bold items are the new fields.using System;using System.Collections;using System.ComponentModel;using System.Data;using System.Drawing;using System.Web;using System.Web.SessionState;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls;namespace TrafficDept{/// <summary>/// Summary description for AddOwner./// </summary>public class AddOwner : System.Web.UI.Page{protected System.Web.UI.WebControls.Button Button1;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator3;protected System.Data.SqlClient.SqlConnection sqlConnection1;protected System.Web.UI.WebControls.TextBox TextBox1;protected System.Web.UI.WebControls.TextBox TextBox2;protected System.Data.SqlClient.SqlDataAdapter daAddOwner;protected System.Web.UI.WebControls.TextBox tbOtherOwnerLName;protected System.Web.UI.WebControls.Panel namePanel;protected System.Web.UI.WebControls.TextBox tbOtherOwnerMI;protected System.Web.UI.WebControls.TextBox tbOtherOwnerFName;protected System.Web.UI.WebControls.TextBox tbSecondOwnerLName;protected System.Web.UI.WebControls.TextBox tbSecondOwnerMI;protected System.Web.UI.WebControls.TextBox tbSecondOwnerFName;protected System.Web.UI.WebControls.Button Button4;protected SiteCubed.EditWorksProfessional tbNote;protected System.Web.UI.WebControls.Panel notePanel;protected System.Web.UI.WebControls.DropDownList ddlStatus;protected PeterBlum.PetersDatePackage.DateTextBox tbRegDate;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator2;protected System.Web.UI.WebControls.TextBox tbOwner;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;protected System.Web.UI.WebControls.TextBox tbOwnerMI;protected System.Web.UI.WebControls.TextBox tbOwnerLName;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator4;protected System.Web.UI.WebControls.TextBox tbBusinessName;protected System.Web.UI.WebControls.TextBox tbAddress;protected System.Web.UI.WebControls.TextBox tbRegNo;protected System.Web.UI.WebControls.TextBox tbPreviousOwner;protected System.Web.UI.WebControls.TextBox tbPOwnerAddress;protected System.Web.UI.WebControls.TextBox tbPORegNo;protected PeterBlum.PetersDatePackage.DateTextBox tbTransferDate;protected System.Web.UI.WebControls.DropDownList ddlMeans;protected System.Web.UI.WebControls.Button Button3;protected System.Web.UI.WebControls.Button Button2;protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;protected System.Data.SqlClient.SqlCommand sqlInsertCommand1;protected System.Data.SqlClient.SqlCommand sqlUpdateCommand1;protected System.Data.SqlClient.SqlCommand sqlDeleteCommand1;protected TrafficDept.dsAddOwner dsAddOwner1; private void Page_Load(object sender, System.EventArgs e){// Put user code to initialize the page here}#region Web Form Designer generated codeprivate void Button2_Click(object sender, System.EventArgs e){Response.Redirect("AddOthers.aspx");}private void Button1_Click(object sender, System.EventArgs e){daAddOwner.Fill(dsAddOwner1);dsAddOwner.OwnershipRow drOwner = dsAddOwner1.Ownership.NewOwnershipRow();drOwner.Plates_ID = int.Parse(Session["PlatesID"].ToString());drOwner.Status = ddlStatus.SelectedItem.Text;drOwner.Current_Legal_Owner = tbOwner.Text;drOwner.Owner_CurrentLegalOwnerMI = tbOwnerMI.Text.ToString();drOwner.Owner_CurrentLegalOwnerLName = tbOwnerLName.Text;drOwner.Owner_CurrentBusinessName = tbBusinessName.Text;if (!tbRegDate.xIsEmpty)drOwner.Date_of_Registration = DateTime.Parse(tbRegDate.Text.ToString());drOwner.Address_of_Current_Owner = tbAddress.Text;drOwner.Reg_No_for_Current_Owner = tbRegNo.Text;drOwner.Previous_Owner = tbPreviousOwner.Text;drOwner.Previous_Owner_Address = tbPOwnerAddress.Text;drOwner.Reg_No_for_Previous_Owner = tbPORegNo.Text;if (!tbTransferDate.xIsEmpty)drOwner.Date_of_Transfer = DateTime.Parse(tbTransferDate.Text.ToString());drOwner.By_Means_of = ddlMeans.SelectedItem.Text;drOwner.Owner_SecondaryLegalOwnerFName = tbSecondOwnerFName.Text;drOwner.Owner_SecondaryLegalOwnerMI = tbSecondOwnerMI.Text;drOwner.Owner_SecondaryLegalOwnerLName = tbSecondOwnerLName.Text;drOwner.Owner_OtherLegalOwnerFName = tbOtherOwnerFName.Text;drOwner.Owner_OtherLegalOwnerMI = tbOtherOwnerMI.Text;drOwner.Owner_OtherLegalOwnerLName = tbOtherOwnerLName.Text;drOwner.Owner_Notes = tbNote.Text;dsAddOwner1.Ownership.Rows.Add(drOwner);daAddOwner.Update(dsAddOwner1);Response.Redirect("AddOthersTP.aspx");}private void Button3_Click(object sender, System.EventArgs e){//daAddOwner.Fill(dsAddOwner1);//dsAddOwner.OwnershipRow drAddOwner = dsAddOwner1.Ownership.NewOwnershipRow();namePanel.Visible = true; }private void Button4_Click(object sender, System.EventArgs e){notePanel.Visible = true;}}}
View 1 Replies View RelatedHi I have a table named UserMaster having a column Sex for male and female.Sex MaleMaleFemaleFemaleFemale I want to replace all Male with Female and all Female with Male with a single query. Can any one please help me out
View 4 Replies View RelatedHello All.
You may find below script stupid to you. I am trying to create a table with week number that is not fixed, i.e. March 2006 may have 4 weeks and April 5 weeks based on our company calendar.
And Field Names could be Grp, SubGrp, Week_10, Week_11, Week_12, Week_13 depending on the week being stored in TempWeekFile. We could start the month having only Week 10 and following week with Week 11 added into TempWeekFile.
My below script shows error. I have following records in my TempWeekFile
Week_No WeekCnt
10 1
11 2
12 3
13 4
How to write a script that do the above? Has anyone done this using a better method? Please advise. Thank you.
---------------------------------------------------------
if exists (select * from information_schema.tables where table_name='WeeklySalesToThird_Month')
drop table WeeklySalesToThird_Month
Declare @WeekCount int
Declare @Cnt int
Declare @WeekNo varchar(2)
Select @WeekCount = count(*) from TempWeekFile
Select @WeekCount
Select @Cnt=1
CREATE TABLE [dbo].[WeeklySalesToThird_Work_Month] (
[Grp] [varchar] (30) NULL ,
[SubGrp] [varchar] (30) NULL ,
while @Cnt<=@WeekCount
Begin
select @WeekNo = Week_No from TempWeekFile
where WeekCnt=@Cnt
[Week_@WeekNo] [Money] NULL
@Cnt=@Cnt+1
End
) ON [PRIMARY]
GO
--------------------------------------------------------
I want to write a select statement that will show only distinct Student IDs where every record in the table with that student id has a grade above 80?
Lets say for example the table looks like this:
Student_ID
Grade
11276
93
56879
81
11276
76
44327
92
11276
84
56879
97
56879
82 The select statement should only show students, 56879 & 44327 because student 11276 made a 76 in one class.Anyone know how to word this select statement?Thanks for any help!
I'm running a select statement from within my VB.net code. However, if the software package name that I chose from a drop down list in my form is not available in the search (in the TableColumn called Software.Package_Name), I would like to be able to write the message "Record Not Found" to the Package_Name "ListBox" column.
Here's the select statement:
"SELECT n.Workstation_Name , s.install_status , s.install_date , s.name , s.version , s.build , s.Package_Name , n.city , n.state FROM software s , network n where ( s.mac=n.mac AND n.Workstation_Name='B001617CA709E' ) AND ( s.name='Adobe Reader' AND s.Install_status='1') ORDER BY n.Workstation_Name ASC"
My ListBox control is named lstQueryResults and uses a Structure that is one to one with each row of the lstQueryResults control.
i.e.
Public Class LIST_CTRL_DATA
Public WksName As String
Public InstallStatus As String
Public InstallDate As String
Public PackageName As String
Public AppName As String
Public Version As String
Public Build As String
Public City As String
Public State As String
Public Percent As UInteger
Finally, here's the While..End routine that plugs the data into the ListBox called lstQueryResults.
While sqlReader.Read()
currec += 1
Dim msg As THREAD_MESSAGE
msg.msgid = 101
Dim message As New LIST_CTRL_DATA
message.WksName = sqlReader("Workstation_Name").ToString
message.InstallStatus = sqlReader("Install_Status").ToString
message.InstallDate = sqlReader("Install_Date").ToString
message.AppName = sqlReader("Name").ToString
message.Version = sqlReader("Version").ToString
message.Build = sqlReader("Build").ToString
message.PackageName = sqlReader("Package_Name").ToString
message.City = sqlReader("City").ToString
message.State = sqlReader("State").ToString
message.Percent = (currec * 100) / maxrecs
msg.msgdata = message
m_ParentWindow.Invoke(m_NotifyMainWindow, msg)
End While
Any ideas on whether or not you can write an If..Then Else statement within the SQL Select Statement and if so how?
I'm still somewhat new to SQL. Any assistance would be great.
Regards,
NBK
I create a table that is named T1(C1,C2) and insert some data,as following:
C1 C2
1 1
4 NULL
NULL 5
7 NULL
NULL 9
12 12
14 NULL
NULL 16
I want to write a select statement to obtain the following result:
C1 C2
1 1
4 5
7 9
12 12
14 16
how to write the select statement?
I want in my query to select a different field in case another one is null. in mysql i'd do it like this:
select
a
,if(b is null, c, b)
,d
from
alphabet
how can this be done in sql server?
thanks
I have a report that uses different datasets based on the year selected by a user.
I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.
I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.
How could I display data from the dataset a user selects via the year_id options?
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)
SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
I have a requirement like below .
Input table will be like below:
eventdata:
accountID deviceID timestamp speedKPH address
--------- -------- ---------- -------- -------------
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359088796 0 Ullalu Road
preva1 bolero 1359088886 0 Ullalu Road
preva1 bolero 1359088888 8.47 Ullalu Road
preva1 bolero 1359088986 0 Ullalu Road
preva1 bolero 1359088988 45 Ullalu Road
preva1 bolero 1359088996 21 Ullalu Road
preva1 bolero 1359088998 0 Ullalu Road
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359089009 12 Ullalu Road
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359089016 0 Ullalu Road
preva1 bolero 1359089026 0 Ullalu Road
So here i need output table like below:
stoppagedetails:
accountID deviceID from_timestamp to_timestamp diff
--------- -------- ---------- -------- -------------
preva1 bolero 1359088796 1359088888 92
preva1 bolero 1359088986 1359088988 2
preva1 bolero 1359088998 1359089006 8
preva1 bolero 1359089016
How to write mysql query for the above requirement.
col1 col2 col3 col4
36930.60 145 N . 00
17618.43 190 N . 00
6259.20 115 N .00
8175.45 19 N .00
18022.54 212 N .00
111.07 212 B .00
13393.05 67 N .00
In above 4 col
if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
here col2 values are unique.
Can any one reply for this..............
Code:
SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS +
AUG_CURR_CREDITS + AUG_CURR_DEBITS +
SEP_CURR_CREDITS + SEP_CURR_DEBITS +
OCT_CURR_CREDITS + OCT_CURR_DEBITS +
NOV_CURR_CREDITS + NOV_CURR_DEBITS +
DEC_CURR_CREDITS + DEC_CURR_DEBITS +
JAN_CURR_CREDITS + JAN_CURR_DEBITS +
FEB_CURR_CREDITS + FEB_CURR_DEBITS +
MAR_CURR_CREDITS + MAR_CURR_DEBITS +
APR_CURR_CREDITS + APR_CURR_DEBITS +
MAY_CURR_CREDITS + MAY_CURR_DEBITS +
JUN_CURR_CREDITS + JUN_CURR_DEBITS) as CURR_AMT
I need to sum these amounts running from July to the month prior to whatever the current month is. So if it was August, it would only be
Code:
SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS) as CURR_AMT
Is there a cleaner (shorter) way to iterate through the twelve months than either writing the query 12 times in an IF statement, or 12 CASE statements? This is only part of a query that joins several tables (not shown).
Any suggestions on the best way to write this would be valued.
I am trying to write a stored procedure that is an INSERT INTO <sql table> FROM <other sql tables> WHERE <where clause>.
I need to insert 3 columns from multiples SQL tables into one SQL table. Here is my code snippet:
ALTER PROCEDURE [dbo].[sp_insert_test]
@MID INT OUTPUT,
@CIDINT OUTPUT,
@MemberNVARCHAR OUTPUT
[Code] ....
i cannot pass any of the values to the tblVotings table and not sure how to enter the @MID, @CID and @Member to the INSERT INTO statement.
i want to select the values of column passed from the user like
connectionsizein or connectionsizemm
how can i do
i am using sql server 2005
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns
ContactID
I need some help.I am trying to write a query which does the followingSELECT * from table1 where field1=(SELECT distinct field1 FROM table1WHERE field2='2005' or field2='2010')I need all the values from table1 which match any value from field 1from the subquery.Any help is appreciated.thanks
View 4 Replies View RelatedCan Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!
View 6 Replies View RelatedI have 3 columns in my SharePoint list. I wasn’t to select the max date for a particular condition. How to write it in CAML?So from the below table I want to get the max date for column 1 when the value is A or B
View 3 Replies View RelatedBasically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.
Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)
Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0
What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0
I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.
I would like to query a table for a max value of one field for a distinct combination of two other fields. Let's call these fields RowID, ObjectID, and ObjectType. RowID is an auto-increment field, so for each distinct combination of ObjectID and ObjectType, there will be many values of RowID. To visualize an example:
RowID, ObjectID, ObjectType
1 , 1 , 1
2 , 1 , 2
3 , 1 , 3
4 , 1 , 1
5 , 1 , 2
6 , 1 , 3
Of these rows, I would only want 4, 5, and 6 (max values for distinct combination of ObjectID and ObjectType).
I hope I explained this clearly. I would imagine I'd need to use some form of nested query, but nothing I have tried so far has worked. I am using SQL 2005.
Thanks!
I have the linked server connection created and works perfectly well. I mean I am able to see the tables while I am on my database.
how do I write a SQL statement to reference the linked server ?
I tried the following:
Select top 100 * from casmpogdbspr1.MPOG_Collations.dbo.AsaClass_Cleaned
Then I get the error message....
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "casmpogdbspr1" does not contain the table ""MPOG_Collations"."dbo"."AsaClass_Cleaned"". The table either does not exist or the current user does not have permissions on that table.
Hi,
I've got a question!
In a database I have a field interest-target with values like 8,15,115,3 and 18,13,15,6 and 51,6,7,118 etc.
Now I like to select from these fields the value 8. I tried the following:
SELECT * FROM TABLE WHERE FIELD LIKE '%8,%' OR FIELD LIKE '%,8,%' OR FIELD LIKE '%,8%'
I will the also get the value 18 wich I don't like :-(
Any solutions here?
Thanks!
Roel
I want to select only the descriptions from a table that have a ' in them. Anyone know how to do this?
exp: Select * from SkuMaster where Description like '%'%'
I have tried all kinds of combinations with no luck.
Is there a way in SQL Server to query for a character that is in a field? ... I have this old table before there was form validation, and I want to get the addresses out of the column that actually have an @.
I want to do something like this.
SELECT
email
FROM
xyz
WHERE
email contains '@'
AND
email contains '.'
any ideas? Will this work?
Thanks,
-L
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
Hi,
Can someone tell me if it is possible to do an SQL insert with a select (to copy specific records) query and specify the value for a specific field to insert in the new records instead of using the value in the field in the select statement.
If so can you provide me with a simple example.
Cheers
Mark :)
I've a select SELECT Field1, Field2, Field3 FROM Tablethe problem is that I can have filled Field1 OR Field2 ... I would like to create a new column in the result for see only the filled field if Field1 is = null or = ' ' I want to see Field2 if Field2 is = null or = ' ' I want to see Field1
View 2 Replies View RelatedHow would I go about selecting records where a datetime field is >= 1hr?
View 2 Replies View Related