How To Write A Logic ( Give To Same Suggation )
May 20, 2008View 1 Replies
ADVERTISEMENT
HOW TO WRITE A LOGIC IN SQL SERVER USING THIS COBAL CODE
May 25, 2008sub = 1
DETAILED-VOUCHER
VOUCHER-A OCCURS 13 TIMES.
ACC-NUM PIC X(6).
ADB-V OCCURS 12 TIMES.
CNT-V PIC 9999 COMP-3.
AMT-V PIC S9(9)V99 COMP-3.
It is a initialization of the two dimensional array DETAILED-VOUCHER(Temporary Variable) which consists of the account number for that account number different group of people say as (teachers, state police etc) have different 12 kinds of amounts such as (DEATH-AMT, PAYEE-AMTetc ) for every account number. SUB (Temporary Variable) is used as subscript whose value will be incremented by 1 after initializing current position by zero in order to initialize all 12 positions in array.
LOP-ZERO-2.
MOVE ZEROS TO CNT-V (1 SUB).
MOVE ZEROS TO AMT-V (1 SUB).
ADD 1 TO SUB.
IF SUB NOT GREATER THAN 12
GO TO LOP-ZERO-2.
MOVE 2 TO SUB.
LOP-ZERO-3.
MOVE CNTS (1) TO CNTS (sub).
ADD 1 TO SUB.
IF SUB NOT > 13 GO TO LOP-ZERO-3.
MOVE '401.21' TO ACC-NO-V (1). ---------- This are the hotcode values
MOVE '410.01' TO ACC-NO-V (2).
MOVE '410.02' TO ACC-NO-V (3).
MOVE '410.03' TO ACC-NO-V (4).
MOVE '410.04' TO ACC-NO-V (5).
MOVE '410.06' TO ACC-NO-V (6).
MOVE '410.05' TO ACC-NO-V (7).
MOVE '411.01' TO ACC-NO-V (8).
MOVE '411.02' TO ACC-NO-V (9).
MOVE '331.01' TO ACC-NO-V (10).
MOVE '212.19' TO ACC-NO-V (11).
MOVE '212.22' TO ACC-NO-V (12).
MOVE ZEROS TO XYZ-S (1).
MOVE ZEROS TO MNO-B (1).
MOVE ALL '9' TO A-KEY
MOVE 01 TO D-X.
MOVE 1 TO SYS-Y
Please give suggest me how to write logic in the sql server. I want to how to write two dimensional array loop in the sql server.
SQL Server 2008 :: Write Query For Date Logic?
May 25, 2015I have a below query which have a date filter like "EST_PICK_DATE between '2015-02-01' and '2015-06-01'", where the logic is EST_PICK_DATE should be 3 months from the current month and 1st date of next month. Ex for current month MAY, EST_PICK_DATE shoulc be between '2015-02-01' and '2015-06-01'. I need to write below query dynamically. In below query i have hardcoded the value ("EST_PICK_DATE between '2015-02-01' and '2015-06-01'"), but it should take dynamically. How to achieve this?
I am using this query in SSIS package, So Shall i do in SQL level or we should implement this logic in package? If yes, How?
INSERT INTO STG_Open_Orders (Div_Code, net_price, gross_price) SELECT ord.DIV_CODE AS Div_Code, ord_l.NET_PRICE AS net_price, ord_l.gross_price AS gross_price, FROM ORD ord inner join ORD_L ord_l ONord.ORD_ID=ord_l.ORD_ID WHERE ord_l.EST_PICK_DATE BETWEEN '2015-02-01' AND'2015-06-01'
Backup Master Key, Cannot Write Into File 'c: Empmaster'. Verify That You Have Write Permissions, That The File Path Is Valid.
Jul 12, 2006Hi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
I Give Up On This One...
Jul 20, 2005I have tried and tried to get this to work and I'm doing something dumb.I'm going to include sample data and my format file. Can someone helpme figure out my dumb mistake on a bulk insert using a format file?Don't worry about data types, I'll handle that later. Thanks a lot!!!CSV File:ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,ADDRESS1,CITY, STATE,ZIP,PHONE,COMPETITOR,PLAN,HARVESTDATE,EIDs,EIDCount,sourceIds,minS core,maxScore,hasContract,matchFilter,matchFilterBGR,matchFilterCAS,matc hFilterMTV,forceMatch,forceMatchUserId,paymentPlatforms,matchStatusColor ,recordId,dupId,NetworkComparedTo1,name1,Family Practice General Practice,FP,PCP,address1,DeerfieldBeach,FL,33442,phone1,C1,PPO,5/15/2004,1000067851,2,BADG_0874992,9,14.4,Y,Y,Y,Y,Y,0,,3,G,1, ,netcomp12,name2,Family Practice GeneralPractice,FP,PCP,address2,Margate,FL,33063,phone2,C 2,PPO,5/15/2004,1000067851,2,BADG_0874992,9,10,Y,Y,Y,Y,Y,0,,3,G,2, ,netcomp23,name3,General Practice,GP,PCP,address3,DeerfieldBeach,FL,33442,phone3,C3,PPO,5/15/2004,1000067851,2,BADG_0874992(14.4Y)|BADG_1901859,9,14.4,Y,Y,Y,Y,Y,0,,3,G,3, ,netcomp34,name4,ReproductiveEndocrinology,OBEN,OB,address4,Davie,FL,33328,phon e4,C4,PPO,5/15/2004,1000083687,1,CAS_650410436,10.3,10.3,Y,N,N,N,N,0,,0, Y,4, ,netcomp4Format File:8.0311 SQLCHAR 0 1 "
" 0 quote Latin1_General_CI_AS2 SQLCHAR 0 3000 "," 1 Provider_Raw_ID Latin1_General_CI_AS3 SQLCHAR 0 3000 "," 0 none_name Latin1_General_CI_AS4 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS5 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS6 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS7 SQLCHAR 0 3000 "," 0 none_Address Latin1_General_CI_AS8 SQLCHAR 0 3000 "," 0 none_City Latin1_General_CI_AS9 SQLCHAR 0 3000 "," 0 none_State Latin1_General_CI_AS10 SQLCHAR 0 3000 "," 0 none_Zip Latin1_General_CI_AS11 SQLCHAR 0 3000 "," 0 none_Phone Latin1_General_CI_AS12 SQLCHAR 0 3000 "," 0 none_Competitor Latin1_General_CI_AS13 SQLCHAR 0 3000 "," 0 none_Plan Latin1_General_CI_AS14 SQLCHAR 0 3000 "," 0 none_HarvestDate Latin1_General_CI_AS15 SQLCHAR 0 3000 "," 0 none_EIDs Latin1_General_CI_AS16 SQLCHAR 0 3000 "," 5 EIDCount Latin1_General_CI_AS17 SQLCHAR 0 3000 "," 0 SourceIds Latin1_General_CI_AS18 SQLCHAR 0 3000 "," 6 minScore Latin1_General_CI_AS19 SQLCHAR 0 3000 "," 7 maxScore Latin1_General_CI_AS20 SQLCHAR 0 3000 "," 8 hasContract Latin1_General_CI_AS21 SQLCHAR 0 3000 "," 9 matchFilter Latin1_General_CI_AS22 SQLCHAR 0 3000 "," 10 matchFilterBGR Latin1_General_CI_AS23 SQLCHAR 0 3000 "," 11 matchFilterCAS Latin1_General_CI_AS24 SQLCHAR 0 3000 "," 12 matchFilterMTV Latin1_General_CI_AS25 SQLCHAR 0 3000 "," 13 forceMatch Latin1_General_CI_AS26 SQLCHAR 0 3000 "," 14 forceMatchUserId Latin1_General_CI_AS27 SQLCHAR 0 3000 "," 15 paymentPlatforms Latin1_General_CI_AS28 SQLCHAR 0 3000 "," 16 matchStatusColor Latin1_General_CI_AS29 SQLCHAR 0 3000 "," 17 recordId Latin1_General_CI_AS30 SQLCHAR 0 3000 "," 18 dupId Latin1_General_CI_AS31 SQLCHAR 0 3000 "," 4 NetworkComparedTo Latin1_General_CI_ASHere's a script for the table!if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Provider_Results]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[Provider_Results]GOCREATE TABLE [dbo].[Provider_Results] ([Provider_Raw_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[Category] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SpecialtyCode] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[NetworkComparedTo] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[EIDCount] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[minScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[maxScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[hasContract] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilter] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilterBGR] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilterCAS] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilterMTV] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[forceMatch] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[forceMatchUserId] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[paymentPlatforms] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[matchStatusColor] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[recordId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[dupId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThanks again. I am dying on this one. With everything includedhopefully someone (smarter than me) can figure it out.Thanks,Andrew*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
Nu Ger Jag Upp (I Give Up)
Feb 5, 2008
Jag har installerat sql server 2008 (I have installed sql server)
I cannot find enterprise manager - I think that the sql server is useless unless there is a manager.
Sql - If Not Found, Give Name
Mar 13, 2007Been trying to get this one but need a little input.
SELECT a.Id,g.LabName......
FROM someTbl a,table g
WHERE a.id = @idAND (b.calLab = g.ID OR g.LabName = ???)
With the above, if b.calLab = 0 then there is no entry in table g.(I didn't develop the table or would have included NONE)
So, how to tell g.LabName to come back as 'None' if b.calLab = 0?
Thanks,
Zath
Give All Results Even When Not Available
Oct 15, 2007Hello I have a problem with a query..
What I want:
I have a query that selects from different tables:
Code:
SELECT * FROM table1 as t1 ,table2 as t2 WHERE t1.t2_Id = t2.Id
But now I want all results from table1 even if it's not linked with table2, if that's the case i want to add something like:
Code:
CASE
WHEN 'notlinked' THEN 1
ELSE 0
END HighLight
that way I can show the rows that have to be manually linked
hope you understand my problem...
thanks!
Someone Can Give A Litgh?
May 17, 2004Hi Everybody,
In a table there´s a field for store real values,
so...in the database, using the SQL Server Enterprise Manager
and execute query as 'select * from' and it return the value
'15,35' just like stored before, why using Delphi via an ADO conection
in my result set there is a '15,3500003814697' value if in the record time
the inputed value was '15,35'?
What I have doing wrong?
Would You Like To Give Me Some Advice
May 28, 2004hi,everyone
i use sybase before,and i want to learn ms sqlsr2000,can anybodu give me some advice,or give me some matrial
thx in advance
Please Give Me Answer
May 20, 2008batch processing means
View 3 Replies View RelatedGetting Day If We Give Date
Jan 3, 2007hi,
i want to get day if we give date.ie if i give 03-01-2006 it has to give day as wednesday.so what ever date if i give it has to give the day of the date.can any one please give me query for this
Can Anyone Give Me A Query For This ?
Jul 20, 2005These are my table structures . Could you please suggest me analternative:tblArticles: ArticleID,Title,Summary,ContextSubTypeID,PostDatetblArticlePages: ArticlePageID,ArticleID,PageNum,ContenttblAuthors:AuthorID, NametblArticleAuthors:ArticleID,AuthorIDtblContextSubtype:ContextSubTypeID,NameI need to get Author,PostDate,Title,tblContextSubType.Name,PageN um,Content,- these have to grouped accordingly by the respective fields. Contentneed not be grouped by.
View 2 Replies View RelatedRe:Please Give Me Ur Suggestion
Nov 13, 2007Hi
I am new to Sql Server
I have to select all fields from a table based on min(date) . i have duplicate accountnumbers in the query i need to eliminate that based on min(dateTime).
when i am writing a query
Select a,b,c,min(d) from table , it is not working?
Below is the example
A B C D
222 1 11 2007-02-12 14:09:16.000
222 1 8 2007-11-06 13:57:18.480
Any suggestions would be very helpful
Give Sql Query For This Illustrations
Mar 28, 2008Hai friends
i have table student with 2 fields s_id,s_name
s_id s_name1 raja2 ramu3 vinoth4 muthu5 arun
i have another table test with s_id who wrote test...
s_id 25
now i have to display like below....give me sql query
s_id s_name2 ramu5 arun
Thanks in Advance...pl Help me......
Give Me Sql Query For One Illustrations
Mar 28, 2008Hai friends i have table
s_team
id name game
1 raja cricket
2 ravi football
3 muthu cricket
4 pravin cricket
5 hurry football
I have to diplay the above table in the following ...pl give me sql query
game totalplayers
cricket 3
football 2
Any Solution For This??Please Give Some Suggestions
Dec 11, 2001I have started working with MS SQL Server 2000 recently. I have a scenario in which I require to know within the given period of time ( say 5 mins) , which all tables from a particular database got modified. I do not want to write a trigger for each and every table for all the 13 databases , my application deals with. I have even tried the following query:
declare @curdate datetime
select @curdate=getdate()
select name, refdate from sysobjects
where xtype = 'U' and refdate =@curdate
But nope it does not help me, since refdate is something else. Can anybody tell me how can you figure out from sysobjects when was a particular object last accessed , even this would serve my purpose.
I have trying to get this done via profiler. My applications api's connect to the database under some credentials which i do not know since I do not have access to source code ( i am doing black box testing). So I can't even put a trace on one particular user account. What I am doing currently is trapping all store procedure events..., but then its too much of work...
Hence I wanted to know , is there any way out for the situation where given a database name and a time span we can find out the tables modified/accessed within that time span from that database ???
Xp_logingrant.......Can Anyone Give Me This Script
May 27, 1999CAn you please give me a copy of this script...
THANK YOU...
To Give A Table Lock
Oct 14, 2005How can i give a table wise exclusive lock in MSSQL Server ?
I got the description but, How can I apply this ?
The sql :
LOCK TABLE <tablename> IN EXCLUSIVE MODE
is not working.
Is there any query/method to do this ?
Please help ...
thanks
About Exclusive locks
--------------------
Exclusive (X) locks are used for data modification operations, such as UPDATE, INSERT, or DELETE.
Other transactions cannot read or modify data locked with an Exclusive (X) lock. If a Shared (S) exists, other transactions cannot acquire an Exclusive (X) lock.
--------------------
Bcp Command To Give Colums
Jan 8, 2004Can someonte tell me that if i
bcp bda..mytable out c:discounts.xls -c -p , how can I put the first row as my column names since I get only data
How To Give More Space For My SQL Database
Jan 11, 2007(Please Rush)
Hi all,
I am using SQL SERVER 2000 now, and my SERVER is still have about 27 GB but in my database is saying only 3.4 MB left, I was trying to shrink my database but it is still did not give me more space.
Do you know any way could help me to extend more spaces for my database?
I also included the picture so could help you more
Thank you very much
vdang (vdang2003@sbcglobal.net)
Where...Can It Give You The Most Recent Entries?
Jun 12, 2007SELECT post.C_ID, post.Comment, post.Title, post.Date, authors.U_Name
FROM authors INNER JOIN
post ON authors.A_ID = post.A_ID
ORDER BY C_ID DESC
That's the query I'm using, and I was hoping there is a way to use the WHERE clause to have only the 5 highest #'s from my primary key, which is A_ID IDENTITY(1,1).
What would be the best way for me to do this? I also have date as a datetime, but then I could be getting more or less than 5 results in my query.
Thanks
Wht Does This Give Divide By 0 Error?
May 24, 2007I am using iif which works just fine in Access to prevent divide by zero errors but this:
=iif(Fields!oh_ret.Value<>0,(Fields!oh_ret.Value-Fields!oh_cost.Value)/Fields!oh_ret.Value,"")
Fails
As Does:
=iif(Fields!oh_ret.Value=0,"",(Fields!oh_ret.Value-Fields!oh_cost.Value)/Fields!oh_ret.Value)
Why? How do I get around this?
Plz Help Me How To Give Reportname In Reportexecutionservice
May 14, 2007Hi
Im trying to load a report using
Reportexecutionservice
like this
Reportexecutionservice.ExecutionInfo exinfo=res.LoadReport(reportname, null);
rptname consists of report which is present @ my server.How to give reportname inorder to load a report .im using reportingservice2005
plz do help me.
error im getting Unable to indentify the report.Plz tell what is path i should given in order to acces th report..
Anyone Give Solution(stored Procedure)?
Feb 28, 2008Table:Questionforum_id quest_id question description quest_raised_by raised_date alert_me approval no_of_hits16 1 What is diff between asp and asp.net? <Binary data> JOHNYJP 2/27/2008 5:32:38 PM True True NULL4 1 test question? <Binary data> SAMUEL 2/25/2008 12:25:05 PM False True 317 1 diff between asp.net1.0 and 2.0? <Binary data> MICHEL 2/27/2008 6:07:56 PM False True 315 2 What is diff vb6&vb.net? <Binary data> MICHEL 2/27/2008 6:32:52 PM True True 514 1 what are features available? <Binary data> RAGAVAN 2/26/2008 12:11:06 PM True True 915 1 VB6 tutorial.. <Binary data> RAGAVAN 2/26/2008 12:12:29 PM True True NULL14 2 Tell about access? <Binary data> RAGAVAN 2/26/2008 12:14:09 PM False True 514 3 how to create grath in excel? <Binary data> SAMUEL 2/26/2008 12:23:31 PM True True 714 4 test1 <Binary data> MICHEL 2/27/2008 6:34:08 PM True True 24 2 new quest? <Binary data> SAMUEL 2/27/2008 6:38:20 PM True True 18 1 test quest for html? <Binary data> GANESH 2/27/2008 6:41:07 PM True True NULL4 3 test html tag2 <Binary data> GANESH 2/27/2008 6:44:24 PM False True NULL8 2 hello its for test? <Binary data> MICHEL 2/27/2008 6:46:13 PM False True NULL21 1 What is the new feature ? <Binary data> SAMUEL 2/28/2008 9:30:43 AM False True NULL22 1 What is the new feature ? <Binary data> SAMUEL 2/28/2008 9:32:14 AM True True NULL23 1 Is orcas released? <Binary data> SAMUEL 2/28/2008 9:33:18 AM True True NULL16 2 Test page for html code!!!!!!!.... <Binary data> SAMUEL 2/28/2008 9:36:50 AM True True 417 2 explain with details? <Binary data> SAMUEL 2/28/2008 4:46:08 PM True True NULLTable:Replyforum_id quest_id ans_id answer reply_by reply_on alert_me14 3 12 <Binary data> MICHEL 2/28/2008 3:57:46 PM True4 1 8 <Binary data> GANESH 2/27/2008 4:46:52 PM NULL15 2 13 <Binary data> SAMUEL 2/28/2008 4:20:36 PM False4 2 14 <Binary data> SAMUEL 2/28/2008 4:46:39 PM False14 3 15 <Binary data> GANESH 2/28/2008 4:49:12 PM True14 2 16 <Binary data> GANESH 2/28/2008 4:50:01 PM False16 2 17 <Binary data> GANESH 2/28/2008 4:51:02 PM True17 1 9 <Binary data> MICHEL 2/27/2008 6:08:32 PM NULL17 1 10 <Binary data> SAMUEL 2/27/2008 6:25:40 PM NULL16 2 11 <Binary data> SAMUEL 2/28/2008 9:40:41 AM NULLThis is my table structure.when i pass value only to 'quest_raised_by' field in question table.but I want following result,suppose i pass quest_raised_by='SAMUEL'forum_id quest_id quest_raised_by ans_id no_of_reply answer reply_by reply_on alert_me 4 1 SAMUEL 8 1 <Binary data> GANESH 2/27/2008 4:46:52 PM NULL14 3 SAMUEL 15 2 <Binary data> GANESH 2/28/2008 4:49:12 PM True4 2 SAMUEL 14 1 <Binary data> SAMUEL 2/28/2008 4:46:39 PM False21 1 SAMUEL - 0 - - - -22 1 SAMUEL - 0 - - - -23 1 SAMUEL - 0 - - - -16 2 SAMUEL 17 2 <Binary data> GANESH 2/28/2008 4:51:02 PM True17 2 SAMUEL - 0 - - - -Any one give storedprocedure as soon as posible.Thanks,Durai
View 4 Replies View RelatedGive Solution Using Stored Procedure?
Feb 29, 2008username work_date no_of_working_hours(nvarchar)a 02/17/2008 0:50 a 02/18/2008 0:50 a 02/23/2008 0:20b 02/18/2008 0:30 b 02/23/2008 0:30 b 02/24/2008 0:30 suppose i pass work_date from 02/18/2008 to 02/23/2008.i want following result username total_no_of_working_hours(nvarchar)a 1:10b 1:00 How to write stored procedure?Thanks,Durai
View 7 Replies View RelatedWhy Doesnt BETWEEN Give Me My Desired Result?
Dec 9, 2005I am trying to select records based upon last name
WHERE (Last_Name BETWEEN 'A%' AND 'C%')
When I run this I get only last names starting with A and B--no C. ?!?
This is confusing to me....I ran it with lastname >= 'A%' and lastname <='C%'and it returned only names starting with B. Why does SQL ignore the "="
I hope this isnt obvious :/
Can You Give Me A Free Version Of "Log Explorer"
Aug 20, 2001Can you give me a free version of "Log Explorer"
View 2 Replies View RelatedHow Give Right To Backup All Databases To NT Account
Jan 8, 2002Hello , everybody
Is possible to set up server role to NT user in order to backup ONLY
any databases on server (nothing alse )
Now I am specifying this user as backup operator to each database
This NT account is domain account used to run Backup.exe software
Thank you
CASE Does Not Give Correct Result
Aug 31, 2007How do I automatically assign a new cardcode-number? (according to the following formula: highest existing number + 1)
Scenario:
-There are two types of business partners: Customers and Suppliers.
-Customers have the value 'C' in the colomn CardType.
-Suppliers have the value 'S' in the colomn CardType.
-Customers have the following syntax 'C123456' in the colomn CardCode.
-Suppliers have the following syntax 'S123456' in the colomn CardCode.
-Existing CardCode-values in the DB for the Customers: C000001 - C100599.
-Existing CardCode-values in the DB for the Suppliers: S000001 - S200199.
The idea is that when a user creates a new business partner, the CardCode should be automatically filled when a new assigned number (highest existing number + 1), according to the value that is selected in CardType (either the letter 'C' or 'S').
What's been done so far:
SELECT top 1
(CASE
WHEN CardType='C' THEN (SELECT top 1 'C' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'C%' AND T0. CardType='C'
order BY T0.CardCode desc FOR BROWSE)
WHEN CardType='S' THEN (SELECT top 1 'S' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'S%' AND T0. CardType='S'
order BY T0.CardCode desc FOR BROWSE)
END)
FROM OCRD T0
The current result:
The result that it gives is 'C100600'.
The problem however is that it always gives this result and does not take into account what has been selected in CardType.
When I add the following: "order BY T0.CardCode desc FOR BROWSE" it gives the result 'S200200'.
So, what does work is that it takes the highest existing value and adds 1. But what doesn't work is the taking account what value is selected in CardType.
Does anyone know how I can make this work?
Someone Can Give A Litgh? - Field Type
May 17, 2004Hi Everybody,
In a table there´s a field for store real values,
so...in the database, using the SQL Server Enterprise Manager
and execute query as 'select * from' and it return the value
'15,35' just like stored before, why using Delphi via an ADO conection
in my result set there is a '15,3500003814697' value if in the record time
the inputed value was '15,35'?
What I have doing wrong?
Thanks for attention
Leonardo Almeida
T-SQL (SS2K8) :: Give Point And Set The Score
Aug 12, 2014I've table and data as following,
CREATE TABLE [dbo].[x_SCORE](
[idx] [int] IDENTITY(-2147483648,1) NOT NULL,
[CVID] [int] NOT NULL,
[myGender] [char](1) NULL,
[whatGender] [char](1) NULL,
[point_Gender] [tinyint] NULL,
[Code] ....
So, my table and data must be as following,
This is the calculation for CVID=1449
1- myGender=M
2- whatGender=M
3- point_Gender=4
4- So, score_Gender=4
5- myBMI=23.53
6- min_BMI=20.22
7- max_BMI=30.00
8- point_BMI=3
9- myBMI is between 20.22 and 30.00
10- So, score_BMI=3
This is the calculation for CVID=1925
1- myGender=F
2- whatGender=M
3- point_Gender=4
4- So, score_Gender=0
5- myBMI=35.43
6- min_BMI=20.22
7- max_BMI=30.00
8- point_BMI=3
9- myBMI IS NOT between 20.22 and 30.00
10- So, score_BMI=0
After calculation, my data should be as following :
The variant for each row IS SAME. See as following :
Give Results Even If Two Tables Used Don't Match
Aug 12, 2014I have a table of customer information (ci), but I have to link to another table in order to get the street address (sa).
If I exclude the street address I get 33 results. If I make the link to street address I will only get 30 results.
What I found is that if the ci table field for country is blank and I try linking to sa table then that result will not be shown.
Is there a way to get all 33 results while linking the two tables?