Could somebody advice what to do when using listagg leads to varchar2 overflow because of lots of aggregated strings (during aggregation in SQL query via Group etc.) in One field? I use report (It just ONE SQL query) where I aggregate Phone Codes by ZoneName (Country etc.) and some of them have tons of codes for one Zone - so I could ge...
Hi, I want to assign mulitple values into a variable and use that variable in where-clause. For Ex: declare v_filename varchar2(300) := ''('filename1','filename2')''; cnt number; begin select count(*) into cnt from table_name where filename in v_filename; end; Please advise. Thanks, Deepak ...
I have the following queries: SELECT Sites.EDISID, Sites.[Name], (SUM(DLData.Quantity) / 8) AS TiedDispense FROM Sites JOIN UserSites ON UserSites.EDISID = Sites.EDISID JOIN Users ON Users.[ID] = UserSites.UserID JOIN MasterDates ON MasterDates.EDISID = UserSites.EDISID JOIN DLData ON DLData.DownloadID = MasterDates....
Is there a way I can open and read a text file within a .NET project? I know I can use the System.IO.StreamReader class to do it for a Windows text file. I'd like to store some SQL scripts for upgrading my database in text files within my project. Since I'm writing this in VB I can't just paste the scripts directly into my code due to l...
HI, Using Microsoft SQL Server 2005: I have a table "test": id, link_id, name 1 11 test1 2 11 test2 3 11 test3 4 12 test4 Is there a way to query this, and return the results grouped by "link_id", with the names joined? EG, SELECT link_id, name FROM test WHERE ??????? results: link_id, ...
Assuming you have the following database table: create table Names ( Id INT IDENTITY NOT NULL, Name NVARCHAR(100) not null, ParentNameId INT null, primary key (Id) ) create index IX_Name on Names (Name) alter table Names add constraint FK_NameNames foreign key (ParentNameId) references Names This allows the ...
If I run a stored proc using the SqlCommand and the SqlCommand times out does the StoredProc continue to execute or does it get force to quit when the SqlCommand disconnects? ...
I have a query: SELECT exhibitor_participation+0 AS exhibitor_participation_value FROM `exhibitor_registry` WHERE ((exhibitor_participation_value & 1) = 1) Question: Why does it return this:? Unknown column 'exhibitor_participation_value' in 'where clause' Isn't it supposed to be successful since I defined exhibitor_participatio...
ODP.Net exposes the ability to pass Associative Arrays as params into an Oracle stored procedure from C#. Its a nice feature unless you are trying to use the data contained within that associative array in a sql query. The reason for this is that it requires a context switch - SQL statements require SQL types and an associative array p...
I have an .mdf file with Visual Studio 2008 with one table in it that has numerous columns of various types. How can I generate the SQL statement that produces this table so that I can alter it and create another similar table? Is there a way to do this without SQL Server? ...
This seems simple enough: I want to duplicate a row in a SQLite table: INSERT INTO table SELECT * FROM table WHERE rowId=5; If there were no explicit unique column declarations, the statement would work, but the table's first column is declared rowID INTEGER NOT NULL PRIMARY KEY. Is there any way to create a simple statement like the...
I'm trying to sort some data by sales person initials, and the sales rep field is 3 chars long, and is Firstname, Lastname and Account type. So, Bob Smith would be BS* and I just need to sort by the first two characters. How can I pull all data for a certain rep, where the first two characters of the field equals BS? ...
My table (SQL Server 2008) has 1 million+ records, when I try to order records by datetime, it takes 1 second, but when I order by ID (int), it only takes about 0.1 second. Is there any way to improve the efficiency? (I already added the datetime column to the index) ...
I know it's a simple question, but in everything I've read, I've never seen this spelled out specifically. If you do a query on a page, do you need to worry about SQL injection attacks? Or is it only a problem when you ask the user for input? Thanks! ...
We need to write a trigger so that when rows inserted into a sql server table meeting certain conditions, they are then written to a windows OS flat file in CSV format. Are there any commands short of running a xp_cmdshell that would allow us to do this? ...
I have created a database where I use uniqueidentifier as the primary key. I'm trying to run a parametrized query that in the end looks like this. (insert into someTable (uniqueID) Values('76c14693-5475-4224-ba94-7d30c919ac59') (uniqueID is my PK). This insert statement executes without issues when I run it in SQL Server Management Stu...
Hello, I was having issues with an INT field where there may be no value, a zero value, or an integer above zero and since SELECT foo FROM bar where foo = '' evaluates identically to SELECT foo FROM bar where foo = 0 I redefined foo like to foo INT(11) NULL so that rows where no value for foo is supplied do not get selected by e...
I'm trying to model which countries border each other in MySQL. I have three tables: nodes ----- node_id MEDIUMINT countries --------- country_id MEDIUMINT (used as a foreign key for nodes.node_id) country CHAR(64) iso_code CHAR(2) node_adjacency -------------- node_id_1 MEDIUMINT (used as a foreign key for nodes.node_id) node_id_2 ME...
I've got a table of temperature samples over time from several sources and I want to find the minimum, maximum, and average temperatures across all sources at set time intervals. At first glance this is easily done like so: SELECT MIN(temp), MAX(temp), AVG(temp) FROM samples GROUP BY time; However, things become much more complicated ...
I'm trying to search several tables at once for a search term. My query is: SELECT item.ItemID FROM Inventory.Item item JOIN Inventory.Category catR // each item can be in several categories ON catR.ItemID = item.ItemID JOIN Category.Category cat ON cat.CategoryID = catR.CategoryID ...