CompShack.com - SQL & PL/SQL

Ora: Toad

Thu, 05 Apr 2012 07:19:57 +0000

Hi,

I am using TOAD version 10.
but when am execute to queries in TOAD err like this

"ORA-00900: invalid SQL statement"

Please assist any one.

Regards
Ravi

Datastage interview questions

Sat, 03 Mar 2012 13:35:59 +0000

What is the stage from which you can write unix command on datastage

One sequential file having 100 rec output need to be in 5 different files 1 rec- 1 file 2rec-2file... again repeat what stages you use and explain

Two sequential files with common coulmn but different values output need to club all of them into single sequential file

One file consists of four columns with sno and marks output need to be avg of marks based on roll no

In a sequential file how do you eliminate header and footer and read the middle data

What is use of pool in the configuration file

oracle oci.dll error

Wed, 14 Dec 2011 09:17:21 +0000

i am getting oracle oci.dll could not be loaded only on 64bit
pl give me solution for this

thanks
ramkumar sharma

sql

Wed, 14 Sep 2011 06:11:39 +0000

insert into flag_cust
(CID,
LAST_NAME,
FIRST_NAME,
M_INIT,
ADD1,
ADD2,
CITY,
STATE,
ZIP,
DOB,
SEX,
ACCT_STATUS,
HH_INCOME,
FICA_SCORE,
NEW_IND,
SURROGATE_CID)
SELECT
MK.*
,CASE WHEN MK.new_IND = 'n'
THEN DW_SEQ.NEXTVAL
Else null
END as surrogate_cid
FROM
(SELECT
c.CID,
LAST_NAME,
FIRST_NAME,
M_INIT,
ADD1,
ADD2,
CITY,
STATE,
ZIP,
DOB,
SEX,
ACCT_STATUS,
HH_INCOME,
FICA_SCORE,
case when C.cid is null
THEN 'y' else 'n'

Update statement to change SSN(Social Security Number) no in PeopleSoft:

Tue, 14 Jun 2011 13:23:31 +0000

Hi Folks,

Can anybody help me what will be the update statement for SSN no format change.I am giving you the example in below:
Existing SSN in PeopleSoft--- 830-75-6027.

SSN should be updated to 000-75-6027.

kindly help me to write the update statement on it.
Table name: PS_PERS_NID

Thanks in advance.

How to insert values into table based on condition from another table

Wed, 04 May 2011 06:38:16 +0000

Hi,

Can anyone help me in inserting a value into table based on values from another table.

for Example
____________________________________________________________

i want to insert value "a" if value a particular value in the other table is "c" and i want to insert value "b" if value in the others table is "d"

i hope i am not making it too complex

Thanks in advance!!!

Querying long char filed

Hari_aindian — Mon, 27 Dec 2010 09:04:13 +0000

Hi,

I need to query the PSSQLTEXT defn record by having the condition SQLTEXT like '%recname%';

how to accomplish this in Oracle. In DB2 i did it many times but in oracle this is not running.

Thanks,
Hari.A

Update Error - Single-row subquery returns more than 1 row

westendorf — Thu, 21 Oct 2010 19:39:29 +0000

I'm working on an update. I am first getting the current data (NOW) and then comparing it to a saved file (LAST). I need to update this file which is called ps_uni_textbk_skew with the values from the NOW table for several fields for the keys that match the NOW table to the ps_uni_textbk_skew table. There are 5 keys (SUBJECT, CATALOG_NBR, CLASS_SECTION, STRM, SSR_TXBDTL_SEQNO) . This query works great if you remove the update stuff. I get 2 rows. But once I started adding the update around the query, it doesn't work anymore.

SQL Question on Full Outer Join

Sat, 16 Oct 2010 14:54:43 +0000

Hi, I am writing a sql select statement. First, I need to get all courses and if they have textbooks, include the textbooks. (this is my new data extract). That's the first select and it works great. Then I need to take this file and do a full outer join with another file. (this is my previous old data extract, it does contain some extra fields, so not the exact same table layout). I should get a mixture of some records are only in the first select, others in both files and some only in the file I'm joining with.

Compare too rows and insert the changes into new record in Oracle10g

Thu, 30 Sep 2010 13:33:01 +0000

Hi All, Please help me in writing a query to compare two rows of a table and insert the changes(i.e., changed columns) into another table.

How to apply patches and update Oracle 10g database

dkarthik14 — Thu, 05 Aug 2010 06:16:50 +0000

Kindly tell me step by step how to apply new patches in oracle 10g database

Help me with this SQL

krispsoftpro — Thu, 17 Jun 2010 06:26:16 +0000

Hi I have a table called EMPDET with Emplid and Deptid as keys and values are as shown below.

Emplid Deptid Desc
1 A TEST
2 A TEST
3 A ABC
4 A TEST
5 B XYZ
6 B XYZ
7 B xyz
8 C TEST2
9 C STH
10 C TEST2
11 D STG
12 D STG
13 D STG

I need to write a query which will fetch below results. It should pick only dupilcate combination

Expected results:

A TEST
A ABC
C STH
C TEST2

Thanks,
Kris

i want write curser to get information from diff tables with table name and what info is feed for that field

Wed, 02 Jun 2010 11:59:32 +0000

i want to get info abt consumer from database with all info like table name and consumer details

PS Query - Need the SQL to create a REPLACE statement

Fri, 12 Jun 2009 11:16:19 +0000

I am still learning how to build and manipulate queries within PeopleSoft and I'm certainly no expert with SQL, but I am looking to find the correct statement to have the query look at a person's years of service with the company (which is calculated using an expression) and when the person's years of service are 5, 10, 15, 20 years, I need the query to return a character, such as *, in a separate field. I cannot build this into the criteria because I do not want to exclude those rows of data where the years of service are other than 5, 10, 15 and so on.

How do I return multiple rows when using START WITH in an Oracle stored procedure?

Chad0075 — Tue, 09 Jun 2009 20:44:52 +0000

Having trouble displaying a user approval route for all users and their approval limits
Al reports to Bill, Bill reports to Chris, Chris reports to Dean.
I am trying to show a report that summarizes all user ids.
The error is ORA-01422: exact fetch returns more than requested number of rows.
Using Oracle 8i.
--------------------------
Al - $0 limit
Bill - $10 limit
Chris - $20 limit
Dean - $40 limit
-----
Bill - $10 limit
Chris - $20 limit
Dean - $40 limit
----
Chris - $20 limit
Dean - $40 limit
----
Dean - $40 limit

Passing check variables fronm the front end as parameters to Stored Procedure in SQL SERVER2005

ashok_star2004 — Sat, 16 May 2009 06:41:59 +0000

Hi All,
I want to pass variables(Tables) which are checked in check box in the front to the stored procedure in sql server2005.How can i do this.Can any one please help me up please.i want to call the procedure from jsp code.

Thanks in Advance,
Ashok K.

Pushing tables from one database to another database

ashok_star2004 — Fri, 08 May 2009 09:30:21 +0000

Hi,
Im using SQLServer2005.I have some tables in 1 database CRM90 which are not present in another database HRMS9.I want to move those tables to HRMS9 from CRM90 which are not present in HRMS9.I want to do this by writing a procedure in SQL Server2005.Can anyone help me up please!!

Thanks in Advance,
Ashok K.

compare data from two identical tables

Wed, 15 Apr 2009 07:31:53 +0000

hi guys i am tryin to compare data from two identical tables E6900 and M9000 and this my DBlink ops$mis_exec.mis_sub_prof@prod
so my query is as follows i am not sure whether i am on the right track

select * from ops$mis_exec.mis_sub_prof@prod minus select * from ops$mis_exec.mis_sub_prof;

Retrieving Tables from different databases using database links with Escape Characters

ashok_star2004 — Mon, 23 Mar 2009 05:35:59 +0000

Hi!
I want to get tables which are present in one database and not present in other database using database links.The tables must start with PS_.
I wrote a query for this.but its not functioning.I want to get it using Escape characters.Can u help me out please..

SELECT C.TABLE_NAME FROM USER_TABLES C WHERE C.table_name like ‘PS\_%’ESCAPE’\’ AND C.TABLE_NAME NOT IN(SELECT A.TABLE_NAME FROM USER_TABLES A
WHERE A.table_name like ‘PS\_%’ESCAPE’\’
INTERSECT
SELECT B.TABLE_NAME FROM USER_TABLES@PJCRM B
WHERE B.table_name like ‘PS\_%’ESCAPE’\’)

ORACLE Long Character

Fri, 18 Apr 2008 19:27:28 +0000

Is it possible to select rows from a table where the Long Character field contains a value?