473,320 Members | 1,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How can I change n-rows --> 1-row ? Getting constraint information.

11
Hi all,
I've tried to get primary and unique constraint information via syscat.tabconst and syscat.keycoluse tables as one value for each constraint.
For example, let's there is table called tblTest.
tblTest ( Id, col1, col2, col3, col4 )
- PK (P_PK_tblTest) : Id
- UK (U_UK_tblTest) : col2, col3

If I join two tables, syscat.tabconst (TC) and syscat.keycoluse (KC), it will show
TC.constname TC.type KC.colnames
-------------- -------- -------------
P_PK_tblTest P Id
U_UK_tblTest U col2
U_UK_tblTest U col3

But I'd like get them like below such as we can get columns information from syscat.indexes
TC.constname TC.type KC.colnames
-------------- -------- -------------
P_PK_tblTest P +Id
U_UK_tblTest U +col2+col3

So I've tried to solve this using function but it's complaining something.
How can I get the result like above (+col2+col3) ?

In addition, I've posted the script that I tried to make function and error message. It would return only one result.

create function getUPConstraint( p_schema varchar(50), p_tableName varchar(50), p_type varchar(1) ) returns varchar(200)

language sql

begin atomic

declare v_result varchar(200);
declare v_columns varchar(50);

declare c1 cursor with return for
SELECT kc.colname
FROM syscat.tabconst tc, syscat.keycoluse kc
WHERE tc.tabschema = kc.tabschema
AND tc.tabname = kc.tabname
AND tc.constname = kc. constname
and tc.tabschema = p_schema
and tc.tabname = p_tableName
AND tc.type = p_type
order by kc.colseq;

declare exit handler for not found
set v_result = '';

begin
open c1;
fetch_loop:
loop
fetch c1 into v_columns
set v_result = v_result + '+' + v_columns;
end loop fetch_loop;

close c1;
end;

return v_result ;
end@

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "cursor with return for SELEC" was
found following " declare c1". Expected tokens may include:
"<space>". LINE NUMBER=11. SQLSTATE=42601
Jun 19 '08 #1
1 1303
tensi4u
11
Actually I've solved this problem with the makeshift. I've posted what I had done. But I've been in stuck with creating function issue. Thank you.

Expand|Select|Wrap|Line Numbers
  1. select tblColSpan.constname, '+' || max(tblColSpan.col1) || '+' || max(tblColSpan.col2)
  2. from (
  3.     select tblInner.constname, ( case tblInner.colseq when 1 then tblInner.colname else '' end) col1, ( case tblInner.colseq when 2 then tblInner.colname else '' end) col2, ( case tblInner.colseq when 3 then tblInner.colname else '' end) col3 
  4.     from ( SELECT constname, colname, colseq FROM syscat.keycoluse WHERE tabschema = <schema name> AND tabname = <table name> and constname in ( <constraints name> {, <constraint name>}) order by constname, colseq ) tblInner
  5. ) tblColSpan
  6. group by tblColSpan.constname.
Jun 20 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Developer98115 | last post by:
I need help getting schema information from an existing SQL Server database. My thought was that you could use ADOX via InterOp. Has anyone done this successfully and how? I have created a...
0
by: z. f. | last post by:
Hi, i have asp.net vb.net wep application. i try to investigate performance issue. so first i built a vb.net windows application that makes http requests to my pages and show me the time taken...
1
by: z. f. | last post by:
Hi, i have a asp.net vb.net web appliation. it is compiled to release build with debug information. i have module to analyze errors in application_onError to trap all errors in my code and...
3
by: Johannes | last post by:
Hi, I got a strange prblem when using InnoSetup to deploy an EXE made with VB6 on WinXP: If I run the setup the ABC.EXE is installed without problems. But when I start the ABC.EXE it is...
2
by: dollyvishal | last post by:
How to get constraint information for MS Access tables using Query?
4
by: herc | last post by:
My objective is to get the "label" information for the different volumes that are mounted. I want to display the same information as Windows Explorer. I wrote this test code with gets me the...
8
by: Abubakar | last post by:
Hi, I want to know that when my application is running outside of the debugger (in debug or release build) can I set the application/project settings in a way that when it crashes it tells me...
0
by: Andrus | last post by:
I noticed that Linq-SQL DataContext ExecuteQuery method does not have new() constraint: IEnumerable<TResultExecuteQuery( command, ... ) IEnumerable must return object so it must create this...
1
by: Mufasa | last post by:
I'm trying to modify my exception handler to give me as much useful information as possible. One of the thigs I would like to know is where in the code the error actually happened. I can print out...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.