This will even edit your main table in case you forgot to create a column for serial numbers.You can also use this to add sr_no column to two different columns and easily join them (or create a new table using both of them).
Please notice that usage patterns for SEQUENCE_TABLE() are not limited to numbers only. We can, for instance, generate a list of predefined string literals (convert a row into a column, if you wish).
How To Generate Serial Number In Mysql Select Queryl
To get the session identifier, the session serial number, and the SQL identifier of a SQL statement, query the V$SESSION view. The following example gets all sessions and SQL identifiers for the user AWSUSER.
Working... showing full records\",\"author\":\"username\":\"former.member\",\"displayName\":\"Former Member\",\"groupIcons\":[],\"suspended\":true,\"isCurrentUser\":false,\"parentAuthor\":\"username\":\"kennedy.t\",\"displayName\":\"Kennedy Thomas\",\"groupIcons\":[],\"suspended\":false,\"isCurrentUser\":false,\"id\":9611229,\"creationDate\":1350622646000,\"activeRevisionId\":10383953,\"lastActivity\":1350622646000,\"parentId\":9611300,\"originalParentId\":9609572,\"likeCount\":1,\"visibility\":\"full\",\"depth\":0,\"attachments\":[],\"canVoteUpOrCancel\":false,\"relations\":\"canReport\":false,\"visibility\":\"full\",\"canEdit\":false,\"canUseDelete\":false,\"isLiked\":false,\"type\":\"comment\",\"canVoteUpOrCancel\":false,\"canConvertToAnswer\":false,\"canBeModerated\":false,\"canViewRevisions\":false,\"score\":1,\"showInReply\":false,\"reported\":false,\"canCancelReport\":false,\"canDelete\":false,\"canVoteDownOrCancel\":false,\"canComment\":false,\"canViewReports\":false,\"isCurrentUserAuthor\":false,\"liked\":false,\"moderatorComment\":false}]}}"); const simplifiedQuestionView = JSON.parse("true"); (function() window.pageContext = mergeDeep(pageContext, question: id: 9609572, plug: "query-for-invoice-with-serial-number", votes: 0, questionTitle: "Query for Invoice with Serial Number", isClosed: false, isLocked: false, isRedirected: false, redirectedFromTitle: "", redirectedFromId: "", closedStatusData: JSON.parse(""), userVoted: false, relations: JSON.parse("\"canClose\":false,\"canUnredirect\":false,\"canReport\":false,\"visibility\":\"full\",\"canEdit\":false,\"canUseDelete\":false,\"canReopen\":false,\"type\":\"question\",\"canVoteUpOrCancel\":false,\"canViewRevisions\":true,\"canUnlock\":false,\"reported\":false,\"canVoteDownOrCancel\":false,\"canLock\":false,\"canCancelReport\":false,\"canComment\":true,\"isCurrentUserAuthor\":false,\"canViewReports\":false"), isQuestionAccepted: true , childToViewInfo: id: "" , comments: JSON.parse("\"9609770\":\"rootParentId\":9609770,\"commentsCount\":4,\"comments\":[\"body\":\"Working... showing full records\",\"author\":\"username\":\"former.member\",\"displayName\":\"Former Member\",\"groupIcons\":[],\"suspended\":true,\"isCurrentUser\":false,\"parentAuthor\":\"username\":\"kennedy.t\",\"displayName\":\"Kennedy Thomas\",\"groupIcons\":[],\"suspended\":false,\"isCurrentUser\":false,\"id\":9611229,\"creationDate\":1350622646000,\"activeRevisionId\":10383953,\"lastActivity\":1350622646000,\"parentId\":9611300,\"originalParentId\":9609572,\"likeCount\":1,\"visibility\":\"full\",\"depth\":0,\"attachments\":[],\"canVoteUpOrCancel\":false,\"relations\":\"canReport\":false,\"visibility\":\"full\",\"canEdit\":false,\"canUseDelete\":false,\"isLiked\":false,\"type\":\"comment\",\"canVoteUpOrCancel\":false,\"canConvertToAnswer\":false,\"canBeModerated\":false,\"canViewRevisions\":false,\"score\":1,\"showInReply\":false,\"reported\":false,\"canCancelReport\":false,\"canDelete\":false,\"canVoteDownOrCancel\":false,\"canComment\":false,\"canViewReports\":false,\"isCurrentUserAuthor\":false,\"liked\":false,\"moderatorComment\":false]"), answerPager: answersCount: 4, page: 1, pageSize: 10, pageCount: 1, sort: "votes" , answers: JSON.parse("[\"body\":\"\\n Thanks for suggestion but\\n\\n \\n\\n\\n Kennedyisnull(T0.[IntrSerial],0) is not working in Select Distinct. It shows errorORDER BY items must appear in the select list if SELECT DISTINCT is specified.and RahulUr query is very helpful to me But I dont want Serial number from Delivery (ODLN). I want Sr. No. from Invoices i.e. OINV.I tried ur query removinf ODLN line.. But it shows slight difference in result.\\n\\n\\n \\n \\n \\n \\n\",\"author\":\"username\":\"former.member\",\"displayName\":\"Former Member\",\"groupIcons\":[],\"suspended\":true,\"isCurrentUser\":false,\"id\":9609770,\"posted\":1350561720000,\"votes\":3,\"isAccepted\":true,\"isLocked\":false,\"userVoted\":\"\",\"relations\":\"score\":3,\"canCancelAccept\":false,\"canUnlock\":false,\"canUseDelete\":false,\"accepted\":true,\"canVoteDownOrCancel\":false,\"canLock\":false,\"canAccept\":false,\"type\":\"answer\",\"canVoteUpOrCancel\":false,\"isCurrentUserAuthor\":false,\"attachments\":[],\"body\":\"Hi Ketaki.....Try this sample query......SELECT T10.CardCode, T10.Docnum,T10.DocDate, T10.CardName,T11.Quantity,T11.ItemCode,T4.DistNumberfrom OINV T10 Inner Join INV1 T11 ON T10.DocEntry=T11.DocEntry Inner Join DLN1 T1 on T11.BaseEntry=T1.DocEntry Inner Join ODLN T0 ON T0.DocEntry=T1.DocEntryleft outer join SRI1 I1 on T1.ItemCode=I1.ItemCode and (T1.DocEntry=I1.BaseEntry and T1.ObjType=I1.BaseType)left outer join OSRN T4 on T4.ItemCode=I1.ItemCode and I1.SysSerial=T4.SysNumberRegards,Rahul\",\"author\":\"username\":\"former.member\",\"displayName\":\"Former Member\",\"groupIcons\":[],\"suspended\":true,\"isCurrentUser\":false,\"id\":9609689,\"posted\":1350558847000,\"votes\":1,\"isAccepted\":false,\"isLocked\":false,\"userVoted\":\"\",\"relations\":\"score\":1,\"canCancelAccept\":false,\"canUnlock\":false,\"canUseDelete\":false,\"canVoteDownOrCancel\":false,\"canLock\":false,\"canAccept\":false,\"type\":\"answer\",\"canVoteUpOrCancel\":false,\"isCurrentUserAuthor\":false,\"attachments\":[],\"body\":\"Hi Ketaki,\\n\\nTry this,\\n\\n\\nSELECT distinct T4.DocNum as 'Invoice No.', T4.DocDate, T4.NumAtCard as 'Ref. No.', T4.CardName as 'Customer Name',\\nT2.U_Part_No as 'Part No.', t2.ItemCode as 'Item Code',t0.ItemCode, T0.ItemName as 'Item Name', isnull(T0.[IntrSerial],0) as 'Serial No.'\\nFROM [dbo].[OSRI] T0\\nINNER JOIN SRI1 T1\\n ON T0.SysSerial = T1.SysSerial and T1.[BaseType]=13\\n and T0.[ItemCode]=T1.[ItemCode]\\nLEFT JOIN [dbo].[INV1] T2 ON T2.DocEntry=T1.BaseEntry and T2.LineNum=T1.BaseLinNum\\nLEFT JOIN [dbo].[OINV] T4 ON T4.DocEntry=T2.DocEntry where T4.CardCode = '[%0]'ORDER BY T0.[ItemCode], T0.[IntrSerial]\",\"author\":\"username\":\"azeem.hafeez\",\"displayName\":\"Azeem Hafeez\",\"groupIcons\":[],\"suspended\":false,\"isCurrentUser\":false,\"id\":9609613,\"posted\":1350558532000,\"votes\":0,\"isAccepted\":false,\"isLocked\":false,\"userVoted\":\"\",\"relations\":\"canCancelAccept\":false,\"canUnlock\":false,\"canUseDelete\":false,\"canVoteDownOrCancel\":false,\"canLock\":false,\"canAccept\":false,\"type\":\"answer\",\"canVoteUpOrCancel\":false,\"isCurrentUserAuthor\":false,\"attachments\":[],\"body\":\"HiTry ThisSELECT distinct T4.DocNum as 'Invoice No.', T4.DocDate, T4.NumAtCard as 'Ref. No.', T4.CardName as 'Customer Name',T2.U_Part_No as 'Part No.', t2.ItemCode as 'Item Code',t0.ItemCode, T0.ItemName as 'Item Name', isnull(T0.[IntrSerial],0) as 'Serial No.'FROM [dbo].[OSRI] T0INNER JOIN SRI1 T1 ON T0.SysSerial = T1.SysSerial and T1.[BaseType]=13 and T0.[ItemCode]=T1.[ItemCode]LEFT JOIN [dbo].[INV1] T2 ON T2.DocEntry=T1.BaseEntry and T2.LineNum=T1.BaseLinNumLEFT JOIN [dbo].[OINV] T4 ON T4.DocEntry=T2.DocEntrywhere T4.CardCode = '[%0]'ORDER BY T0.[ItemCode], T0.[IntrSerial]RegardsKennedy\",\"author\":\"username\":\"kennedy.t\",\"displayName\":\"Kennedy Thomas\",\"groupIcons\":[],\"suspended\":false,\"isCurrentUser\":false,\"id\":9609703,\"posted\":1350559131000,\"votes\":0,\"isAccepted\":false,\"isLocked\":false,\"userVoted\":\"\",\"relations\":\"canCancelAccept\":false,\"canUnlock\":false,\"canUseDelete\":false,\"canVoteDownOrCancel\":false,\"canLock\":false,\"canAccept\":false,\"type\":\"answer\",\"canVoteUpOrCancel\":false,\"isCurrentUserAuthor\":false,\"attachments\":[]]"), answerForm: formAction: "/answers/9609572/post.json", textareaName: "body", textareaErrors: "", isAttachmentsEnabled: true, answerEditorialGuideline: title: "Before answering", content: "You should only submit an answer when you are proposing a solution to the poster\'s problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that your answer complies with our Rules of Engagement.", links: [ title: "Rules of Engagement", href: " -of-engagement.html", ] , answerMinBodyLength: '10', answerMaxBodyLength: '20000' , currentUser: sapInternalId: '', permissions: canVoteUpOrCancel: false, canVoteDownOrCancel: false, canModerate: false, , isVotedUp: false, isVotedDown: false , alerts: alertModeratorMinLength : "It should be given a proper explanation about why the content is inappropriate.", alertModeratorMinLengthValue : "10", alreadyReportedMessage : "You already have an active moderator alert for this content." , url: profileApiBaseUrl: ' -api.services.sap.com', followUnfollowQuestion: '/sap/nodeSubscription.json', isFollowingQuestion: '/sap/isFollowingNode.json', vote: voteUp: '/commands/0/voteup.json', voteDown: '/commands/0/votedown.json', cancelVote: '/commands/0/cancelvote.json' , rss: answers: '/feed/9609572/answers.rss', answersAndComments: '/feed/9609572/comments-and-answers.rss' , authorizeUploadContext: type: 'answer' , atMention: userSearchServiceUrl: ' ', currentUserName: '', useNewUSSCORS: true, atMentionDelayMs: 100, showMentionInRedactor: true , attachmentSettings: commentMaxAttachments: '2', answerMaxAttachments: '10', commentMaxAttachmentSizeBytes: '1048576', answerMaxAttachmentSizeBytes: '1048576', commentAttachmentsSizeBytesTotal: '2097152', answerAttachmentsSizeBytesTotal: '10485760' , editor: editorClipboardUploadEnabled: true ) )(); Home
Community
Ask a Question
Write a Blog Post
Login / Sign-up Search Questions and Answers 0 Former Member Oct 18, 2012 at 11:05 AM Query for Invoice with Serial Number 897 Views Follow RSS Feed Hi... 2ff7e9595c
Comentários