Re: Loop in a Stored procedure - Database Discussion Boards

Josh7harington10-Aug-11 5:14

10-Aug-11 5:14

Does anyone know where i may be able to find / download a English to Afrikaans OR Afrikaans to English Database ??? I need it for a project i am doing, i do not want one that connects online or a translator, i want an actual database with the words and translations.

Thanx alot,
Josh

Re: HELP !!

Eddy Vluggen10-Aug-11 11:04

Eddy Vluggen

10-Aug-11 11:04

First of all, don't start a topic with some generic topic like "help". I had to click on it and read it to determine it's contents - something I don't do when there's not much time. People could have an easy answer and skip your question, simply because the topic doesn't provide any clue. Most people posting questions here on the boards are looking for help in one way or the other.

On to your problem; there are some free dictionaries here[^], but I never seen them and can't say much about them.

Ergane[^] has it's dictionaries available as a separate download.

Last alternative, you can download the wiktionary[^].

Good luck Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

Loop in a Stored procedure

lionelcyril9-Aug-11 20:50

lionelcyril

9-Aug-11 20:50

Hi,
I am using a SSRS report and the report is generated by executing a stored procedure.
The procedure gets 4 input values a,b,c,d where values c and d is a string containing multiple values.
I am executing a Complex SQL statement for all values of c and d.
For this reason i am using nested loop:

SQL

A as INPUT
B as INPUT
C as INPUT
D as input
Cursor Cur as OUTPUT

	Loop for c
		Loop for d
			execute the SQL Statement(select 
		END loop
	END Loop

problem:
Now the results of the SQL statement has to be saved temporarily somewhere I am not allowed use permanent tables and I m struglling using temporary tables.
If i save the value in the cursor each time the loop runs the previous results get overwritten.

Re: Loop in a Stored procedure

Shameel9-Aug-11 22:20

Shameel

9-Aug-11 22:20

Use a Table Variable to store temporary data.

Syntax:

SQL

DECLARE @TableVar1 table(
    Col1 int NOT NULL,
    Col2 varchar(100),
    Col3 datetime
)

And then you can use it just like a physical table, e.g:

SQL

INSERT INTO @TableVar1 (Col1, Col2, Col3) VALUES (10, 'Hello', GETDATE())

Re: Loop in a Stored procedure

lionelcyril10-Aug-11 0:40

lionelcyril

10-Aug-11 0:40

Just to let you know.I am using this procedure in a package.

Where should I write the temp table declaration?

Re: Loop in a Stored procedure

Shameel10-Aug-11 8:42

Shameel

10-Aug-11 8:42

What does you mean by a package? Since you mentioned SSRS in your original post, I assumed the database to be SQL Server. AFAIK, SQL Server does not support packages, only Oracle does.

Re: Loop in a Stored procedure

lionelcyril10-Aug-11 8:46

lionelcyril

10-Aug-11 8:46

Sorry I didnt mention earlier..
I am writing the stored procedure in oracle.
I wanted to know if I can use a array and the store the results in the reference cursor which can be pulled by SSRS. but i dont know how to pass the values form array to reference

Re: Loop in a Stored procedure

S Douglas20-Aug-11 9:20

S Douglas

20-Aug-11 9:20

Shameel wrote:
AFAIK, SQL Server does not support packages, only Oracle does

FYI, SSIS (SQL Server Intergration Services) does use the term 'packages' for its code base.

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

Re: Loop in a Stored procedure

jschell10-Aug-11 10:07

jschell

10-Aug-11 10:07

I doubt it is going to be efficient to try to incrementally retrieve values from Oracle and use them in sql server.

So you have two steps.
1. Retreive the data from Oracle, all of it, and put it into a suitable data structure.
2. Use it in SSRS.

First step would probably be generally achieved by
A. Create appropriate temp table(s)
B. Populate table(s) from Oracle.

The design of the temp tables depends specifically on business information based on the what you are doing in SSRS and how that relates to the data in Oracle. So if you cannot determine the structure of the tables yourself you are going to need to explain the problem in detail or provide a sample that has been reduced from those requirements.

Keep in mind that you do NOT attempt step 2 until you have completed step 1.

Inner Query in mysql

Rakesh Meel8-Aug-11 0:56

Rakesh Meel

8-Aug-11 0:56

Is there any posibility of Inner Query in my SQL as Like MS Sql?

Re: Inner Query in mysql

Simon Bang Terkildsen8-Aug-11 1:53

Simon Bang Terkildsen

8-Aug-11 1:53

I'm actually not sure what Inner Query means, I'm guessing a subquery, if so then this should help
http://dev.mysql.com/doc/refman/5.1/en/subqueries.html[^]

Re: Inner Query in mysql

Simon_Whale8-Aug-11 2:49

Simon_Whale

8-Aug-11 2:49

You need to expand on your question so that we can answer it correctly and hopefully save you time.

also have a read of this in case the other answer is not what you are looking for.

MySQL Inner joins[^]

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Re: Inner Query in mysql

DaveAuld8-Aug-11 12:44

DaveAuld

8-Aug-11 12:44

Do you mean nested Selects?

i.e.

SQL

Select something From tableThis Where ID In (Select somethingelse From tableThat Where condition );

if you do then;
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html[^]

Dave
Find Me On: Web|Facebook|Twitter|LinkedIn

Folding Stats: Team CodeProject

Re: Inner Query in mysql

Rakesh Meel8-Aug-11 19:38

Rakesh Meel

8-Aug-11 19:38

Thanks for good comment dear...
but this syntex not working proper now i m sending my sample query on message board.

"SELECT * From tbladussentry where SID in (select Id from tbladusentry where SID in ( select ID from tbladuset where CID in(108,367)))"

Re: Inner Query in mysql

GuyThiebaut13-Aug-11 1:35

GuyThiebaut

13-Aug-11 1:35

I know you asked for subqueries however in this case I think joins are easier to read and more elegant:

select t1.* 
From tbladussentry t1
join tbladusentry t2
    on t1.SID = t2.Id
join tbladuset t3
    on t2.SID = t3.ID
    and t3.CID in(108,367)

Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)

Re: Inner Query in mysql

Rakesh Meel15-Aug-11 19:00

Rakesh Meel

15-Aug-11 19:00

Thanks dear....You are probably right but there is something more.......Look at my Example proper and told that is possible or not in MySql

argument data type ntext is invalid for argument 1 of rtrim function

md_refay7-Aug-11 22:09

md_refay

7-Aug-11 22:09

i use this line in my script
Select Data = ltrim(rtrim(@RowData))
@RowData is ntext
so i receive this error when trying to execute it

argument data type ntext is invalid for argument 1 of rtrim function

so what is the replacement to work with ntext instead of ltrim & rtrim

md_refay

Re: argument data type ntext is invalid for argument 1 of rtrim function [modified]

Shameel8-Aug-11 5:35

Shameel

8-Aug-11 5:35

What database is it? If it is SQL Server 2008, you should use nvarchar(max) instead of ntext. Most string functions cannot work with ntext.

EDIT: As a workaround, you can do this:

SQL

Select Data = CAST(ltrim(rtrim(CAST(@RowData AS nvarchar(max))) AS ntext)

modified on Monday, August 8, 2011 11:45 AM

DB2: Create primary key on partition table?

cuong-nm7-Aug-11 21:02

cuong-nm

7-Aug-11 21:02

Hi all !

In DB2, I want to create primary key on partition table. How can I do it?

Example:

Create table TEMP1:

CREATE TABLE TEMP1 (
ID INTEGER NOT NULL,
DATEREPORT VARCHAR(8) NOT NULL,
NUMBERREPORT VARCHAR(20),
TITLE VARCHAR,
YEAR INTEGER,
IDCOM INTEGER,
IDTEN INTEGER DEFAULT 0 NOT NULL
)DISTRIBUTE BY HASH (IDTEN, YEAR);

After that, I want to create primary key on ID column but not yet. How can I do?

Thanks !

MCP.NET, MCAD.NET, MCSD.NET

Re: DB2: Create primary key on partition table?

Herman<T>.Instance9-Aug-11 2:29

Herman<T>.Instance

9-Aug-11 2:29

use google: http://sqlzoo.net/howto/source/z.dir/tip241027/i02create.xml[^]

In Word you can only store 2 bytes. That is why I use Writer.

Re: DB2: Create primary key on partition table?

cuong-nm9-Aug-11 16:30

cuong-nm

9-Aug-11 16:30

Sorry, do you understand my question?

You must run by step:

- Create table with partition by hash

- Create PK on that table

or

- Create table with PK and partition by hash

will have same error

SQL0270N Function not supported Reason code = "1")

MCP.NET, MCAD.NET, MCSD.NET

Re: DB2: Create primary key on partition table?

Wendelius12-Aug-11 7:01

Wendelius

12-Aug-11 7:01

As far as I know you cannot create a primary key without including all the columns in distribute list. So basically you either have to:
- include ID in the distribute list
- define the primary key using IDTEN and YEAR and ID

The need to optimize rises from a bad design.My articles[^]

Re: DB2: Create primary key on partition table?

cuong-nm14-Aug-11 17:19

cuong-nm

14-Aug-11 17:19

ok, thanks

MCP.NET, MCAD.NET, MCSD.NET

Cube processing confirmation?

current19996-Aug-11 1:01

current1999

6-Aug-11 1:01

Hi,
After processing your ETL package which loads data into the cube, how do you ensure that your cube has been processed?
Current

Re: Cube processing confirmation?

Mycroft Holmes6-Aug-11 2:52

Mycroft Holmes

6-Aug-11 2:52

Surely the last step in your ETL package is to call the processing method of the cube! (seems obvious but I don't deal with cubes so I may be completely wrong).

Never underestimate the power of human stupidity
RAH

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.