Home » SQL & PL/SQL » SQL & PL/SQL » Order By length of RecName field...is it possible? (merged)
Order By length of RecName field...is it possible? (merged) [message #311475] Fri, 04 April 2008 10:49 Go to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
hello folks,

I am trying select RECNAMES, but order them by the field length. Is there any way to ORDER BY the length of the RECNAME field?

Here is what I have so far:

SELECT DISTINCT A.RECNAME
FROM PSRECFIELD A, PSRECDEFN B
WHERE A.FIELDNAME LIKE '%NAME%'
AND A.RECNAME = B.RECNAME
AND B.RECTYPE = 0
AND A.RECNAME NOT LIKE '%VW'
AND A.RECNAME NOT LIKE 'COMPANY%'
ORDER BY **** field length of RECNAME field*****

Currently,without the ORDER By, I get a list of records, but they are listed alphabetically. I need them to be listed by field length size.

Thanks,
orayoh Smile
rec count and field value for multiple tables [message #311479 is a reply to message #311475] Fri, 04 April 2008 10:58 Go to previous messageGo to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
Hello,

Is it possible to get a record count along with a recname in the same sql statement for multiple tables?

I am trying to select recnames and their counts for multiple tables. However, is this only possible using a script?

I can select a list of the recnames, but I also need the count for each of these records.

Thanks,
orayoh Smile
Re: Order By length of RecName field...is it possible? [message #311480 is a reply to message #311475] Fri, 04 April 2008 11:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you actually think this is an expert problem?
Re: Order By length of RecName field...is it possible? [message #311482 is a reply to message #311480] Fri, 04 April 2008 11:06 Go to previous messageGo to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
Frank wrote on Fri, 04 April 2008 12:01
Do you actually think this is an expert problem?


Sorry about that. Mod's can you please move this to the general PL/SQL forum?

Re: rec count and field value for multiple tables [message #311484 is a reply to message #311479] Fri, 04 April 2008 11:07 Go to previous messageGo to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
Mod's, can my post be moved to the general PL/SQL forum?

Re: Order By length of RecName field...is it possible? [message #311489 is a reply to message #311482] Fri, 04 April 2008 11:17 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You are aware of the length function?
Re: rec count and field value for multiple tables [message #311492 is a reply to message #311479] Fri, 04 April 2008 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is recname?

Regards
Michel
Re: Order By length of RecName field...is it possible? [message #311493 is a reply to message #311489] Fri, 04 April 2008 11:28 Go to previous messageGo to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
ThomasG wrote on Fri, 04 April 2008 12:17
You are aware of the length function?


Yes, but I wasn't sure how to use it with my statement.

Also, can the LENGTH function be used to find the length of a field name. I have seen it used in the where clause, but not to find the length of a field.

Ultimately, I want to be able to select the RECNAME from a record along with it's length...and sorted by field length.

SELECT RECNAME FROM PSRECFIELD WHERE LENGTH(RECNAME) = 5;

SELECT DISTINCT A.RECNAME
FROM PSRECFIELD A, PSRECDEFN B
WHERE A.FIELDNAME LIKE '%NAME%'
AND A.RECNAME = B.RECNAME
AND B.RECTYPE = 0
AND A.RECNAME NOT LIKE '%VW'
AND A.RECNAME NOT LIKE 'COMPANY%';
Re: Order By length of RecName field...is it possible? [message #311499 is a reply to message #311493] Fri, 04 April 2008 11:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Not sure if I understand you correctly here, but could it be that you don't mean the number of characters in the RECNAME, but the sum of the characters in all the fields that make up the logical record, or maybe the number of fields in the record?

Show us what you mean by providing sample data plus expected output

[Updated on: Fri, 04 April 2008 11:37]

Report message to a moderator

Re: Order By length of RecName field...is it possible? [message #311504 is a reply to message #311499] Fri, 04 April 2008 11:48 Go to previous messageGo to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
Frank wrote on Fri, 04 April 2008 12:36
Not sure if I understand you correctly here, but could it be that you don't mean the number of characters in the RECNAME, but the sum of the characters in all the fields that make up the logical record, or maybe the number of fields in the record?

Show us what you mean by providing sample data plus expected output


Okay,

Below is what I currently have as a sql statement. The output follows(I only listed a few tables, since the sql results in over 3000 tables).

I basically need to create a spreadsheet with the table names, along with their record counts. So I figured the best way to do this is to select all the recnames and sort them by the lengths of their recnames. This would enable me to cut and paste the data and create a script. I would then add the "select count(*) "NAMES_TMP" from PS_NAMES_TMP;" around each table name in the script. Which is why I wanted to sort the names via field length.

This just makes it quicker to paste around the table name. I know this is a bit convoluted way of doing things, but I'm not sure how to do it another way, which I am sure it does exist.

SELECT DISTINCT A.RECNAME
FROM PSRECFIELD A, PSRECDEFN B
WHERE A.FIELDNAME LIKE '%NAME%'
AND A.RECNAME = B.RECNAME
AND B.RECTYPE = 0
AND A.RECNAME NOT LIKE '%VW'
AND A.RECNAME NOT LIKE 'COMPANY%';

Output

ABS_TYPE_TBL
ACCOMPLISH_LANG
ADDAPPT_SEC_JPN
ADDRESSES_SA
ADM_APPL_TENDER
ADM_EDI_MSGS
.
.
.

[Updated on: Fri, 04 April 2008 11:52]

Report message to a moderator

Re: Order By length of RecName field...is it possible? [message #311505 is a reply to message #311475] Fri, 04 April 2008 11:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
If DB statistics are current, the easiest way is to
SELECT TABLE_NAME, NUM_ROWS FROM ALL_TABLES;

Regardless of what method is deployed, the accuracy will vary directly with rate at which INSERT & DELETE DMLs are done.
Re: rec count and field value for multiple tables [message #311506 is a reply to message #311492] Fri, 04 April 2008 11:57 Go to previous messageGo to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
Michel Cadot wrote on Fri, 04 April 2008 12:25
What is recname?

Regards
Michel



Recname is a field in the table PSRECFIELD. The sql statement I am trying to modify is below. I have actually made two seperate posts about diff aspects of the same issue. Maybe I should request this issue be deleted and just post to the other one.

SELECT DISTINCT A.RECNAME
FROM PSRECFIELD A, PSRECDEFN B
WHERE A.FIELDNAME LIKE '%NAME%'
AND A.RECNAME = B.RECNAME
AND B.RECTYPE = 0
AND A.RECNAME NOT LIKE '%VW'
AND A.RECNAME NOT LIKE 'COMPANY%';

Thanks,
orayoh
Re: rec count and field value for multiple tables [message #311507 is a reply to message #311506] Fri, 04 April 2008 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We will merge them.
Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Order By length of RecName field...is it possible? [message #311513 is a reply to message #311505] Fri, 04 April 2008 12:14 Go to previous messageGo to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
Michel Cadot wrote on Fri, 04 April 2008 13:03
We will merge them.
Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel



Thanks Michel...much appreciated.

I am on:

SQL*Plus: Release 9.2.0.1.0
Oracle9i Enterprise Edition Release 9.2.0.6.0
Windows XP SP2

anacedent wrote on Fri, 04 April 2008 12:56
If DB statistics are current, the easiest way is to
SELECT TABLE_NAME, NUM_ROWS FROM ALL_TABLES;

Regardless of what method is deployed, the accuracy will vary directly with rate at which INSERT & DELETE DMLs are done.


I tried the following with not much success.

SELECT Table_Name,
num_Rows
FROM All_Tables
WHERE Table_Name IN (SELECT DISTINCT a.recName
FROM psrecField a,
psrecdefn b
WHERE a.FieldName LIKE '%NAME%'
AND a.recName = b.recName
AND b.recType = 0
AND a.recName NOT LIKE '%VW'
AND a.recName NOT LIKE 'COMPANY%')
ORDER BY num_Rows;
Re: Order By length of RecName field...is it possible? (merged) [message #311517 is a reply to message #311475] Fri, 04 April 2008 12:24 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I tried the following with not much success.
My car does not have much success.
Tell me how to make my car go, please.

Your statement is utterly devoid of meaningful content.
Re: Order By length of RecName field...is it possible? (merged) [message #311521 is a reply to message #311517] Fri, 04 April 2008 12:28 Go to previous messageGo to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
anacedent wrote on Fri, 04 April 2008 13:24
>I tried the following with not much success.
My car does not have much success.
Tell me how to make my car go, please.

Your statement is utterly devoid of meaningful content.


My apologies, I meant that it did not return the appropriate data. The stats I require exist in the two tables PSRECFIELD and PSRECDEFN. My original sql statement returns 3647 rows vs 506 rows from the ALL_TABLES table.

Is it possible to select record names and get a count of all the records returned in the same sql statement?

[Updated on: Fri, 04 April 2008 12:30]

Report message to a moderator

Re: Order By length of RecName field...is it possible? (merged) [message #311524 is a reply to message #311521] Fri, 04 April 2008 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This means your statistics are not up to date.

Regards
Michel
Re: Order By length of RecName field...is it possible? (merged) [message #311528 is a reply to message #311475] Fri, 04 April 2008 12:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you read & FOLLOWED the posting guidelines as stated in URL above.

>The stats I require exist in the two tables PSRECFIELD and PSRECDEFN
But you do not post the DESCRIBE of these tables so we know the same data as you. Why?


>select record names and get a count of all the records
I never realized that records had names. Please explain.

What output do you expect/desire & why is that output correct?
Re: Order By length of RecName field...is it possible? (merged) [message #311532 is a reply to message #311528] Fri, 04 April 2008 12:43 Go to previous messageGo to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
anacedent wrote on Fri, 04 April 2008 13:37
http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you read & FOLLOWED the posting guidelines as stated in URL above.

>The stats I require exist in the two tables PSRECFIELD and PSRECDEFN
But you do not post the DESCRIBE of these tables so we know the same data as you. Why?


>select record names and get a count of all the records
I never realized that records had names. Please explain.

What output do you expect/desire & why is that output correct?


I basically need to create a spreadsheet with the table names, along with their record counts.

SELECT DISTINCT a.recName
FROM   psrecField a,
       psrecdefn b
WHERE  a.FieldName LIKE '%NAME%'
       AND a.recName = b.recName
       AND b.recType = 0
       AND a.recName NOT LIKE '%VW'
       AND a.recName NOT LIKE 'COMPANY%';


Output

ABS_TYPE_TBL
ACCOMPLISH_LANG
ADDAPPT_SEC_JPN
ADDRESSES_SA
ADM_APPL_TENDER
ADM_EDI_MSGS
.
.
.
Re: Order By length of RecName field...is it possible? (merged) [message #311533 is a reply to message #311532] Fri, 04 April 2008 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no count in your output... and Ana already answered this question.

Regards
Michel
Re: Order By length of RecName field...is it possible? (merged) [message #311535 is a reply to message #311475] Fri, 04 April 2008 12:46 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Since you choose to not answer all my inputs, I choose to stop wasting my time trying to assist you.

You're On Your Own (YOYO)!
Re: Order By length of RecName field...is it possible? (merged) [message #311536 is a reply to message #311533] Fri, 04 April 2008 12:49 Go to previous messageGo to next message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
Michel Cadot wrote on Fri, 04 April 2008 13:45
There is no count in your output... and Ana already answered this question.

Regards
Michel



I know, but Ana asked me what I output I expected. I tried combining the select from the psrecField and psrecdefn tables, with a select from the ALL_TABLES, but no luck. I have asked my dba's if the stats can be updated.

If the stats were updated, then two columns of data, record name along with a count would be ideal.
Re: Order By length of RecName field...is it possible? (merged) [message #311537 is a reply to message #311535] Fri, 04 April 2008 12:51 Go to previous message
orayoh
Messages: 22
Registered: May 2006
Location: Ontario
Junior Member
anacedent wrote on Fri, 04 April 2008 13:46
Since you choose to not answer all my inputs, I choose to stop wasting my time trying to assist you.

You're On Your Own (YOYO)!


That's fine. In any case, thanks for your help.
Previous Topic: group by/rollup?
Next Topic: Define a Record Layout (merged 3 topics, deleted 2)
Goto Forum:
  


Current Time: Sat Dec 10 01:09:00 CST 2016

Total time taken to generate the page: 0.10939 seconds