SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database. I have a very simple dilemma. In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields. It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table. In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters. The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%') If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null> My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>. For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null> (accounting for about 95% of the records in the database). Thanks in advance Simon
I want to do something similar to ExecuteScalar in ADO.net but instead in T-SQL.Basically I want to do a query that will return the first value in the table queried and put it into a variable. How do I do this?Thanks in advance.
I am trying t get output for the following querry but I know am missing something. Can anyone help me out with it.
select distinct productnum, (SELECT SUM(quantity) FROM orderlineitem w WHERE w.productnum = e.productnum) as Quantity from orderlineitem e where parentOrderlineitemid is null order by Quantity desc)
In the above query am getting the productnum and quanity and it looks like this
productnum quantity abc 6 ttt 3 sss 1
What am tring to do to this query is that . From another table 'product' i want all the data to be retrieved with this productnum(the table 'product' has a column called prductnum). I don't know how to write a query for this.
my query is select * from product where productnum in ( select distinct productnum, (SELECT SUM(quantity) FROM orderlineitem w WHERE w.productnum = e.productnum) as Quantity from orderlineitem e where parentOrderlineitemid is null order by Quantity desc)
here is my table: MVPTABLE columnName: Accsno JanMail JanVisit JanPhone JanComments FebMail..upto DecMail DecVisit DecPhone DecComments.
eg: table with values: Accsno JanMail JanVisit JanPhone JanComments FebMAil FebVisit A234 1 2 3 yes jim 0 2 A234 0 2 0 No Comments 1 2 As34 0 0 0 No Comments 1 2 A235 1 2 3 yes jim 0 2 A235 0 2 0 No Comments 1 2 As35 0 0 0 No Comments 1 2
am sending 2 parameter as 1> @param1= A234 2> @param2= 'JanMail,JanVisit,JanPhone,FebMail,FebVisit,FebPhone,MarMail,MarVisit,MarPhone,AprMail,AprVisit,AprPhone,MayMail,MayVisit,MayPhone,JunMail,JunVisit,JunPhone,JulyMail,JulyVisit,JulyPhone,AugMail,AugVisit,AugPhone,SepMail,SepVisit,SepPhone,OctMail,OctVisit,OctPhone,NovMail,NovVisit,NovPhone,DecMail,DecVisit,DecPhone
based on these 2 parameter i wanted to retrieve respective Comments say, if @param1 Ac234 @param2= JanMail,JanVisit,JanPhone,MarMail if the value of JanMail or FebMAil....DecMail = 1,then retieve respective comments if the value of JanVisit,FebVisit..........DecVisit=2,then retrieve respective comments if the value of JanPhone,or FebPhone,.....DecPhone=3 then retive respective comments
Hi, how can i retrieve 10 fake rows from any table? for examle for a row it will seem like that.. Id Name Value Status----- ----------- ----------- ---------------1 null null null thanks in advance..
I'm writing a trigger for my DotNetNuke portal that requires me to read the value of a just inserted record. I believe I'm doing this right, still I'm unable to retrieve the desired value from the Inserted table. What am I doing wrong? Here is my code:
CREATE TRIGGER tr_addEditorRole ON [dbo].[UserPortals] AFTER INSERT AS Declare @Portal int set @Portal = (select PortalId FROM inserted)
Declare @TabId Int set @Tabid = (select TabID from Tabs where Tabs.PortalID = @Portal and Tabs.TabName = 'MyTab')
Declare @ModuleId int set @ModuleId = (SELECT ModuleId FROM Modules WHERE Modules.TabID = @TabId and Modules.ModuleTitle = 'MyModule')
I am more familiar with writing SQL for Oracle than MS SQL Server. Within Oracle there is a simple command, 'Describe', which actually shows the data types, and whether or not an attribute accepts NULLS. MS SQL Server does appear to support such a command, however I am looking for a way to describe the attributes of tables nonetheless.
I am having a big problem, trying to create datebase. I have 3 tables: SUPERAVATAR, MASTERAVATAR, MEGAAVATAR. - SuperAvatars are heroes in an online role playing gaming. They have an ID, ‘superavatarID’ which contain an UNIQUE NUMBER NOT NULL PRIMARY KEY to identify them. A wisdom – ‘trickster’,’conjuror’,’magician’, etc.. A current owner… who is the user or player of the game and has that Super Avatar– we associate the link to USERID to know the person. SuperAvatars can be fathers or mothers of Mega Avatars. -MegaAvatars are the children of SuperAvatars…. They also have an ID UNIQUE NUMBER NOT NULL PRIMARY KEY to indentify them. A magic power – it can be a ‘Leader’ or ‘Sheep’… A ‘parent’ – parent is the number identifying to know to who Super Avatar belongs. e.g. SUPERAVAT WISD CURRENTOWNER FATHEROF MOTHEROF 1 Thick 3 1 2 Mentally Ch. 11 3 3 Smart 9 2 4 Genius 16 4 5 Thick 19
-We see that MEGAAVATAR 1 has a magic power as Magician and his father is ‘1’. ‘1’ identifies the SUPERAVATAR. We see SUPERAVATARID…. who has the number ‘1’? the first in the row… who has wisdom – thick and he belongs to the USER with ID number 3. -We see MEGAAVATARID… we choose the number 2…. His magic power is as WIZARD… and his father is the number 3. We see SUPERAVATARID now… we look up the SuperavatarID 3…. We can see he has a wisdom – GENIUS… who belongs to the USERID 16 and he is father of MEGAAVATAR number 2. The list can carry on and never stop.
I have this Problems: We create in this example 3 tables: Users, SuperAvatar, MegaAvatar
SQL CREATE TABLE users(userID NUMBER CONSTRAINT pk_user PRIMARY KEY,email VARCHAR2(50) NOT NULL UNIQUE,password VARCHAR2(15) NOT NULL UNIQUE,subscription CHAR(8) NOT NULL CHECK (subscription IN('ACTIVE' , 'INACTIVE' ) ) );
CREATE TABLE superavatar(superavatarID NUMBER CONSTRAINT pk_superavatar PRIMARY KEY, wisdom VARCHAR2(19) NOT NULL CHECK (wisdom IN ('THICK', 'MENTALLY CHALLENGED', 'AWAKE', 'SMART', 'GENIUS')), currentOwner NUMBER NOT NULL, fatherOf NUMBER,motherOf NUMBER);
CREATE TABLE megaavatar (megaavatarID NUMBER CONSTRAINT pk_megaavatar PRIMARY KEY, magicPower VARCHAR2(12) NOT NULL CHECK (magicPower IN('TRICKSTER','CONJUROR','MAGICIAN','WIZARD','SORCERER')), parent NUMBER);
Now, the 3 tables are created….. What happen when we try to insert values to this table? In this case we insert to User Table some examples: INSERT INTO users VALUES('3','john@hotmail.com','great78','ACTIVE'); INSERT INTO users VALUES('9','chrisandsandra@gmail.com','chrisandra)','ACTIVE'); Now, we insert to SuperAvatar some examples; INSERT INTO superavatar VALUES('1','THICK','3','1',''); INSERT INTO superavatar VALUES('3','SMART','9','3',''); | | ‘9’ is the UserID that we already insert to the User table What’s the problem? We have to insert manually the data from USERID to CURRENTOWNER as we didn’t match or link CURRENTOWNER from SUPERAVATAR Table to USERID from USER Table with a SQL CODE. What happen if we have thousands of USERS that they register to this game…? We will never know to how belongs that SUPERAVATAR but if someone do it manually can spend a year.
I am trying to fix this problem …. I add in SUPERAVATAR TABLE ‘CHECK’ in currentOwner..
SQL> CREATE TABLE superavatar (superavatarID NUMBER CONSTRAINT pk_superavatar PRIMARY KEY, wisdom VARCHAR2(19) NOT NULL CHECK (wisdom IN ('THICK', 'MENTALLY CHALLENGED', 'AWAKE', 'SMART', 'GENIUS')), currentOwner NUMBER NOT NULL CHECK (currentOwner IN(SELECT userID FROM users)), fatherOf NUMBER, motherOf NUMBER); * ERROR: ORA-02251: subquery not allowed here
Hi! I needto find the last record that has been inserted on a table. If I perform a SELCT * FROM mytable , will I receive the data based on the inserted order (natural order)? In that case i'll simply need to check the last record of the results I got in my application to find the last inserted one? (no I am not looking for an identity number ^^)
There is a table with a single column with 75 rows - 50 unique / 25duplicates. How would pull back a list of the rows that have/areduplicates?This is a question that I got in an interview. I didn't get it,obviously....Thanks,Tim
Hi Forum,I have a split function.CREATE FUNCTION dbo.Split2(@List nvarchar(2000),@SplitOn nvarchar(5)) RETURNS @RtnValue table (Id int identity(1,1),Value nvarchar(100)) AS BEGINWhile (Charindex(@SplitOn,@List)>0)Begin Insert Into @RtnValue (value)Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) ReturnEND Courtesy p2p.wrox.com It returns a TableHow to take the return into a temporary table?I want it inside an SP.Because i have a varchar field named occasionTime (varchar) in a tablea sample data inside it is "January,21,EveryWeek"In my SP i have to get either "January" and compare it to something or "21" or "EveryWeek" for comparison.How to do it?Regards,Naveen
Create Table UserStatus ( UserId int, Date datetime, StatusId int )
Create Table Status ( StatusId int Status nvarchar(50) )
A user will go through all Status one by one. (1) Registered -> (2) In progress -> (3) authorized. Now I want to know which users are in progress (2) but a simple select statement like:
Select LastName from Users Inner Join Users.UsersId = UserStatus.UserId Where UsersStatus.StatusId = 2
Will not return the wanted records because all authorized Users have been in this status.
I hope you understand the problem and can help me out.
Hi I am trying to get the last ID value of a table. The following code worked when a SQLConnection object was used, how do I achieve the same result using an SQLDataSource? this.sqlSelectCmd2 = new System.Data.SqlClient.SqlCommand("SELECT IDENT_CURRENT('Assets')",sqlConnection1); sqlConnection1.Open(); int nID = Convert.ToInt32( this.sqlSelectCmd2.ExecuteScalar() ) ; nID++; dc.FocusedRow["ID"] = nID.ToString(); Cheers Pen
Hi, Can any one help me to retrieve an image data from a table. I wanted to show the images on my web page using ASP. I am trying with pub_info table of pubs database, in this table logo is a image data type colum. I opened a record set in ASP page and I am not able to retrieve the image from the record set.
What would be the most efficient method of constructing a sql statement to retrieve certain record into (this gets tricky) rows of a table with 3 columns date.desc.
I have 4 tables I am pulling data from. Table A and B can be joined and Table C and D can be joined via foreign keys but A and/or B can not be Joined to C and/or D via foreign Key.
My statement basically looks like this to start
SELECT s.HTENANT, s.HPROP, s.HUNIT, s.SHEADNAME3B, s.DTOT7I FROM H8summ S join tenant t on s.htenant = t.hmyperson
And returns this :
29226 128 20577 Almukhtar, Khadijah M 4242.00 26574 128 17980 Archie, Mary 20218.00
The last number is the key it is the income.
In another table we have an income limit table setup that would have the low, medium and high settings for the range.
For instance it may have 11400 as the elimit attribute and say 22000 as the vlimit attribute.
Then in the fourth table we the description that can be linked back to the income limit table via the hinclimit attribute. In this case the desc attribute would be ELI for elimit and VLI for vlimit.
So I want to take the income compare it to the income limit table and then return the description
The output would look like this : 29226 128 20577 Almukhtar, Khadijah M 4242.00 eli 26574 128 17980 Archie, Mary 20218.00 vli
I just not sure how to reference the income limit table without the foreign key in the from statement. When I just add it it returns 6 rows for each person (number of rows on the income limit table)
I want to know the status of an index whether it is corrupted or it needs to be reindexed etc ?
I used the sp_MShelpindex tablename stored procedure to retrieve the status also. In the status column it is showing as 0,2 and 2000 values . what are these values ?
I have looked on google and haven't found a query (as of yet) to perform this function.
Essentially I am using VB.NET with Excel and have a mapping between a worksheet and a table in my database. I wrote an import function to pull the data out of excel and put it into SQL Server but I want to try catching errors before i do that.
What is the SQL query to get column sizes from a table. Meaning in a table I have column1 that is allowed a size of int(5). How do i retrieve that information from a query opposed to just looking at it in SQL Server EM??
I need help to build a query that shows me how many columns inside a range on columns are null. Example: quantity1;quantity2;quantity3;quantity4;quantity5; quantity6;quantity7;