crystal report SQL query

[zsyed]

Can any one please tell me why is the following script not working?
Many Thanks,

(select "LotNumberTransactions"."abtlotnumberid" from LotNumberTransactions inner join SerialNumberTransactions A on "LotNumberTransactions"."abtJobID" = "SerialNumberTransactions"."sntJobID" and "LotNumberTransactions"."abtTransactionType" = 3 where A."sntserialnumberid" = "SerialNumberTransactions"."sntserialnumberid" and A."sntStatus" = 2 and A."sntTransactionType" = 3)

 

[zsyed]

Which is basically, I'm tryig to get the output for the following SQL script:
select abtlotnumberid from LotNumberTransactions inner join serialnumbertransactions on abtJobID = sntJobID and abtTransactionType = 3 where sntserialnumberid = '1110-259' and sntStatus = 2 and sntTransactionType = 3

 

[RTag]

How about this one :

SELECT "LotNumberTransactions"."abtlotnumberid"
FROM LotNumberTransactions
INNER JOIN SerialNumberTransactions A
ON "LotNumberTransactions"."abtJobID" = A."sntJobID"
AND "LotNumberTransactions"."abtTransactionType" = 3
WHERE A."sntStatus" = 2
AND A."sntTransactionType" = 3

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[zsyed]

REsults in following error
Error in compiling SQL Exression
Subquery returned more than one value. Native Error 512 DAtabase Vendor code 512.

 

[epmo]

The query is used in place where one value is expected. The problem is not in the query syntax but in the returned data. Try to use distinct if it still returns the same error modify the where clause to return just one record:

SELECT DISTINCT "LotNumberTransactions"."abtlotnumberid"
FROM LotNumberTransactions
INNER JOIN SerialNumberTransactions A
ON "LotNumberTransactions"."abtJobID" = A."sntJobID"
AND "LotNumberTransactions"."abtTransactionType" = 3
WHERE A."sntStatus" = 2
AND A."sntTransactionType" = 3

 

[lbass]

Try the query as a command instead of a SQL expression (database->database expert->your datasource->add commmand (above the list of tables).

-LB

 

[zsyed]

Thank you all for the responses.
I can eliminate multipe records by setting top 1 after select, however, I need this query to be executed in the specific instance of serial number which is
A."sntserialnumberid" = "SerialNumberTransactions"."sntserialnumberid"
In the abscenc of the above condition the query results in the global stance and the result would be incorrect. I'm unable to get the above condition working.

I tried as command and could not make it to work. May be I don;t know much about command.

Any help is appreciated.

 

[lbass]

Please show the command you tried and the error message you received.

-LB

 

[zsyed]

it's really tricky to get the query as command because there is a high chance of duplications. For ex. if I use query as
select sntpartid, sntpartrevisionid, sntserialnumberid , count (sntserialnumberid) as counter from serialnumbertransactions where sntStatus = 2 and sntTransactionType = 3 group by sntserialnumberid, sntpartid, sntpartrevisionid


The test is only on serailnumber transactions, the equal number of chances of duplicatins are possible in lotnumber transactions. In that situation I would take the latest record. but if I use top 1 then whole query result in one line only.

 

[lbass]

Instead of showing these sample queries, why not explain what you are trying to do and show some sample data at the detail level.

-LB

 

[zsyed]

please find below for description with sample data. I hope this clarifies. If you need clarification then feel free to let meknow

Serial Number Part ID Transaction Type status Job ID
1110-259 PM687MC0176 1 1 8506
1110-259 PM687MC0176 2 1110-259 PM687MC0176 3 2 8506
1110-259 PM687MC0176 4 3 31145-0002-001

The above one is a serial number transactions table, shows the serial number life stage. A serialnumber created, assigned, mfg receipt and issued to job.

Our query should look at for any serial number, find and goto the third stage (mfg receipt) and then find the job, then link with lottransactions table to get the corresponding lot number.
The connecting points are same job id, Same transaction type, same date and same transaction number.

Lot Number Quantity Job ID Part ID
B2826A 5.56 8506 BRMCR9.625
B2826A 6 8506 M687MC0176

 

[lbass]

So given the sample data from the two tables, what would you want returned to the report? Also, it is unclear which field corresponds to the life stage.

-LB

 

[zsyed]

Sorry about the confusion. the report logic flows from serail numbers. for each serial number for a given part(PM687MC0176) there would be transaction type = 2 (mfg rct). on that record, there would be a job id (8506). from that record logic should link to the lot transaction table for the given above touch points (transaction type, part id, and job id). For the lot transaction table we would have to get lot number (B2826A).

If you need more clarification please let me know.

 

[zsyed]

I would like to split the query into two parts. First is to get job number from serial numbers table corresponding to a serial number and transaction type 3. Once I get a job ref from this table then I can use this to find lot number in lot table. To get job reference in serial table, how can I use the query?. please find below sample data.
serial# transaction-type jobid tranactiondate&time
xyz1 1 abc1 day1
xyz1 2 abc1 day2
xyz1 3 abc1 day3
xyz1 3 abc1 day4
xyz1 4 abc2 day5

I would like to get the first avaialable transaction type = 3 for the day. I tried below code but giving me last record in the set which is 4. Is there anything I'm missing?
(select "SerialNumberTransactions"."sntjobid" from serialnumberTransactions A
where A."sntSerialNumberID" = "SerialNumberTransactions"."sntSerialNumberID" and
A."sntTransactionDate" = (select max("sntTransactionDate") from "SerialNumberTRansactions" A where
A."sntSerialNumberID" = "SerialNumberTransactions"."sntSerialNumberID" And A."sntTransactionType" = 3))

 

[zsyed]

Hi lbass,

I manged to sue command query and I'm alsomt there except with some data issue. I'm using query which is resulting duplicates and which is correct but I do not want it that way. I need the first available record for a serial number for that job combination. If there are more that one record exist then the query should result in first avaialble record. How can I make use of transaction date for this purpose? Say which ever occur latest that should be picked.

sameple data
Sr. Num lotnum jobid transactiondate?
1203-445 51647 10217
1203-445 N7383 10217
1203-446 51647 10217
1203-446 N7383 10217
1203-447 N7383 10217

select distinct max(sntserialnumberid) as sernum, abtlotnumberid, sntjobid from serialnumbertransactions join lotnumbertransactions on Sntpartid = abtpartid and sntpartrevisionid = abtpartrevisionid and sntjobid = abtjobid where snttransactiontype = 3 group by sntserialnumberid, abtlotnumberid, sntjobid

 

[zsyed]

Is there any way that we can code SQL to drop duplicate records?

 

[zsyed]

Looks new editor. Thanks for the new system.

I managed work with this problem using command query. thanks to all for suggestions and tips. This is a great forum.