SQL Coding - SQL Server 2000

alexisbr
alexisbr used Ask the Experts™
on
Hi.  I wrote some VB code in an Access module that I am trying to convert to SQL so I can run it directly on the server.  I am stuck on one thing and hope someone can help.

Here's my code in VB that works:
*************************
    Do While i < 9
     i = i + 1
     If rs1("D" & cstr(i)) = True Then
        thestring = thesample & "-" & CStr(i)
*************************
Here's what I have so far in SQL but it's not correct.  

  while @Dcount < 9 BEGIN
             set @Dcount = @Dcount + 1
             IF (@D +  convert(varchar(5), @Dcount) = 1)  BEGIN  
              Set @theString = thesample + ‘-’ + convert(varchar(5), @Dcount)
*************************
How do I write "D" & cstr(i) in SQL?  
Right now I am getting an error: Must declare the variable '@D'.

I did declare the dynamic variables:
declare @D1 as bit, @D2 as bit, @D3  as bit, @D4  as bit, @D5 as bit, @D6  as bit, @D7  as bit, @D8  as bit, @D9  as bit

Does anyone have any ideas?

Thanks,
Alexis
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What exactly is it that you want to do with this code? I think that the approach you're taking is not the most efficient one, since you're trying to convert a client side code to a server side code with the same logic.
Let us know exactly what you want to achieve and we might help you with a better solution.

Regards,

Author

Commented:
Thanks for your reply.  I am trying to convert data from one table into another so I can do reports based on the data in the second table.  I'm not concerned right now about the most efficient solution as I can probably tweak the code once I get it working. And it's not looking through more than a few thousand records.  I just need to structure the data in a different way for a report.

I have used this type of logic in VB, PHP and ASP and it works fine.  I just don't know how to write the equivalent code in SQL as I haven't done that much straight coding in SQL.  If you wouldn't mind telling me if this can be done, that would help.  I have already spent over an hour trying to find this logic on the internet by looking up dynamic sql, increments, etc but have not been successful.

I can always post a separate question if the code I write is not efficient.  This is going to be a process run from the server only and will not have a user interface.

Thanks,
Alexis
The problem is that in your vb code you have a recordset (I assume) rs1. You don't have that in you sql code, so I'd have to know exactly what you're trying to do with this recordset in order to provide you with an example of how it can be done.

From the looks of it you're evaluating different fields in the recordset for TRUE/FALSE, this could be done with a where clause in SQL, that's why we need to know more or less what you're trying to achieve with this query.
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Author

Commented:
I have records with data values and 9 bit values, D1 through D9.  If the bit value is true, I want to insert data into another table and set a string value based on which bit values was used in the insert statement.  I will give a similar, less complicated example about what I want to accomplish.  The data itself will not make sense but it will show you how I need to the data to look in the second table.

Source table:  MyData    fields: name varchar(20), D1 bit, D2 bit
record 1:  name = 'joe'  
record 2: name = 'mary'

Destination table:  ReportData      field: newfield varchar(25)

If D1 is true in MyData, I want to create a new record in ReportData that has newfield = 'joe-1'
If D2 is true in MyData, I want to create a new record in ReportData that has newfield = 'joe-2'

Therefore, if joe's record in MyData has D1 = true and D2 = false, only one record would be inserted into ReportData that has newfield = "joe-1".  If Mary's record has D1 and D2 both = true, then 2 records would be inserted into ReportData with "mary-1" and "mary-2".  In this example, I would end up with 3 records inserted into ReportData.

Does this make sense?

Thanks,
Alexis
Since you want to select the name + the number of the bit field that has 1 as value, plus you need to repeat the same row (with a different number) if it has two bit fields with value 1, then I don't think we can do this withouth the use of "union all" .

With this query, what we're doing is selecting the "name" field and adding "-1" when D1=1, we're adding "-2" when D2=1 and so forth.

Query:
SELECT [name]+'-1' AS [name], 
FROM MyData
WHERE D1=1
UNION ALL
SELECT [name]+'-2' AS [name], 
FROM MyData
WHERE D2=1
UNION ALL
SELECT [name]+'-3' AS [name], 
FROM MyData
WHERE D3=1
UNION ALL
SELECT [name]+'-4' AS [name], 
FROM MyData
WHERE D4=1
UNION ALL
SELECT [name]+'-5' AS [name], 
FROM MyData
WHERE D5=1
UNION ALL
SELECT [name]+'-6' AS [name], 
FROM MyData
WHERE D6=1
UNION ALL
SELECT [name]+'-7' AS [name], 
FROM MyData
WHERE D7=1
UNION ALL
SELECT [name]+'-8' AS [name], 
FROM MyData
WHERE D8=1
UNION ALL
SELECT [name]+'-9' AS [name], 
FROM MyData

Open in new window


If you need to insert this result in another table, then you can use something like this:
INSERT INTO myNewTable(col1, col2,..., colN)
SELECT [name]+'-1' AS [name], 
FROM MyData
WHERE D1=1
UNION ALL
SELECT [name]+'-2' AS [name], 
FROM MyData
WHERE D2=1
UNION ALL
SELECT [name]+'-3' AS [name], 
FROM MyData
WHERE D3=1
UNION ALL
SELECT [name]+'-4' AS [name], 
FROM MyData
WHERE D4=1
UNION ALL
SELECT [name]+'-5' AS [name], 
FROM MyData
WHERE D5=1
UNION ALL
SELECT [name]+'-6' AS [name], 
FROM MyData
WHERE D6=1
UNION ALL
SELECT [name]+'-7' AS [name], 
FROM MyData
WHERE D7=1
UNION ALL
SELECT [name]+'-8' AS [name], 
FROM MyData
WHERE D8=1
UNION ALL
SELECT [name]+'-9' AS [name], 
FROM MyData

Open in new window


That will insert the resultset of the query into the table.
In both queries you'll have to replace the fake table names with the real ones, and the field names too.
Let me know if this works for you.

Author

Commented:
Thanks, LionKing.  I understand what you have done and am applying it to my tables/fields.  I will get back to you.

Author

Commented:
LionKing,
Thanks.  Your logic worked but I did not use the UNION ALL lines.  They were causing an error and I realized that they weren't necessary.  The INSERT lines worked fine without the UNION ALL in both the Query Analyzer and the Stored Procedure that will be called from SQL Agent.

Thanks so much for your help.  This is a different way of thinking for me and I will use your code for future reference.

Alexis
You're welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial