I need to join an existing table with date entries with a list of months in a given period. For this I would need to temporary generate a list of months within this period. Is there a command in mysql for doing this? Temporary Table, stored procedure? Thx, martin ...
This is the DB Schema: Books (bookid, title, author, year) Customers (customerid, name, email) Purchases (customerid, bookid, year) Reviews (customerid, bookid, rating) Pricing (bookid, format, price) How do I find customers (show their names and email addresses) who purchased more than one book in year 2003? Thanks! ...
I have a query: SELECT ID FROM requests WHERE date <operator> <expression> I need to change <expression> relying on my stored procedure's parameter. If flag is set: WHERE date BETWEEN GETDATE() - 7 AND GETDATE() If is not: WHERE date = date I tried to use CASE-THEN but it forces using concrete operator but I have to have few: SELE...
I'm attempting to rename a stored procedure in SQL Server 2008 with sp_rename system sproc. The third parameter is giving me difficult though and I keep receiving the following error: Msg 15249, Level 11, State 1, Procedure sp_rename, Line 75 Error: Explicit @objtype 'P' is unrecognized. As the message indicates I'm passing in a P fo...
I sense an easier way to do the code. How can I do the deletion/insertion shorter? // to delete the old tags before inserting new tags $result = pg_query_params ( $dbconn, ...
Lets say I have two tables tblA ( tableAID INT IDENTITY(1,1), foo VARCHAR(100)) tblB ( tableBID INTIDENTITY(1,1), tableAID INT, bar varchar(100)) tblB.tableAID is a FK to tblA. I want to insert a bunch of records (pulled from some other table in the system) into this pair of tables. I need to know what the ID from in...
I'm reading up on indexes for optimizing database performance and would like to gather best practices for various database situations in terms of applying indexes. Take a totally non-indexed database with non-trivial amount of tables and rows, what rules do you use in determining what index is applied to what column of what type to achi...
I have a table, tblDocs that has a few columns: DocName varchar(50) DocLocation int Active int DocID int All entries in the table have a DocName and DocLocation. Active and DocID are blank. What I need to do is for each row in tblDocs I need to check the value of DocLocation and based on that value I update tblDocs, setting the Acti...
I'm having an small issue with SQLXML in SQL Server 2008. Here's my testing SQL query DECLARE @XMLTable TABLE(GameHistory XML) INSERT INTO @XMLTable VALUES( '<game xmlns="http://my.name.space"> <move> <player>white</player> <piece>pawn</piece> <start>A2</start> <end>A3</end> </move> <move...
I'm new to Django and still having some problems about simple queries. Let's assume that I'm writting an email application. This is the Mail model: class Mail(models.Model): to = models.ForeignKey(User, related_name = "to") sender = models.ForeignKey(User, related_name = "sender") subject = models.CharField() conversat...
Hello Everyone, Let's say I have two tables one called Customers and one called CustomerAddresses. Then lets say I have a Customer object which contains a property that is of the type List<Address>. This property holds a list of all the different shipping addresses a customer may have used, so it could contain 0 - n addresses in it (j...
The Situation (Ignore this it is boring): I have reports that I created using reporting services. Some of these reports take the parameter, "Month". They enter in an integer for the month they want. Example: December = 12. In order to view the report, I am simply using the Report Viewer in visual studio. I need the month field to be a dr...
I'm using Oracle SQL Developer to query an Oracle DB (not sure which version it is) and I'm going to use the SQL I make for a Crystal report. Many of the reports the previous developers have written don't use JOIN keywords to make the joins (and I'm not too familiar with JOIN keywords as a result). Many of the joins they make are mad...
How to save query result using T-SQL in a csv file in SSMS 2008? Needs to be done programmatically. ...
I have a website that's sort of a Craiglist type app. In my Database, should I create a table called UserRoles, and assign the UserRoleID as a Foreign Key to every user created? 1 for Admin(Complete Priveledges), 2 for Moderator, 3 for Normal User, etc. Also, inside of my ASP.Net application, say I have a UserControl. Inside of that us...
Some days ago I started using partitioning to make it possible to work with large amount of data. I created master table as documentation suggest, created some child tables which inherit from the master one and then added a constraint for each child table to define the allowed key value. Constraint was like: CHECK (site_id = 'google.co...
how can I accomplish: select * from table where column_value is int I know I can probably inner join to the system tables and type tables but I'm wondering if there's a more elegant way. Note that column_value is a varchar that "could" have an int, but not necessarily. Maybe I can just cast it and trap the error? But again, that se...
There's a database engine that looks amazing for a free tool and that is Scimore. Have you had much experience with it? If so, how does it rate..particularly against Firebird? How resilient/self reliant is it? (Meaning how much downtime/maintenance is expected?) The scale out capabilities also look very interesting. I just downloade...
I have a table (t) with a column (c) defined as varchar 20, containing the values 'a', 'b' and 'c' If I execute the following sql, a value is returned: select * from table where c = 'a ' Why? I assume it's casting/trimming the parameter in some way, but why? How would I really search for 'a ' ...
If I have this stored proc definition minus the body ALTER PROCEDURE sp_AlloctionReport(@where NVARCHAR(1000), @alldate NVARCHAR(200), @alldateprevweek NVARCHAR(200)) AS And I call like this. sp_AllocationReport "ProductCode = 'FA' AND AllocationDate = '20090112' AND VenueInfo.VenueID In(SELECT vf.VenueID FROM VenueFilters vf INNER J...