If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Tabellen Spalten zählen

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 5
Tabellen Spalten zählen

Hallo Lieber User,

ich habe für Oracle folgende Anweisung

SELECT COUNT(*) FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = '[Tabelle]' and COLUMN_NAME != '_ID'

diese Möchte ich aber für DB2 übersetzen bzw. an DB2 Dialekt anpassen.

Nun kann ich nicht die Abfrage so umschreiben, dass diese die Anzahl von Spalten einer Tabelle als Zahl ausgibt.

Bitte um Ihre Hilfe,

Grüß und fröhliches Weihnachten.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 5
Erledigt.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Mar 2003
Posts: 267
Quote:
Originally Posted by surik_88 View Post
Hallo Lieber User,

ich habe für Oracle folgende Anweisung

SELECT COUNT(*) FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = '[Tabelle]' and COLUMN_NAME != '_ID'

diese Möchte ich aber für DB2 übersetzen bzw. an DB2 Dialekt anpassen.

Nun kann ich nicht die Abfrage so umschreiben, dass diese die Anzahl von Spalten einer Tabelle als Zahl ausgibt.

Bitte um Ihre Hilfe,

Grüß und fröhliches Weihnachten.
Etwas wie:

Code:
 SELECT COUNT (*) FROM syscat.columns
 WHERE tabname = ... and colname <> ...
sollte funktionieren. Es ist wahrscheinlich einfacher, hier Hilfe bekommen, wenn Sie auf Englisch stellen

Translated with Google translate. I have no idea whether the above makes sense or not,
__________________
--
Lennart
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 5
Quote:
Originally Posted by lelle12 View Post
Etwas wie:

Code:
 SELECT COUNT (*) FROM syscat.columns
 WHERE tabname = ... and colname <> ...
sollte funktionieren. Es ist wahrscheinlich einfacher, hier Hilfe bekommen, wenn Sie auf Englisch stellen

Translated with Google translate. I have no idea whether the above makes sense or not,

Thnaks it works already.

May be you can help me with other questions.

1. If I want to delete my Thread how can I do it in this Forum

2. Substr in DB2. So I have values f.e name, name_A, nickname, nickname_A how can I write the function substr(string, start, length) I need always the last two characters? I thougt substr(name, -2) like '_A' but it doesnot work with -2. Do you have any Idea? In oracle mysql and Microsoftsql it works.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2003
Posts: 267
Quote:
Originally Posted by surik_88 View Post
Thnaks it works already.

May be you can help me with other questions.

1. If I want to delete my Thread how can I do it in this Forum

2. Substr in DB2. So I have values f.e name, name_A, nickname, nickname_A how can I write the function substr(string, start, length) I need always the last two characters? I thougt substr(name, -2) like '_A' but it doesnot work with -2. Do you have any Idea? In oracle mysql and Microsoftsql it works.
What version of db2 are you running, recent versions have an Oracle compability mode for situations like yours. If you cannot use that something like:

substr(s,length(s) -1, 2)

will work.
__________________
--
Lennart
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 5
Quote:
Originally Posted by lelle12 View Post
What version of db2 are you running, recent versions have an Oracle compability mode for situations like yours. If you cannot use that something like:

substr(s,length(s) -1, 2)

will work.
Thank a lot for your help. I have solved my problem with function right. It is the same as substr, but from other side. So from right to left

IBM Information Management Software for z/OS Solutions Information Center
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On