topic Problem with stored procedure columns in QlikView

Problem with stored procedure columns

Thu, 12 May 2011 14:00:18 GMT

Dear Qlik-members!

I have a problem with a stored procedure i run in my load script because my company has changed something.

One month ago the procedure gave me a normal table.

Since one week the same procedure has some extra columns.

Now is the problem that my colleague has made a &*#*&$!! mistake and the procedure gives me a table with two columns who has the same name......

How can is select those both individually?

I hope you can help me

Kind Regards,

Martijn

Problem with stored procedure columns

blaise — Thu, 12 May 2011 15:46:25 GMT

Im not sure how you use the stored procedure but have you tested with;

Load column1, column2; sql exec your_stored_procedure;

Problem with stored procedure columns

erichshiino — Thu, 12 May 2011 15:59:32 GMT

QV will probably rename as blaise said. At least, that's what happen with excel files with columns with the same name.

Maybe it will be column and column 1

you could try:

Load *; sql exec your_stored_procedure;

or even without the load to see the names you can get

Re: Problem with stored procedure columns

blaise — Thu, 12 May 2011 16:08:58 GMT

Well the column1 and column2 was only used as an example where he can specify what columns to load

Sent from my Sony Ericsson Xperia arc

Re: Problem with stored procedure columns

Fri, 13 May 2011 08:09:19 GMT

Thanks blaise and erich!

I like the idea of use * to know the names who gives Qlikview them.

But when I use

Temp:

LOAD *;

SQL EXEC "$(vDatabase)".dbo.[name_procedure] 2,'$(vStartDate)','$(vEndDate)',$(i);

I get an error (something like "Reading OLEDB failed") and when I use the old method

Temp:

LOAD a,

SQL EXEC "$(vDatabase)".dbo.[name_procedure] 2,'$(vStartDate)','$(vEndDate)',$(i);

not....

Please help

Kind regards,

Martijn

Re: Problem with stored procedure columns

blaise — Fri, 13 May 2011 08:34:04 GMT

1. only the sql exec part and reload the application

2. Find the column names in the table viewer (ctrl+t)

3. add the column names into a load ; statement

4. Reload the application

Re: Problem with stored procedure columns

Fri, 13 May 2011 08:47:34 GMT

Hi Blaise,

When I do this I still get the same error...

Now I run only

SQL EXEC "$(vDatabase)".dbo.[name_procedure] 2,'$(vStartDate)','$(vEndDate)',1;

I have add the "mode is write" part on the end of my connection string and checked the sentence about read en write on database.

Hope you or somebody can help me.

Kind Regards,

Martijn

Re: Problem with stored procedure columns

Fri, 13 May 2011 09:16:31 GMT

Hi Erich,

The part withou load wil also not work...

I get error reading OLEDB failed.

Re: Problem with stored procedure columns

blaise — Fri, 13 May 2011 11:10:51 GMT

How did you load the data before? Does the stored procedure works at all?

Best Regards

Problem with stored procedure columns

Fri, 13 May 2011 11:58:06 GMT

Yes normal it works.

See my first post

Problem with stored procedure columns

Mon, 06 Jun 2011 12:54:39 GMT

Can somebody help me with this?

Problem with stored procedure columns

blaise — Mon, 06 Jun 2011 13:15:12 GMT

please post the statement you use to extract the data using the PL/SQL stored procedure and nothing else (ie create an application that only extracts the data from PL/SQL stored procedure).

Please post a screenshot of the table viewer where all fields are visible.

Br Blaise

Problem with stored procedure columns

Tue, 07 Jun 2011 12:47:40 GMT

I'm not sure select * will work in this situation.

I get the same thing that you get. When OLEDB driver (and ODBC) reaches the duplicate column name, it throws ODBC/OLEDB read failed.

Can you rename the column on the sql side ? Having two columns with the same name, while allowed in SQL, seems confusing.

If you cannot rename the column in that procedure, create anohter one or have it return an alternate name based on the calling program.