SELECT Statement

I am getting ORA-01422: Exact fetch returns more than requested number of rows

Hi Experts, I am gettting this error when i run my test case for my PL/SQL procedure ,which hav select and Insert statement only ,not clear where i wud hav went wrong,please guide me where should i check exactly its poping this error message which i catched in exception piece after Insert statement

PL/SQL: How to handle OUT Parameter during INSERT

HI Experts, I have a OUT parameter in my procedure(whihc performs INSERT data into a table),how to handle this OUT parameter (primary key of table) -- Will Insert statement inside the PL/SQL block should include this parameter or we should use SELECT stm after Insert to add this attribute ,i am...

How to save the output from an SQL select statement to a spool file

Please can someone tell me how I can save the output from an SQL select statement to a spool file. Thanks, Tom

Conditional processing in embedded SQL

I'd like to conditionally control the host variable into which the data from an SQL select statement is retrieved. This is for flexibility, so that I can use the same code in my program more than once. Here is a code sample: C/EXEC SQL C+ If :ResultName = 'AgtMaster' C+ Then ...

How can I join 3 tables using JOIN keyword

Hi Experts, Pls hlp me with my query How can i join 3 tables using JOIN Keyword inside PL/SQL block in SELECT ... WHERE clause Query 3 Tables namely Reg,ROOTS,ME and the select statement takes both parameter variable and local variable Thanks!!!!!

Need SQL query

I have two tables DETAIL and USER. Detail table have two columns (projectid and projectdetail). Projectid is a primary key in DETAIL table. USER table have two columns (projectid and Userid). User table is like more than one userid can be under each projectid, one user can access more than one...

SQL select

SELECT obj.object_type FROM apps.opt_log_issue log, discov.opt_changed_database_objects obj WHERE --(UPPER(log.resolution(+)) like '%'||obj.object_name||'%' -- and trunc(log.resolution_date(+)) - trunc(to_date(substr(obj.last_ddl_time,1,11),'dd-mon-yy')) <=5 -- and...

Advantages and disadvantages of certain SQL commands

Where can I find information about the advantages and disadvantages of using SQL commands like BCP, BULK INSERT, SELECT INTO, etc.?

SQL Server trigger , insert result of multiple where clause into a variable

Hi All, I have a select statement that stores the result in a variable,this works fine if there is only one condition and field in the clause.How do I go about it if there is more than one? For example (Working one) SELECT @Variable = (SELECT [Field] FROM [Table] WHERE [FIELD] = Value) But I am...

Outputting results of SQL SELECT statements into a spreadsheet

I am using OPENROWSET to output the result of a number of SQL SELECT statements into a spreadsheet. It works for most of the SELECT statements, but there are 4 random queries out of 82 that take more than an hour to execute. This is the code: insert into...

How do I use the results of count(*)=0

I am using SQL to code a select statement. If the result of the statement = count(*)=0, I want to put a message out on a report.

Crystal Record selection

I need to eliminate duplicate records by comparing two fields, if the data is the same, I only want it to appear once. "Select Distinct" report option compares the complete record. Please help

Accessing another database using a variable

I have a select like this: SELECT a.part_id, a.descript, b.location from PART_MASTER a, anotherdatabase.INVENTORY b where a.part_id = b.part_id Is it possible to use a @variable to identify the other database, rather than hard coding it? (I've tried @db2.INVENTORY b) but SQL SERVER 2000 doesn't...

SQL Server equivalent of Oracle's "when no data found" exception

In a stored procedure, I'm storing the result of a select statement in @variable. Is there a way to capture a SQL Code 100, like Oracle's SQLERRM(100)/ORA-01403 if no records are returned? I'm currently setting @variable = '*' before the select and and IF @variable = '*' afterwards, but there...

SQL Subfile and position to ability

I have a subfile that can be sorted by any of 4 fields, so I want to use imbedded SQL to read the data out of my PF in order to populate the subfile. I want to be able to allow the user to enter position to information, and position to that entry - normally, I would do a SETLL to the file based on...

join tables with nested select count and group by

hi i am having three tables : state, agencydetail, parentdetaills i am running following queries but i am getting wrong result plz help me out where is the problem select state.state,count(ag.agencycode) as registag,count(ff.agencycode) as reportag,count(inp.regno) as...

CASE sintaxis error in SELECT

In the middle of a large storage procedure I have a working SELECT 01 (see below) which at a couple of points uses CASE statement. I need to expand the most inner CASE to get SQL Server check for several values instead of one. Currently CASE I am talking about looks like (it is in the middle of...

SQL: Using a column created in the SELECT in the WHERE CLAUSE

Can I use a column in the WHERE clause that I created with 'AS' in the WHERE clause. I have tried and SQL tells me that the column is not in the table? Thanks, Ray

Calling a stored procedure in a SELECT list on SQL Server 2000

I am using SQL Server 2000, and my question is: is there any way to call a stored procedure in a SELECT list?

Optimizing Query

Hi, I am having a table which is having around 170 Columns(Don't blame me but my Developer) and there are only 989 records in the table.I created all sorts of Indexes but still the query (Select * from Table) takes almost 2 mins to display 989 records.I need all the columns to be displayed.Is there...