Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Record selection from large list

Status
Not open for further replies.

Llazwas

IS-IT--Management
Feb 16, 2007
58
US
I'm using Crystal X with a provideX database as my source. I'm starting to write an inventory sales report and would like to select a specific list of about 300 items. It tried using multiple selection criteria (below) but it doesn't seem to work. Is there a better way to select a large list of items? I would place of of my selections in one array but Crystal appears to max out at a certain number.
Any guidance is greatly appreciated!
Code:
{mytable.itemno} IN ["930-000", "930-002", "930-005", "930-006", "930-010", "930-011", "930-025", "930-013", "930-016", "930-018"]or
{mytable.itemno} IN ["900-130FS", "900-130NC", "900-130TC", "900-131A TC", "900-131FS", "900-131TC", "900-132FS", "950-088", "950-088FP", "950-089", "950-089 BP ATT.", "950-089A", "950-089F", "950-089F FP", "950-089F FPSD", "950-089FP", "950-090", "950-091", "950-092", "950-092TC", "950-093", "950-093F TC", "950-093HD", "950-094", "950-094HD", "950-095", "950-095A", "950-095F", "950-097", "950-098", "950-100", "950-102", "960-030FS", "960-031F FS", "960-032FS", "960-032NC", "960-032TC", "960-033A TC", "960-033F FS", "960-033F TC", "960-033FS", "960-033TC", "960-038FS", "960-039FS"]
 
Why are these item nos being selected? What do they have in common? I think you should consider whether there is some other field that could be used to limit the item nos to the desired set.

Also, are you by any chance selecting all items that start with 930, 900, 950, etc? Or some combo of that and the ending characters in the item nos? There might be a way to simply the formula based on logic like this.

Another approach would be to exclude unwanted item nos, if there were fewer.

-LB
 
These items were selected by my boss for analysis. Unfortunately there's nothing unique about them other than the itemno itself. I also tried puling all item numbers and suppressing all but the items I need but couldn't get that to work either.
 
Hi,
Depending on your database you might be able to use a Command insted of a table selection in CR:
Code:
Select field1,field2, etc
from
mytable
where
itemno IN ("930-000", "930-002", "930-005", "930-006", "930-010", "930-011", "930-025", "930-013", "930-016", "930-018",
"900-130FS", "900-130NC", "900-130TC", "900-131A TC", "900-131FS", "900-131TC", "900-132FS", "950-088", "950-088FP", "950-089", "950-089 BP ATT.", "950-089A", "950-089F", "950-089F FP", "950-089F FPSD", "950-089FP", "950-090", "950-091", "950-092", "950-092TC", "950-093", "950-093F TC", "950-093HD", "950-094", "950-094HD", "950-095", "950-095A", "950-095F", "950-097", "950-098", "950-100", "950-102", "960-030FS", "960-031F FS", "960-032FS", "960-032NC", "960-032TC", "960-033A TC", "960-033F FS", "960-033F TC", "960-033FS", "960-033TC", "960-038FS", "960-039FS")

The SQL may have fewer restrictions on max criteria - If not, and you have access to do it, create a view that only has the desired item numbers and modify the Command to :
Code:
Select field1,field2, etc
from
mytable
where
itemno IN  In
(Select * from NewViewICreated)

If itemno is indexed, it should be fairly fast.

[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Thanks lbass. I was able to create a command that works perfectly.
Code:
SELECT "IM1_InventoryMasterfile"."ItemNumber", "IM1_InventoryMasterfile"."ItemDescription"
FROM   "IM1_InventoryMasterfile" "IM1_InventoryMasterfile"
WHERE IM1_InventoryMasterfile."ItemNumber"='930-000' OR
IM1_InventoryMasterfile."ItemNumber"='930-011' OR
IM1_InventoryMasterfile."ItemNumber"='930-025' OR
IM1_InventoryMasterfile."ItemNumber"='930-006' OR
IM1_InventoryMasterfile."ItemNumber"='930-016' OR
IM1_InventoryMasterfile."ItemNumber"='930-002' OR
IM1_InventoryMasterfile."ItemNumber"='930-005' OR
IM1_InventoryMasterfile."ItemNumber"='930-010' OR
IM1_InventoryMasterfile."ItemNumber"='930-013' OR
IM1_InventoryMasterfile."ItemNumber"='930-018' OR
IM1_InventoryMasterfile."ItemNumber"='900-130FS' OR
IM1_InventoryMasterfile."ItemNumber"='900-130NC' OR
IM1_InventoryMasterfile."ItemNumber"='900-130TC' OR
IM1_InventoryMasterfile."ItemNumber"='900-131A TC' OR
IM1_InventoryMasterfile."ItemNumber"='900-131FS' OR
IM1_InventoryMasterfile."ItemNumber"='900-131TC' OR
IM1_InventoryMasterfile."ItemNumber"='900-132FS' OR
IM1_InventoryMasterfile."ItemNumber"='950-088' OR
IM1_InventoryMasterfile."ItemNumber"='950-088FP' OR
IM1_InventoryMasterfile."ItemNumber"='950-089' OR
IM1_InventoryMasterfile."ItemNumber"='950-089 BP ATT.' OR
IM1_InventoryMasterfile."ItemNumber"='950-089A' OR
IM1_InventoryMasterfile."ItemNumber"='950-089F' OR
IM1_InventoryMasterfile."ItemNumber"='950-089F FP' OR
IM1_InventoryMasterfile."ItemNumber"='950-089F FPSD' OR
IM1_InventoryMasterfile."ItemNumber"='950-089FP' OR
IM1_InventoryMasterfile."ItemNumber"='950-090' OR
IM1_InventoryMasterfile."ItemNumber"='950-091' OR
IM1_InventoryMasterfile."ItemNumber"='950-092' OR
IM1_InventoryMasterfile."ItemNumber"='950-092TC' OR
IM1_InventoryMasterfile."ItemNumber"='950-093' OR
IM1_InventoryMasterfile."ItemNumber"='950-093F TC' OR
IM1_InventoryMasterfile."ItemNumber"='950-093HD' OR
IM1_InventoryMasterfile."ItemNumber"='950-094' OR
IM1_InventoryMasterfile."ItemNumber"='950-094HD' OR
IM1_InventoryMasterfile."ItemNumber"='950-095' OR
IM1_InventoryMasterfile."ItemNumber"='950-095A' OR
IM1_InventoryMasterfile."ItemNumber"='950-095F' OR
IM1_InventoryMasterfile."ItemNumber"='950-097' OR
IM1_InventoryMasterfile."ItemNumber"='950-098' OR
IM1_InventoryMasterfile."ItemNumber"='950-100' OR
IM1_InventoryMasterfile."ItemNumber"='950-102' OR
IM1_InventoryMasterfile."ItemNumber"='960-030FS' OR
IM1_InventoryMasterfile."ItemNumber"='960-031F FS' OR
IM1_InventoryMasterfile."ItemNumber"='960-032FS' OR
IM1_InventoryMasterfile."ItemNumber"='960-032NC' OR
IM1_InventoryMasterfile."ItemNumber"='960-032TC' OR
IM1_InventoryMasterfile."ItemNumber"='960-033A TC' OR
IM1_InventoryMasterfile."ItemNumber"='960-033F FS' OR
IM1_InventoryMasterfile."ItemNumber"='960-033F TC' OR
IM1_InventoryMasterfile."ItemNumber"='960-033FS' OR
IM1_InventoryMasterfile."ItemNumber"='960-033TC' OR
IM1_InventoryMasterfile."ItemNumber"='960-038FS' OR
IM1_InventoryMasterfile."ItemNumber"='960-039FS' OR
IM1_InventoryMasterfile."ItemNumber"='900-120' OR
IM1_InventoryMasterfile."ItemNumber"='900-121' OR
IM1_InventoryMasterfile."ItemNumber"='900-122' OR
IM1_InventoryMasterfile."ItemNumber"='900-122-1' OR
IM1_InventoryMasterfile."ItemNumber"='900-122-R' OR
IM1_InventoryMasterfile."ItemNumber"='900-124' OR
IM1_InventoryMasterfile."ItemNumber"='900-145' OR
IM1_InventoryMasterfile."ItemNumber"='900-145-1' OR
IM1_InventoryMasterfile."ItemNumber"='900-157/3 TRAY' OR
IM1_InventoryMasterfile."ItemNumber"='900-157/4 TRAY' OR
IM1_InventoryMasterfile."ItemNumber"='900-159/6' OR
IM1_InventoryMasterfile."ItemNumber"='910-028/40' OR
IM1_InventoryMasterfile."ItemNumber"='910-029/40' OR
IM1_InventoryMasterfile."ItemNumber"='910-030/40' OR
IM1_InventoryMasterfile."ItemNumber"='910-102' OR
IM1_InventoryMasterfile."ItemNumber"='910-059/109' OR
IM1_InventoryMasterfile."ItemNumber"='910-059/89' OR
IM1_InventoryMasterfile."ItemNumber"='910-059A/109' OR
IM1_InventoryMasterfile."ItemNumber"='910-059A/89' OR
IM1_InventoryMasterfile."ItemNumber"='910-059X' OR
IM1_InventoryMasterfile."ItemNumber"='910-060/89' OR
IM1_InventoryMasterfile."ItemNumber"='910-063/89' OR
IM1_InventoryMasterfile."ItemNumber"='910-063A/109' OR
IM1_InventoryMasterfile."ItemNumber"='910-063A/55' OR
IM1_InventoryMasterfile."ItemNumber"='910-063A/71' OR
IM1_InventoryMasterfile."ItemNumber"='910-063A/89' OR
IM1_InventoryMasterfile."ItemNumber"='910-064A/89' OR
IM1_InventoryMasterfile."ItemNumber"='910-148A' OR
IM1_InventoryMasterfile."ItemNumber"='910-150' OR
IM1_InventoryMasterfile."ItemNumber"='910-150A' OR
IM1_InventoryMasterfile."ItemNumber"='910-151' OR
IM1_InventoryMasterfile."ItemNumber"='910-081' OR
IM1_InventoryMasterfile."ItemNumber"='910-067/18' OR
IM1_InventoryMasterfile."ItemNumber"='910-067/27' OR
IM1_InventoryMasterfile."ItemNumber"='910-067/48' OR
IM1_InventoryMasterfile."ItemNumber"='910-067/72' OR
IM1_InventoryMasterfile."ItemNumber"='910-067A/18' OR
IM1_InventoryMasterfile."ItemNumber"='910-067A/27' OR
IM1_InventoryMasterfile."ItemNumber"='910-067A/48' OR
IM1_InventoryMasterfile."ItemNumber"='910-067A/72' OR
IM1_InventoryMasterfile."ItemNumber"='910-067A/9' OR
IM1_InventoryMasterfile."ItemNumber"='910-067X' OR
IM1_InventoryMasterfile."ItemNumber"='910-068/27AUS' OR
IM1_InventoryMasterfile."ItemNumber"='910-100 DELUX' OR
IM1_InventoryMasterfile."ItemNumber"='910-125' OR
IM1_InventoryMasterfile."ItemNumber"='920-108' OR
IM1_InventoryMasterfile."ItemNumber"='920-109' OR
IM1_InventoryMasterfile."ItemNumber"='920-111' OR
IM1_InventoryMasterfile."ItemNumber"='920-110' OR
IM1_InventoryMasterfile."ItemNumber"='920-130' OR
IM1_InventoryMasterfile."ItemNumber"='920-125' OR
IM1_InventoryMasterfile."ItemNumber"='920-131' OR
IM1_InventoryMasterfile."ItemNumber"='8012' OR
IM1_InventoryMasterfile."ItemNumber"='8013' OR
IM1_InventoryMasterfile."ItemNumber"='8013/01/90' OR
IM1_InventoryMasterfile."ItemNumber"='8014' OR
IM1_InventoryMasterfile."ItemNumber"='2233096' OR
IM1_InventoryMasterfile."ItemNumber"='8014/01/02/09' OR
IM1_InventoryMasterfile."ItemNumber"='8103' OR
IM1_InventoryMasterfile."ItemNumber"='2265602' OR
IM1_InventoryMasterfile."ItemNumber"='2302126' OR
IM1_InventoryMasterfile."ItemNumber"='2338650' OR
IM1_InventoryMasterfile."ItemNumber"='912-000D' OR
IM1_InventoryMasterfile."ItemNumber"='912-000L' OR
IM1_InventoryMasterfile."ItemNumber"='912-001LC' OR
IM1_InventoryMasterfile."ItemNumber"='912-002' OR
IM1_InventoryMasterfile."ItemNumber"='912-003' OR
IM1_InventoryMasterfile."ItemNumber"='912-004D' OR
IM1_InventoryMasterfile."ItemNumber"='912-004L' OR
IM1_InventoryMasterfile."ItemNumber"='912-005CA' OR
IM1_InventoryMasterfile."ItemNumber"='912-005L' OR
IM1_InventoryMasterfile."ItemNumber"='912-060' OR
IM1_InventoryMasterfile."ItemNumber"='912-061' OR
IM1_InventoryMasterfile."ItemNumber"='912-062' OR
IM1_InventoryMasterfile."ItemNumber"='912-063' OR
IM1_InventoryMasterfile."ItemNumber"='912-010' OR
IM1_InventoryMasterfile."ItemNumber"='912-011' OR
IM1_InventoryMasterfile."ItemNumber"='912-036' OR
IM1_InventoryMasterfile."ItemNumber"='912-013' OR
IM1_InventoryMasterfile."ItemNumber"='912-014' OR
IM1_InventoryMasterfile."ItemNumber"='912-015' OR
IM1_InventoryMasterfile."ItemNumber"='912-076' OR
IM1_InventoryMasterfile."ItemNumber"='912-077' OR
IM1_InventoryMasterfile."ItemNumber"='912-078' OR
IM1_InventoryMasterfile."ItemNumber"='912-079' OR
IM1_InventoryMasterfile."ItemNumber"='912-086' OR
IM1_InventoryMasterfile."ItemNumber"='912-080' OR
IM1_InventoryMasterfile."ItemNumber"='912-081' OR
IM1_InventoryMasterfile."ItemNumber"='912-082' OR
IM1_InventoryMasterfile."ItemNumber"='912-085'
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top