Hai everybody...!
I have a problem here .. Hope I can get help by answers..
I have a table A
ID DATA
1 aa;bb
2 aa;bb;cc
3 aa;cc
4 bb;cc
I wish to get a table B like
ID A B C
1 aa bb null
2 aa bb cc
3 aa null cc
4 null bb cc
I would like get table B a result by using DB2 UDF,,
wish have details of create function & how to call it ..
I am new to DB2.. waiting for Help..!
Thanks in advance...
12  2162 
Thanks for your response..
I have tried that one earlier.. it throws [Error] - &;WHERE ordinal. SQLCODE=-7, SQLSTATE=42601, DRIVER=3.53.71
I am using DB2 Aqua Data Studio 8.0.8
so waiting for further clarification..
It would be very helpful ..
Thanks once again in advance..
What code did you execute when you go that error?
Here is the code I used... - 1)CREATE or REPLACE FUNCTION elemIdx (string CLOB(64K) )
-
2)RETURNS TABLE ( ordinal INTEGER, index INTEGER )
-
3)LANGUAGE SQL
-
4)DETERMINISTIC
-
5)NO EXTERNAL ACTION
-
6)CONTAINS SQL
-
7)RETURN WITH t(ordinal, index) AS
-
8) ( VALUES ( 0, 0 )
-
9) UNION ALL
-
10) SELECT ordinal+1, COALESCE(NULLIF(LOCATE(';',
-
11) string,index+1),0),LENGTH(string)+1) FROM t
-
12) WHERE ordinal < 10000 AND LOCATE(';', string,
-
13) index+1) <> 0 )
-
14) SELECT ordinal, index FROM t
-
15) UNION ALL
-
16) SELECT MAX(ordinal)+1, LENGTH(string)+1 FROM t
In the above code I am not clear about the following...
[ CLOB(64K) ]
[ <10000 AND ]
[ <> ]
Thanks in advance..
Don't just copy and paste code without looking at what you are executing. That is quite a dangerous thing to do.
1.) Don't execute it with those line numbers
2.) Read the tutorial to understand what the SQL is doing.
Thanks..
I do executed without line numbers ..
just for understanding i added line number manually for posting reply...
as i mentioned early
[ CLOB(64K) ]
[ <10000 AND ]
[ <> ]
are not mentioned clearly in tutorial...
Thanks again..
Most likely some of the characters are supposed to be less than (<) instead of < and their code formatter didn't display them right so replace < with < and > with >
yes you are right...! http://www.ibm.com/developerworks/da...03stolze1.html
helps to get the seperate Stings from a group of String as input ...
i.e.s from 'aa;bb;cc' as input gives 'aa' 'bb' 'cc' in three records as output...
but still i am not getting how to get ...
table B
ID A B C
1 aa bb null
2 aa bb cc
3 aa null cc
4 null bb cc
as output from the input
table A
ID DATA
1 aa;bb
2 aa;bb;cc
3 aa;cc
4 bb;cc
i.e., how to pass each DATA in all records of table A as String (by looping) as input
& how to insert the result into the fields A B C of table B respectively..
Thanks Again in Advance...
insert from a select (or from a group of selects)
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: sans_spam |
last post by:
I'm writing a classic ASP application that records all logging of user
logins on our support site. The logging is a rolling window of how many
people have logged in for a given month, i.e. tracked by a 'lastlogin'
field so the tracking is done in a date range. So, for the month of
January I would record lastlogin dates between January 1 and January
31. My question is this...on the last day of the month (example being
Janauary)at 23:59:59PM...
|
by: Joe Bloggs |
last post by:
Hello,
I have a form linking two tables but they have one field that is
shared called "device" I want to allow data input into one of the
fields and hide the other (so I need it automatically updated from the
first record). How do I have the second record in the separate table
updated based on the change or update of the first record?
I am probably missing something really stupid and easy!
|
by: SheldonMopes |
last post by:
I'm not sure how to go about this:
I have a form bound to tableA. I would like to be able to use the
AfterUpdate event of textbox1 to write the contents (after some
manipulation) to TableB. How to open another table while keeping the
table linked to my form open ?
Thanks for your help.
|
by: jaYPee |
last post by:
as of now i am using a stored procedure from my sql server 2000 to
insert record from another table. what i need now is on how can i
insert record by not using the stored procedure and insert it using
dataset.
here is my code in stored procedure..
CREATE PROCEDURE AddRegularLoad
@SchYrSemID as int, @ProgramID as int, @Sem as varchar(50), @Year as
|
by: Prabu Subroto |
last post by:
Dear my friends...
I created a table (named : sven1). I want to populate
this table with the record from another table (named :
appoinment).
but I don't know how to formulate the sql query.
I tried this one:
insert into sven1 (custid, noapp) values ((select
custid from appointment where done='N' and
| | |
by: travismorien |
last post by:
I have four tables of different "entities". One table contains
information for "people", one for "trusts", one for "companies" and one
for "self managed super funds". Each type of entity has an autonumber
ID, "Person ID" "Trust ID" "Company ID" and "SMSF ID"
A "portfolio" table holds information about what shares, funds and
properties everyone owns. But because its organised by "PersonID" it
currently only can hold information for...
|
by: Deano |
last post by:
Problem is that there are lots and lots of fields in an employee record. I
specify a form control as the criterion for one of those fields which is the
value of the primary key for that record. The idea is to copy the current
record I am viewing in the form, to another table.
I want to simple execute a stored query but I get a run-time error 3061, too
few parameters. Expected 1. So even though I've specified the control
within the...
|
by: MLH |
last post by:
DELETE tblPreliminaryVINs.* FROM tblPreliminaryVINs INNER JOIN
tblVehicleJobs ON tblPreliminaryVINs.PVIN = tblVehicleJobs.SerialNum;
The above SQL does not work for me. I get an error
I cannot delete the record(s) because of READONLY
and PERMISSIONS related issues.
I can hilite (select) the record in the table and delete it. I can
use the following SQL to delete record(s) in tblPreliminaryVINs
DELETE tblPreliminaryVINs.* FROM...
|
by: webcat |
last post by:
Hi
I need to UPDATE data into a table
mainData
which contains many fields - one is CODE and one is DESCRIPTION
another table DESCRIP is a lookup - it also contains the same fields, but each is populated.
i need to run through all records and UPDATE the DESCRIPTION field in mainData based on matching CODE in mainData with CODE in DESCRIP
|
by: lee weaver |
last post by:
I have a table for employees and a table that shows which containers they have access to and a table of all containers.
What i need to do is when adding a new employee i need to add a record to the "access" table for each record in the container table. that would basically default the new employee's access to each container.
I think the following code in my addemployee form_Load event should detect if they have been added. but i'm having...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| | |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
